MySQL GROUP BY 和GROUP_CONCAT的一些用法

SOASPX 23492 0

1)  作用:GROUP BY 语句根据一个或多个列对结果集进行分组。

所谓的分组就是根据GROUP BY中的分组标记,将一个“数据集”划分成若干个“小区域”,每个分组标记相同的值,会划分在同一个"小区域中",最终查询出的结果只会显示"小区域"中一条记录。

2)GROUP BY 语句中的GROUP_CONCAT()函数

因为GROUP BY默认只显示了组中一条记录,如果想看组内的所有信息,就需要用到GROUP_CONCAT()函数

3)COUNT()函数:统计记录总数 

COUNT(*)会统计我们表中的NULL值,如果不想统计NULL值,请写COUNT(字段名)

4)聚合函数[‘SUM()求和函数’,’MAX()函数:求最大值’,’MIN()函数:求最小值’,’AVG()函数:求平均值

在GROUP BY的过程中,如果碰到聚合函数,会进行聚合操作

 

mysql测试:

假设有数据表结构如下:

CREATE TABLE `user_info` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `user_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '用户编号',
    `grade` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '年级',
    `class` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '班级',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `uniq_user_id` (`user_id`)
)
ENGINE=InnoDB

插入记录:

INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (10, '10230', 'C', 'B');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (9, '10229', 'C', 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (8, '10228', 'B', 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (7, '10227', 'B', 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (6, '10226', 'B', 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (5, '10225', 'B', 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (4, '10224', 'A', 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (3, '10223', 'A', 'b');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (2, '10222', 'A', 'a');
INSERT INTO `user_info` (`id`, `user_id`, `grade`, `class`) VALUES (1, '10221', 'A', 'a');

所有记录查询结果:

查询grade,和user_id的所有记录

mysql> SELECT user_id,grade FROM user_info;
+---------+-------+
| user_id | grade |
+---------+-------+
| 10221   | A     |
| 10222   | A     |
| 10223   | A     |
| 10224   | A     |
| 10225   | B     |
| 10226   | B     |
| 10227   | B     |
| 10228   | B     |
| 10229   | C     |
| 10230   | C     |
+---------+-------+

group by的常规用法

1.以grade分组,并且查看user_id信息

mysql> SELECT user_id,grade FROM user_info GROUP BY grade ;
+---------+-------+
| user_id | grade |
+---------+-------+
| 10221   | A     |
| 10225   | B     |
| 10229   | C     |
+---------+-------+

由结果可知:使用GROUP BY分组之后,每个分组标记相同的记录只会出现第一条,其他的相同的分组标记的记录会舍弃

2.利用GROUP_CONCAT查看user_id的详细信息

mysql> SELECT GROUP_CONCAT(user_id),grade FROM user_info GROUP BY grade ;
+-------------------------+-------+
| GROUP_CONCAT(user_id)   | grade |
+-------------------------+-------+
| 10221,10222,10223,10224 | A     |
| 10228,10227,10226,10225 | B     |
| 10229,10230             | C     |
+-------------------------+-------+

3.聚合函数max

mysql> select max(user_id),grade from user_info group by grade ;
+--------------+-------+
| max(user_id) | grade |
+--------------+-------+
| 10224        | A     |
| 10228        | B     |
| 10230        | C     |
+--------------+-------+

4.having条件进一步过滤

mysql> select max(user_id),grade from user_info group by grade  having grade>'A';
+--------------+-------+
| max(user_id) | grade |
+--------------+-------+
| 10228        | B     |
| 10230        | C     |
+--------------+-------+

group by的非常规用法

1.查询的列中除了聚合列,分组列标识还有其他常规列,常规列如何取值?

mysql> select max(user_id),id,grade from user_info group by grade;
+--------------+----+-------+
| max(user_id) | id | grade |
+--------------+----+-------+
| 10224        |  1 | A     |
| 10228        |  5 | B     |
| 10230        |  9 | C     |
+--------------+----+-------+

sql的结果就值得讨论了,与上述例子不同的是,查询条件多了id一列。数据按照grade分组后,grade一列是相同的,max(user_id)按照数据进行计算也是唯一的,id一列是如何取值的?看上述的数据结果, 
推论:id是物理内存的第一个匹配项

2.修改id按照上述数据结果,将id=1,改为id=99,执行相同SQL:

mysql> select max(user_id),id,grade from user_info group by grade;
+--------------+----+-------+
| max(user_id) | id | grade |
+--------------+----+-------+
| 10224        |  2 | A     |
| 10228        |  5 | B     |
| 10230        |  9 | C     |
+--------------+----+-------+

推论:第一条数据id变成了99,查出的结果第一条数据的id从1变成了2。表明,id这个非聚合条件字段的取值与数据写入的时间无关,因为id=1的记录是先于id=2存在的,修改的数据不过是修改了这条数据的内容。结合mysql的数据存储理论,由于id是主键,所以数据在检索是是按照主键排序后进行过滤的,因此 
推论:id字段的选取是按照mysql存储的检索数据匹配的第一条。

3.聚合函数和常量在一起做运算

mysql> SELECT GROUP_CONCAT(user_id),grade,SUM(user_id)+9,SUM(user_id) FROM user_info GROUP BY grade ;
+-------------------------+-------+----------------+--------------+
| GROUP_CONCAT(user_id)   | grade | SUM(user_id)+9 | SUM(user_id) |
+-------------------------+-------+----------------+--------------+
| 10222,10221,10224,10223  | A     |          40899 |        40890 |
| 10225,10226,10228,10227  | B     |          40915 |        40906 |
| 10229,10230                       | C     |          20468 |        20459 |
+-------------------------+-------+----------------+--------------+

当GROUP BY 碰到聚合函数和常量在一起的时候,聚合函数会正常发挥作用,但是常量只会计算一次,即在聚合完成之后,再和常量运算,而不是user_id和常量一起累加

结论

  • 当group by 与聚合函数配合使用时,功能为分组后计算

  • 当group by 与having配合使用时,功能为分组后过滤

  • 当group by 与聚合函数,同时非聚合字段同时使用时,非聚合字段的取值是第一个匹配到的字段内容,即id小的条目对应的字段内容。

项目中碰到的一个问题:

一个表EPRJ_LIST:

标签: mysql

发表评论 (已有0条评论)

还木有评论哦,快来抢沙发吧~