MySQL查询到GROUP BY多列
您可以使用IF()GROUPBY多列。为了理解这个概念，让我们创建一个表。创建表的查询如下
mysql> create table MultipleGroupByDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CustomerId int, -> ProductName varchar(100) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1000,'Product-1'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1001,'Product-2'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1002,'Product-3'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1002,'Product-3'); mysql> insert into MultipleGroupByDemo(CustomerId,ProductName) values(1003,'Product-4');
使用select语句显示表中的所有记录。查询如下-
mysql> select *from MultipleGroupByDemo;
输出如下
+----+------------+-------------+ | Id | CustomerId | ProductName | +----+------------+-------------+ | 1 | 1000 | Product-1 | | 2 | 1001 | Product-2 | | 3 | 1001 | Product-2 | | 4 | 1001 | Product-2 | | 5 | 1002 | Product-3 | | 6 | 1002 | Product-3 | | 7 | 1003 | Product-4 | +----+------------+-------------+ 7 rows in set (0.00 sec)
这是对GROUPBY多列的查询
mysql> SELECT CustomerId, if( ProductName = 'Product-2', 1, 0 ) -> FROM MultipleGroupByDemo -> GROUP BY ProductName , CustomerId;
以下是输出
+------------+---------------------------------------+ | CustomerId | if( ProductName = 'Product-2', 1, 0 ) | +------------+---------------------------------------+ | 1000 | 0 | | 1001 | 1 | | 1002 | 0 | | 1003 | 0 | +------------+---------------------------------------+ 4 rows in set (0.00 sec)
这是一个替代查询
mysql> select CustomerId,MAX(IF(ProductName = 'Product-2', 1,0)) from MultipleGroupByDemo group by CustomerId;
以下是输出
+------------+-----------------------------------------+ | CustomerId | MAX(IF(ProductName = 'Product-2', 1,0)) | +------------+-----------------------------------------+ | 1000 | 0 | | 1001 | 1 | | 1002 | 0 | | 1003 | 0 | +------------+-----------------------------------------+ 4 rows in set (0.00 sec)