MySQL GROUP BY 语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
语法结构:
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
准备数据:
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016‐04‐22 15:25:33', '1'), ('2', '小王',
'2016‐04‐20 15:25:47', '3'), ('3', '小丽', '2016‐04‐19 15:26:02', '2'), ('4', '小王',
'2016‐04‐07 15:26:14', '4'), ('5', '小明', '2016‐04‐11 15:26:40', '4'), ('6', '小明',
'2016‐04‐04 15:26:54', '2');
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+
| name | COUNT(*) |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐+
mysql> select * from employee_tbl;
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
| id | name | date | singin |
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
| 1 | ?? | 2016‐04‐22 15:25:33 | 1 |
| 2 | ?? | 2016‐04‐20 15:25:47 | 3 |
| 3 | ?? | 2016‐04‐19 15:26:02 | 2 |
| 4 | ?? | 2016‐04‐07 15:26:14 | 4 |
| 5 | ?? | 2016‐04‐11 15:26:40 | 4 |
| 6 | ?? | 2016‐04‐04 15:26:54 | 2 |
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
mysql> select * from employee_tbl group by singin;
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
| id | name | date | singin |
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
| 1 | ?? | 2016‐04‐22 15:25:33 | 1 |
| 3 | ?? | 2016‐04‐19 15:26:02 | 2 |
| 2 | ?? | 2016‐04‐20 15:25:47 | 3 |
| 4 | ?? | 2016‐04‐07 15:26:14 | 4 |
+‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐+
注意:
1、group by 可以实现一个最简单的去重查询,假设想看下有哪些员工,除了用 distinct,还可以用:
SELECT name FROM employee_tbl GROUP BY name;
2、分组后的条件使用 HAVING 来限定,WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为
where 、group by 、having、order by ,例如:
SELECT name ,sum(*) FROM employee_tbl WHERE id<>1 GROUP BY name HAVING sum(*)>5 ORDER
BY sum(*) DESC;