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.

Avatar photo

About Blackford

这是个最好的时代,这是个最坏的时代,这是个充满希望的春天,这是个令人绝望的冬天,我们前面什么都有,我们前面什么都没有。梦想,让我们一次次的走远,又一次次的回头,一个关于人生的梦想还在不断奔跑,带着喜悦和疼痛,不过一切才刚刚开始,并且直到今天也远远没有结束
This entry was posted in 数据库技术 and tagged , , , , . Bookmark the permalink.

发表评论

电子邮件地址不会被公开。 必填项已用*标注