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

  • 内容
  • 评论
  • 相关

相信很多同学都使用过MySQL的GROUP BY分组查询,例如我有一个表:

+----+----------+------+
| 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 |
+----+----------+------+

我们经常使用的就是对单个数据列进行排序查询。例如我们可以统计一下名字的情况,看看哪个名字使用的人多。

SELECT `name`,count(*) AS num FROM test1 GROUP BY `name`;

查询结果如下:

+----------+-----+
| name     | num |
+----------+-----+
| david    |   1 |
| gavin    |   3 |
| hellogxp |   3 |
| tom      |   2 |
+----------+-----+
从这里我们清晰的看出来名字为hellogxp和gavin的最多为3个,叫david的最少紧紧有1个。
那么我们也可以查询一下这些人当中,谁的得分最高:
SELECT MAX(mark) AS m,`name` FROM test1 GROUP BY `mark` ORDER BY `mark` DESC;

查询结果如下:

+-----+-------+
| m   | name  |
+-----+-------+
| 455 | gavin |
|  45 | gavin |
|  44 | gavin |
|  34 | tom   |
|  33 | tom   |
|  22 | david |
+-----+-------+

从上面的结果我们可以非常清晰的看出得分的高低情况。
那么除此之外其实GROUP BY可以根据多列来进行统计查询,例如这里我们可以根据name和mark两列来进行GROUP BY的统计查询。

SELECT `name`,`mark`,count(*) FROM test1 GROUP BY `name`,`mark`;

查询结果如下:

+----------+------+----------+
| 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

这个返回结果就是根据这两列来进行统计查询的,其中name=hellogxp、mark=22的结果集有两行。
这里有一个很有趣的参数WITH ROLLUP,我们已多列GROUP BY为例来说一下这个参数的意义,这个参数会为第一列分组做一个统计,也即是相当于小计,最后还会为总体记录做一个统计。先看一下:

SELECT `name`,`mark`,count(*) AS num FROM test1 GROUP BY `name`,`mark` WITH ROLLUP;

查询结果为:

+----------+------+-----+
| 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

okay,我们看到上面的结果集,david结果集下面有一条记录。gavin结果集的最后也有一行统计,hellogxp,tom的结果集最后都有一行统计集。记录集中有null的就是统计集,最后一行是对整个返回结果的统计。
至于GROUP BY还有一个小小的功能或者说就是这个查询本身的意义,这里来简单的和大家讲一下,也就是GROUP BY的去重查询:
在下面这条语句中:

SELECT name FROM `test1` GROUP BY `name`;

出现在GROUP BY中的字段原则上要出现在SELECT中,当然,一些聚合函数,例如COUNT(*),MAX(*)等除外。
例如我有这样一个表:

+----+--------+--------+
| 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

我现在有这样一个需求,我需要根据颜色筛选水果的名字,我可以这样查询:

SELECT `name`,`attr` FROM products WHERE `attr` IN ('red','blue','yellow');

查询结果如下:

+--------+--------+
| name   | attr   |
+--------+--------+
| apple  | blue   |
| orange | yellow |
| fruit  | blue   |
| fruit1 | red    |
| fruit1 | red    |
| fruit2 | blue   |
| fruit2 | blue   |
+--------+--------+
7 rows in set

从上面的查询语句可以看出,上面返回的结果集有重复的。但是我们想得到所有水果的名字,而且要除掉重复的。
那这里就用到了GROUP BY,我们对这个字段使用一下分组统计,即可去重:

SELECT `name`,`attr` FROM products WHERE `attr` IN ('red','blue','yellow') GROUP BY `name`;

查询结果为:

+--------+--------+
| name   | attr   |
+--------+--------+
| apple  | blue   |
| fruit  | blue   |
| fruit1 | red    |
| fruit2 | blue   |
| orange | yellow |
+--------+--------+
5 rows in set

这样我们查询出的水果的name就会是去重后的结果,没有重复啦。当然DISTINCT这个关键字可以去重,但是大数据量的时候会很耗费性能的。

那么我们继续再深入的探讨一下group by的小功能,例如这里我们有两张表,一张是用户信息表customer,一张是下单表orders。
customer表结构如下:

+----+-------------+----------------+
| id | customer_id | name           |
+----+-------------+----------------+
|  1 |           1 | gaoxueping     |
|  2 |           2 | zhouxuguang    |
|  3 |           3 | jiangliqiong   |
|  4 |           4 | zhanghongliang |
+----+-------------+----------------+

orders表的结构如下:

+----+-------------+---------------------+
| 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 |
+----+-------------+---------------------+

从这两张表中可以看得出,id为1和3的用户每人都有两个订单,id为2的用户有3个订单,id为4的用户没有订单。
下面我们有这样的需求,查询出所有用户最后下单的时间。okay,我们立刻写下如下的语句:

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;

查询结果为:

+---------------------+--------------+----+
| 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 |
+---------------------+--------------+----+

没问题,这就是i我们需要的数据,其中MAX(date_time)是关键,这个保证你查询出来的时间是最新的下单时间。否则,如果这样查询:

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;

查询出来的是数据表中的第一条记录,也就是如果你有三个订单,查出来的是你的排在数据表前面的订单。因为order by给你去重的时候不知道你要哪一条,因此就在第一条截断啦。

+---------------------+--------------+----+
| 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 |
+---------------------+--------------+----+

那么okay,接下来还有个小家伙zhanghongliang没有订单,但是我们也想查询出来,这个简单,左连接一下即可:

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;

,查询结果为:

+---------------------+----------------+------+
| 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 |
+---------------------+----------------+------+

其实关联表的时候一般很少用到左连接,因为一般来货关联的表都有对应关系,左连接其实是将没有关联的也查出来,也就是左表中所有的查出来,从大部分业务逻辑上来讲,使用JOIN的概率远远大于LEFT JOIN.

评论

0条评论

发表评论

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