分组查询

selectfrom People
--统计员工人数 员工工资总和，平均工资，最高工资和最低工资
--方案1 不是一个好方案
select '武汉' 地区, count() 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary)平均工资,
max(PeopleSalary)最高工资,max(PeopleSalary)最低工资
from People
where PeopleAddress = '武汉'
union
select '北京'地区, count() 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,
max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People
where PeopleAddress='北京'
--方案2 group by分组
select PeopleAddress 地区, count() 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary)平均工资,
max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People
group by PeopleAddress

select PeopleAddress 地区,count(*) 员工人数,sum(PeopleSalary)工资总和,avg(PeopleSalary),
max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People
where PeopleBirth<'1985-1-1'
group by PeopleAddress

--根据员工所在地区分组统计员工人数 员工工资总和 平均工资 最高工资和最低工资
--要求筛选处员工人数至少在2人及以上的记录 并且1985年以后出身的员工不参与统计
select PeopleAddress 地区,count() 员工人数,sum(PeopleSalary)工资总和,avg(peopleSalary),
max(PeopleSalary)最高工资,min(PeopleSalary)最低工资
from People
where PeopleBirth<'1985-1-1' --聚合函数不应该出现在where中
group by PeopleAddress
having count() >=2