分组group by

最新推荐文章于 2022-01-13 10:25:33 发布

changehqw

最新推荐文章于 2022-01-13 10:25:33 发布

阅读量156

点赞数

分类专栏： sql 文章标签： having ROLLUP

本文链接：https://blog.youkuaiyun.com/changehqw/article/details/82753464

版权

sql 专栏收录该内容

6 篇文章

订阅专栏

where VS having

WHERE过滤行，而HAVING过滤分组
WHERE后不能使用聚合函数过滤行，而HAVING可以使用聚合函数过滤分组
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

分组汇总 WITH ROLLUP

select class,ssex,count(sname) from student group by class,ssex;
+-------+------+--------------+
| class | ssex | count(sname) |
+-------+------+--------------+
| 95031 | 女   |            1 |
| 95031 | 男   |            2 |
| 95033 | 女   |            1 |
| 95033 | 男   |            2 |
+-------+------+--------------+

select class,ssex,count(sname) from student group by class,ssex with rollup;
+-------+------+--------------+
| class | ssex | count(sname) |
+-------+------+--------------+
| 95031 | 女   |            1 |
| 95031 | 男   |            2 |
| 95031 | NULL |            3 |
| 95033 | 女   |            1 |
| 95033 | 男   |            2 |
| 95033 | NULL |            3 |
| NULL  | NULL |            6 |
+-------+------+--------------+