group by 详解

引用

一道SQL语句面试题，关于group by
表内容：2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

            胜 负
2005-05-09 2 2
2005-05-10 1 2

create table #tmp(rq varchar(10),shengfu nchar(1))   
  
insert into #tmp values('2005-05-09','胜')   
insert into #tmp values('2005-05-09','胜')   
insert into #tmp values('2005-05-09','负')   
insert into #tmp values('2005-05-09','负')   
insert into #tmp values('2005-05-10','胜')   
insert into #tmp values('2005-05-10','负')   
insert into #tmp values('2005-05-10','负')   
  
select * from #tmp   
select rq, shengfu from #tmp group by rq,shengfu   
select rq, shengfu from #tmp group by shengfu,rq   
select rq, shengfu,sum(shengfu) from #tmp group by shengfu,rq   
  
1)
select rq,    
sum(case when shengfu='胜' then 1 else 0 end)'胜',   
sum(case when shengfu='负' then 1 else 0 end)'负'    
from #tmp    
group by rq  

返回每个部门薪水最高的数据
select departmentName,max(salary) as '薪水' from staff group by departmentName
查询员工数超过4人的部门的最高和最低薪水
select departmentName,max(salary) as maxSalary,min(salary) as minSalary from staff group by departmentName having count(*)>4