我這里創(chuàng)建了一個 goods 表，先看下里面的數(shù)據(jù)：
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、根據(jù)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()函數(shù)，主要目的是為了顯示分組的詳細信息
上面的根據(jù)單個字段分組很簡單，把相同s_id的記錄都歸組了
2、根據(jù)s_id,goods_desc字段分組
分析：這里查詢分組時，會先根據(jù)s_id分組，然后對每個組里面的數(shù)據(jù)再根據(jù)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 和 上面的一比較就明白了
接下來還可以再根據(jù) 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 |
+----+------+------+------------+-------------+------------+-------------+-------------+------+--------------+
這里主要是進行多個字段分組的時候，只需掌握分組順序后面的字段是根據(jù)前面字段分組后的內容再進行的分組即可。
在平時的開發(fā)任務中我們經(jīng)常會用到MYSQL的GROUP BY分組， 用來獲取數(shù)據(jù)表中以分組字段為依據(jù)的統(tǒng)計數(shù)據(jù)。比如有一個學生選課表，表結構如下：
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
我們想統(tǒng)計每門課程有多少個學生報名，應用如下SQL：
SELECT Subject, Count(*)
FROM Subject_Selection
GROUP BY Subject
得到如下結果：
Subject Count
------------------------------
ITB001 5
MKB114 2
因為表里記錄了有5個學生選擇ITB001，2個學生選擇了MKB114。
產(chǎn)生這個結果的原因是：
GROUP BY X意思是將所有具有相同X字段值的記錄放到一個分組里。
那么GROUP BY X, Y呢？
GROUP BY X, Y意思是將所有具有相同X字段值和Y字段值的記錄放到一個分組里。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，謝謝大家對腳本之家的支持。如果你想了解更多相關內容請查看下面相關鏈接
您可能感興趣的文章:- MySql Group By對多個字段進行分組的實現(xiàn)方法
- mysql group by 對多個字段進行分組操作
