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_pricesfrom 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_pricesfrom 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_pricesfrom 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 |
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
总结：这里主要是进行多个字段分组的时候，只需掌握分组顺序后面的字段是根据前面字段分组后的内容再进行的分组即可。
页:
[1]