我们假定有如下员工信息表:
TableName:DEPARTMENT
id depno sex name
1 1 0 Lily
2 2 1 Tom
3 1 1 Jim
4 1 0 Kite
5 2 1 Green
6 1 1 Mr.Right
7 2 1 Boss
问题:
1.每个部门的男女各自总人数
2.总的员工数及每个部门的总人数
分析:
略...
答案:
1.select depno,sex,count(*) from DEPARTMENT group by depno,sex order by depno,sex;
2.select depno sex,count(*) from DEPARTMENT group by rollup(depno,sex) order by depno desc,sex desc;
注:
1.如果语句中有where语句,则先执行where语句,在,再执行group by语句
2.最好不要对包含空值的列进行分组
3.rollup只会对第一列产生汇总,即带group by后面第一列产生汇总.
如果要对group by后面所有列产生汇总,这时我们就要使用cube函数
如:
select depno,sex,count(*) from DEPARTMENT group by cube(depno,sex) order by depno desc,sex desc;