数据库 复习 分组查询 详细

分组查询

使用union

--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资，最低工资
select '武汉' 地区，count(*) 员工人数 ，sum(peoplesalary) ,avg(peoplesalary),max(peoplesalary),min(epoplesalary) 
from people
where peopleaddress = '武汉'
union
select '北京' 地区, count(*) 员工人数 ，sum(peoplesalary) ,avg(peoplesalary),max(peoplesalary),min(epoplesalary) 
from people
where peopleaddress = '北京'

使用group by分组

select peopleaddress 地区，count(*) 员工人数 ，sum(peoplesalary) 工资总和,avg(peoplesalary) 平均工资,max(peoplesalary) 最大工资,min(epoplesalary) 最小工资 
from people
group by peopleaddress






--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资，最低工资
--要求出生在1985年以后
select peopleaddress 地区，count(*) 员工人数 ，sum(peoplesalary) 工资总和,avg(peoplesalary) 平均工资,max(peoplesalary) 最大工资,min(epoplesalary) 最小工资 
from people
where peoplebirth <'1985-1-1'
group by peopleaddress






--根据员工所在地区分组统计员工人数，员工工资总和，平均工资，最高工资，最低工资
--要求出生在1985年以后，且人数在两人以上
select peopleaddress 地区，count(*) 员工人数 ，sum(peoplesalary) 工资总和,avg(peoplesalary) 平均工资,max(peoplesalary) 最大工资,min(epoplesalary) 最小工资 
from people
where peoplebirth <'1985-1-1'
group by peopleaddress
having count(*) >=2