可以使用GROUP BY按列的值進行分組,並且,如果願意的話,也可對列進行計算。可以使用COUNT, SUM, AVG等函數,在上頁進行列的分組計算。
要了解GROUP BY子句,我們先考慮有一個 employee_tbl表,這具有以下記錄:
mysql> create table employee_tabl( id int(10) not null primary key auto_increment, name varchar(32) not null default '', work_date date, daily_typing_pages int(10) default 0 ); Query OK, 0 rows affected (0.28 sec) INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('John', '2015-01-24', 150); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Ram', '2015-07-27', 220); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-05-06', 170); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jack', '2015-01-24', 100); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Jill', '2015-01-24', 220); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-01-26', 300); INSERT INTO employee_tabl(name, work_date,daily_typing_pages) VALUES('Zara', '2015-02-24', 350);
查詢結果:
mysql> SELECT * FROM employee_tabl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2015-01-24 | 150 | | 2 | Ram | 2015-05-27 | 220 | | 3 | Jack | 2015-05-06 | 170 | | 3 | Jack | 2015-04-06 | 100 | | 4 | Jill | 2015-04-06 | 220 | | 5 | Zara | 2007-06-06 | 300 | | 5 | Zara | 2007-02-06 | 350 | +------+------+------------+--------------------+ 7 rows in set (0.00 sec)
現在假定在上表的基礎上,要算天每個員工做的工作數量。
如果我們如寫一個SQL查詢,那麼我們將得到以下結果:
mysql> SELECT COUNT(*) FROM employee_tbl; +---------------------------+ | COUNT(*) | +---------------------------+ | 7 | +---------------------------+
但是,這不是我們的服務目的,我們要分頁顯示打印每個人的總數。這是通過使用聯聚合函數GROUP BY子句如下:
mysql> SELECT name, COUNT(*) -> FROM employee_tbl -> GROUP BY name; +------+----------+ | name | COUNT(*) | +------+----------+ | Jack | 2 | | Jill | 1 | | John | 1 | | Ram | 1 | | Zara | 2 | +------+----------+ 5 rows in set (0.04 sec)
我們會看到更多相關 GROUP BY 的函數,如SUM,AVG等