sql分组聚合练习

1、 按性别统计客户分别有多少

预期结果：

select Gender,COUNT(*) 
from p_customer 
group by Gender order by Gender

2、 查询湖南、湖北、山东三省各创建人(createdby)创建了多少客户。

预期结果：

select 
case when CreatedBy <>'' then CreatedBy end as '创建人' ,
sum(case when  Province='湖南' then 1 else 0 end ) as '湖南',
sum(case when Province='湖北' then 1 else 0 end) as '湖北',
sum(case when Province='山东' then 1 else 0 end) as '山东'
from p_Customer
group by CreatedBy
order by CreatedBy

 

3、 查询5日内过生日的客户姓名及生日(不考虑年末5天情况。如果能考虑则更好)

预期结果：

select CstName 客户姓名,BirthDate 客户生日
from p_Customer
where 
( 
dateadd(year, year( GETDATE() ) - year(BirthDate) , BirthDate )
between GETDATE() and dateadd(day,5,GETDATE()) 
)
or
(
dateadd(year, year( GETDATE() ) - year(BirthDate)+1 , BirthDate )
between GETDATE() and dateadd(day,5,GETDATE())
)
ORDER BY BirthDate

4、  查询5条创建客户记录最多的创建人姓名,按创建记录数从大到小排序

预期结果：

select 
top 5 CreatedBy,
case when CreatedBy<>'' then COUNT(*) end as ' 创建人数'
from p_customer
group by CreatedBy
order by COUNT(*) desc

5、  查询创建过同名客户的创建人（即同一人创建过2个以上名字相同的客户）

预期结果：

select distinct CreatedBy
from p_customer
group by CstName,CreatedBy
having COUNT(*)>1