最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
MySQL group by对单字分组序和多字段分组的方法分享
时间:2022-06-29 09:13:00 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下MySQL group by对单字分组序和多字段分组的方法,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
创建一个 goods 表,先看下里面的数据:
mysql> select * from goods; +----+------+------+------------+-------------+------------+ | id | s_id | b_id | goods_name | goods_price | goods_desc | +----+------+------+------------+-------------+------------+ | 1 | 1 | 5 | book | 22.35 | book | | 2 | 2 | 5 | ball | 32.25 | ball | | 3 | 3 | 5 | NULL | 3.23 | NULL | | 4 | 3 | 5 | macbook | 3.23 | book | | 5 | 3 | 5 | listbook | 2.30 | book | | 6 | 1 | 1 | nicebook | 9999.00 | nicebook | | 7 | 2 | 3 | googlebook | 25.30 | book | +----+------+------+------------+-------------+------------+
1、根据s_id分组
mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id; +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+ | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+ | 1 | 1 | 5 | book | 22.35 | book | book,nicebook | book,nicebook | 1,6 | 22.35,9999.00 | | 2 | 2 | 5 | ball | 32.25 | ball | ball,googlebook | ball,book | 2,7 | 32.25,25.30 | | 3 | 3 | 5 | NULL | 3.23 | NULL | macbook,listbook | book,book | 3,4,5 | 3.23,3.23,2.30 | +----+------+------+------------+-------------+------------+------------------+---------------+-------+----------------+
这里使用了group_concat()函数,主要目的是为了显示分组的详细信息
上面的根据单个字段分组很简单,把相同s_id的记录都归组了
2、根据s_id,goods_desc字段分组
分析:这里查询分组时,会先根据s_id分组,然后对每个组里面的数据再根据goods_desc进行分组
mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc; +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+ | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+ | 1 | 1 | 5 | book | 22.35 | book | book | book | 1 | 22.35 | | 6 | 1 | 1 | nicebook | 9999.00 | nicebook | nicebook | nicebook | 6 | 9999.00 | | 2 | 2 | 5 | ball | 32.25 | ball | ball | ball | 2 | 32.25 | | 7 | 2 | 3 | googlebook | 25.30 | book | googlebook | book | 7 | 25.30 | | 3 | 3 | 5 | NULL | 3.23 | NULL | NULL | NULL | 3 | 3.23 | | 4 | 3 | 5 | macbook | 3.23 | book | macbook,listbook | book,book | 4,5 | 3.23,2.30 | +----+------+------+------------+-------------+------------+------------------+-------------+------+--------------+
这里的goods_descs 和 上面的一比较就明白了
接下来还可以再根据 goods_price 分组
mysql> select *,group_concat(goods_name) goods_names,group_concat(goods_desc) goods_descs,group_concat(id) ids,group_concat(goods_price) goods_prices from goods group by s_id,goods_desc,goods_price; +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+ | id | s_id | b_id | goods_name | goods_price | goods_desc | goods_names | goods_descs | ids | goods_prices | +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+ | 1 | 1 | 5 | book | 22.35 | book | book | book | 1 | 22.35 | | 6 | 1 | 1 | nicebook | 9999.00 | nicebook | nicebook | nicebook | 6 | 9999.00 | | 2 | 2 | 5 | ball | 32.25 | ball | ball | ball | 2 | 32.25 | | 7 | 2 | 3 | googlebook | 25.30 | book | googlebook | book | 7 | 25.30 | | 3 | 3 | 5 | NULL | 3.23 | NULL | NULL | NULL | 3 | 3.23 | | 5 | 3 | 5 | listbook | 2.30 | book | listbook | book | 5 | 2.30 | | 4 | 3 | 5 | macbook | 3.23 | book | macbook | book | 4 | 3.23 | +----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
这里主要是进行多个字段分组的时候,只需掌握分组顺序后面的字段是根据前面字段分组后的内容再进行的分组即可。
在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组, 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表,表结构如下:
Table:Subject_Selection
Subject Semester Attendee --------------------------------- ITB001 1 John ITB001 1 Bob ITB001 1 Mickey ITB001 2 Jenny ITB001 2 James MKB114 1 John MKB114 1 Erica
我们想统计每门课程有多少个学生报名,应用如下SQL:
SELECT Subject, Count(*) FROM Subject_Selection GROUP BY Subject
得到如下结果:
Subject Count
------------------------------
ITB001 5
MKB114 2
因为表里记录了有5个学生选择ITB001,2个学生选择了MKB114。
产生这个结果的原因是:
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。
那么GROUP BY X, Y呢?
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
相关文章
- 人们熟悉的寄居蟹属于以下哪种分类 神奇海洋11月21日答案 11-21
- 第五人格11.22共研服有什么更新 11月22日共研服更新内容介绍 11-21
- 原神恰斯卡怎么培养 11-21
- 无期迷途四星装束是谁 11-21
- 王者荣耀帝丹高中校服怎么获得 11-21
- 光遇姆明季后续版本怎么玩 11-21