MySQL查询到GROUP BY多列

您可以使用IF()GROUP BY多列。为了理解这个概念，让我们创建一个表。创建表的查询如下

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)

这是对GROUP BY多列的查询

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)