sql查询(面试题)，涉及行转列、复合查询、函数、 HAVING 、order by、group by、count、case when then、 join

一　前言

看到技术交流群中，有人分享了这个面试题，试的做了下，现在把题目和sql写下来。

 

二　题目

　　　　1.　　日志表SysLog，字段：用户编号（UserID），时间（Time）。

UserID	Time
1	2011-04-11 13:10:36.663
1	2011-08-12 13:10:36.663
1	2011-03-11 15:10:36.663
1	2011-02-11 15:10:36.663
1	2011-01-11 15:10:36.663
1	2011-05-11 15:10:36.663
1	2011-06-11 15:10:36.663
1	2011-07-11 15:10:36.663
1	2011-03-11 15:10:36.663
2	2011-08-12 15:10:36.663
1	2011-08-12 12:10:36.663
1	2011-08-12 02:10:36.663

　　　　　　　　　　　　　　　　　　　　 　 ......

　　　　2.　　用户表SysUser，字段：用户编号（UserID），姓名（Name）

UserID	Name
1	张三
2	李四

　　　　　　　　　　　　　　　　　　　　　  ......

 　　　　3.　　按登录次数排序得到2011年登录次数大于20次的用户登陆情况，如下表：

用户编号	姓名	登录次数	登陆天数	1月登录次数	......	12月登录次数

 

三　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

查询效果：