MySQL分组查询GROUP BY的参数WITH ROLLUP使用略谈、GROUP BY去重查询

相信很多同学都使用过MySQL的GROUP BY分组查询，例如我有一个表：
[shell]
+----+----------+------+
| id | name | mark |
+----+----------+------+
| 1 | david | 22 |
| 2 | tom | 33 |
| 3 | tom | 34 |
| 4 | gavin | 45 |
| 5 | hellogxp | 33 |
| 6 | hellogxp | 22 |
| 7 | gavin | 455 |
| 8 | gavin | 44 |
| 9 | hellogxp | 22 |
+----+----------+------+[/shell]
我们经常使用的就是对单个数据列进行排序查询。例如我们可以统计一下名字的情况，看看哪个名字使用的人多。
[shell]SELECT `name`,count(*) AS num FROM test1 GROUP BY `name`;[/shell]
查询结果如下：[shell]
+----------+-----+
| name | num |
+----------+-----+
| david | 1 |
| gavin | 3 |
| hellogxp | 3 |
| tom | 2 |
+----------+-----+[shell]
从这里我们清晰的看出来名字为hellogxp和gavin的最多为3个，叫david的最少紧紧有1个。
那么我们也可以查询一下这些人当中，谁的得分最高：
[shell]SELECT MAX(mark) AS m,`name` FROM test1 GROUP BY `mark` ORDER BY `mark` DESC;[/shell]
查询结果如下：[shell]
+-----+-------+
| m | name |
+-----+-------+
| 455 | gavin |
| 45 | gavin |
| 44 | gavin |
| 34 | tom |
| 33 | tom |
| 22 | david |
+-----+-------+[/shell]
从上面的结果我们可以非常清晰的看出得分的高低情况。
那么除此之外其实GROUP BY可以根据多列来进行统计查询，例如这里我们可以根据name和mark两列来进行GROUP BY的统计查询。
[shell]SELECT `name`,`mark`,count(*) FROM test1 GROUP BY `name`,`mark`;[/shell]
查询结果如下：[shell]
+----------+------+----------+
| name | mark | count(*) |
+----------+------+----------+
| david | 22 | 1 |
| gavin | 44 | 1 |
| gavin | 45 | 1 |
| gavin | 455 | 1 |
| hellogxp | 22 | 2 |
| hellogxp | 33 | 1 |
| tom | 33 | 1 |
| tom | 34 | 1 |
+----------+------+----------+
8 rows in set[/shell]
这个返回结果就是根据这两列来进行统计查询的，其中name=hellogxp、mark=22的结果集有两行。
这里有一个很有趣的参数WITH ROLLUP，我们已多列GROUP BY为例来说一下这个参数的意义，这个参数会为第一列分组做一个统计，也即是相当于小计，最后还会为总体记录做一个统计。先看一下：
[shell]SELECT `name`,`mark`,count(*) AS num FROM test1 GROUP BY `name`,`mark` WITH ROLLUP;[/shell]
查询结果为：[shell]
+----------+------+-----+
| name | mark | num |
+----------+------+-----+
| david | 22 | 1 |
| david | NULL | 1 |
| gavin | 44 | 1 |
| gavin | 45 | 1 |
| gavin | 455 | 1 |
| gavin | NULL | 3 |
| hellogxp | 22 | 2 |
| hellogxp | 33 | 1 |
| hellogxp | NULL | 3 |
| tom | 33 | 1 |
| tom | 34 | 1 |
| tom | NULL | 2 |
| NULL | NULL | 9 |
+----------+------+-----+
13 rows in set[/shell]
okay，我们看到上面的结果集，david结果集下面有一条记录。gavin结果集的最后也有一行统计，hellogxp，tom的结果集最后都有一行统计集。记录集中有null的就是统计集，最后一行是对整个返回结果的统计。
至于GROUP BY还有一个小小的功能或者说就是这个查询本身的意义，这里来简单的和大家讲一下，也就是GROUP BY的去重查询：
在下面这条语句中：
[shell]SELECT name FROM `test1` GROUP BY `name`;[/shell]
出现在GROUP BY中的字段原则上要出现在SELECT中，当然，一些聚合函数，例如COUNT(*),MAX(*)等除外。
例如我有这样一个表：[shell]
+----+--------+--------+
| id | name | attr |
+----+--------+--------+
| 1 | apple | blue |
| 2 | orange | yellow |
| 3 | fruit | blue |
| 4 | fruit1 | red |
| 5 | fruit1 | red |
| 6 | fruit2 | blue |
| 7 | fruit2 | blue |
+----+--------+--------+
7 rows in set[/shell]
我现在有这样一个需求，我需要根据颜色筛选水果的名字，我可以这样查询：
[shell]SELECT `name`,`attr` FROM products WHERE `attr` IN ('red','blue','yellow');[/shell]
查询结果如下：[shell]
+--------+--------+
| name | attr |
+--------+--------+
| apple | blue |
| orange | yellow |
| fruit | blue |
| fruit1 | red |
| fruit1 | red |
| fruit2 | blue |
| fruit2 | blue |
+--------+--------+
7 rows in set[/shell]
从上面的查询语句可以看出，上面返回的结果集有重复的。但是我们想得到所有水果的名字，而且要除掉重复的。
那这里就用到了GROUP BY，我们对这个字段使用一下分组统计，即可去重：
[shell]SELECT `name`,`attr` FROM products WHERE `attr` IN ('red','blue','yellow') GROUP BY `name`;[/shell]
查询结果为：[shell]
+--------+--------+
| name | attr |
+--------+--------+
| apple | blue |
| fruit | blue |
| fruit1 | red |
| fruit2 | blue |
| orange | yellow |
+--------+--------+
5 rows in set[/shell]
这样我们查询出的水果的name就会是去重后的结果，没有重复啦。当然DISTINCT这个关键字可以去重，但是大数据量的时候会很耗费性能的。

那么我们继续再深入的探讨一下group by的小功能，例如这里我们有两张表，一张是用户信息表customer，一张是下单表orders。
customer表结构如下：[shell]
+----+-------------+----------------+
| id | customer_id | name |
+----+-------------+----------------+
| 1 | 1 | gaoxueping |
| 2 | 2 | zhouxuguang |
| 3 | 3 | jiangliqiong |
| 4 | 4 | zhanghongliang |
+----+-------------+----------------+[/shell]
orders表的结构如下：[shell]
+----+-------------+---------------------+
| id | customer_id | date_time |
+----+-------------+---------------------+
| 1 | 1 | 0000-00-00 00:00:00 |
| 2 | 1 | 2013-02-23 00:00:00 |
| 3 | 2 | 2013-01-01 00:00:00 |
| 4 | 2 | 2012-01-01 00:00:00 |
| 5 | 2 | 2012-11-01 00:00:00 |
| 6 | 3 | 2013-11-01 00:00:00 |
| 7 | 3 | 2014-11-01 00:00:00 |
+----+-------------+---------------------+[/shell]
从这两张表中可以看得出，id为1和3的用户每人都有两个订单，id为2的用户有3个订单，id为4的用户没有订单。
下面我们有这样的需求，查询出所有用户最后下单的时间。okay，我们立刻写下如下的语句：[shell]
select MAX(date_time) AS last_time,c.name,o.id from customer c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id ORDER BY last_time DESC;[/shell]
查询结果为：[shell]
+---------------------+--------------+----+
| last_time | name | id |
+---------------------+--------------+----+
| 2014-11-01 00:00:00 | jiangliqiong | 6 |
| 2013-02-23 00:00:00 | gaoxueping | 1 |
| 2013-01-01 00:00:00 | zhouxuguang | 3 |
+---------------------+--------------+----+[/shell]
没问题，这就是i我们需要的数据，其中MAX(date_time)是关键，这个保证你查询出来的时间是最新的下单时间。否则，如果这样查询：[shell]
select o.date_time,c.name,o.id from customer c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id ORDER BY o.date_time DESC;[/shell]
查询出来的是数据表中的第一条记录，也就是如果你有三个订单，查出来的是你的排在数据表前面的订单。因为order by给你去重的时候不知道你要哪一条，因此就在第一条截断啦。[shell]
+---------------------+--------------+----+
| date_time | name | id |
+---------------------+--------------+----+
| 2013-11-01 00:00:00 | jiangliqiong | 6 |
| 2013-01-01 00:00:00 | zhouxuguang | 3 |
| 0000-00-00 00:00:00 | gaoxueping | 1 |
+---------------------+--------------+----+[/shell]
那么okay，接下来还有个小家伙zhanghongliang没有订单，但是我们也想查询出来，这个简单，左连接一下即可：[shell]
select MAX(date_time) AS last_time,c.name,o.id from customer c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id ORDER BY last_time DESC;[/shell]，查询结果为：[shell]
+---------------------+----------------+------+
| last_time | name | id |
+---------------------+----------------+------+
| 2014-11-01 00:00:00 | jiangliqiong | 6 |
| 2013-02-23 00:00:00 | gaoxueping | 1 |
| 2013-01-01 00:00:00 | zhouxuguang | 3 |
| NULL | zhanghongliang | NULL |
+---------------------+----------------+------+[/shell]
其实关联表的时候一般很少用到左连接，因为一般来货关联的表都有对应关系，左连接其实是将没有关联的也查出来，也就是左表中所有的查出来，从大部分业务逻辑上来讲，使用JOIN的概率远远大于LEFT JOIN.