最近在项目中用到数据库的读取发现where和having的区别
where:在group by 之前过滤数据
having:在group by 之前过滤数据
举个栗子
id | target_id | time | type | |
1 | 2 | 500 | 2 | |
2 | 2 | 400 | 0 | |
3 | 3 | 450 | 0 | |
4 | 3 | 350 | 0 | |
5 | 4 | 600 | 0 | |
6 | 4 | 550 | 0 | |
7 | 5 | 400 | 0 | |
8 | 5 | 300 | 0 | |
9 | 6 | 700 | 0 |
select * from table where type is not 2 group by target_id order by max(time);
结果如下,因为先要把type是2的行(第一行)删除再分组所以第二行出现在结果中
id | target_id | time | type |
1 | 2 | 400 | 0 |
3 | 3 | 450 | 0 |
5 | 4 | 600 | 0 |
7 | 5 | 400 | 0 |
9 | 6 | 700 | 0 |
select * from table group by target_id having type is not 2 order by max(time);
结果如下,因为要先根据target_id分组但是因为第一行的type是2所以在最后的结果中被过滤掉
id | target_id | time | type |
3 | 3 | 450 | 0 |
5 | 4 | 600 | 0 |
7 | 5 | 400 | 0 |
9 | 6 | 700 | 0 |