mysql分组编序_MySQL group by对单字分组序和多字段分组的方法分享

本篇文章小编给大家分享一下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字段值的记录放到一个分组里。