sql查詢(面試題)，涉及行轉列、複合查詢、函數、 HAVING 、order by、group by、count、case when then、 join

一　前言

看到技術交流群中，有人分享了這個面試題，試的做了下，現在把題目和sql寫下來。

二　題目

　　　　1.　　日誌表SysLog，欄位：用戶編號（UserID），時間（Time）。

　　　　　　　　　　　　　　　　　　　　 　 ......

　　　　2.　　用戶表SysUser，欄位：用戶編號（UserID），姓名（Name）

　　　　　　　　　　　　　　　　　　　　　  ......

 　　　　3.　　按登錄次數排序得到2011年登錄次數大於20次的用戶登陸情況，如下表：

三　sql實現

實現過程：

select u.UserID 用戶編號, u.Name 姓名,
count(case when l.Time is not null then 1 else null end) 登錄次數,
count(distinct convert(char(10),l.Time,120)) 登錄天數,
count(distinct case when convert(char(7),l.Time,120)='2011-01' then 1 else null end) 一月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-02' then 1 else null end) 二月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-03' then 1 else null end) 三月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-04' then 1 else null end) 四月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-05' then 1 else null end) 五月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-06' then 1 else null end) 六月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-07' then 1 else null end) 七月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-08' then 1 else null end) 八月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-09' then 1 else null end) 九月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-10' then 1 else null end) 十月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-11' then 1 else null end) 十一月份登錄次數,
count(distinct case when convert(char(7),l.Time,120)='2011-12' then 1 else null end) 十二月份登錄次數
from [User] u left join [Log] l on u.UserID = l.UserID 
where convert(char(4),l.time,120) = 2011 
group by u.UserID,u.Name  
having count(case when l.Time is not null then 1 else null end) >= 0 
order by  COUNT(case when l.time is not null then 1 else null end) desc

查詢效果：