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 單字分組序和多欄位分組