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