MSSQL综合技术补习---常见笔试题1
为什么写这篇文章
本人最近在找工作，各种面试笔试，很多知识点久了都快忘记了，特别SQL也很久没有写了。
为了快速系统的补习下SQL相关的知识，决定写“MSSQL综合技术补习”系列文章。开始几篇为常见sql面试题型，然后慢慢转向系统的sql知识。
|
考点1---分组聚合条件统计(透视转换) |
这样的题先分组，然后根据条件用case语句来区分条件，最后再聚合条件列。
(虽然透视转换可以使用SQL内置的Pivoting来实现，但是我觉得死语法没有自己写那样思路清楚，所以我没有使用内置的透视转换)
透视转换的步骤：
3个逻辑处理阶段：1.分组 2.扩展 3.聚合
|
例题1 |
|
|
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 |
分析：先根据rq分组，然后根据shengfu扩展，最后再根据shengfu聚合
select
rq as 日期,
sum(case shengfu when '胜' then 1 else 0 end) as 胜,
sum(case shengfu when '负' then 1 else 0 end) as 负
from tmp
group by rq
分辨使用透视转换的场景！
1.数据行是否转换成列，例如胜负是shengfu这个列的2个状态，而且展示结果胜负变成了2个列。
2.转换结果列的数据行是否是聚合结果。
如果上面2个条件都符合，那么就可以使用透视转换来做！
下面也是网上经常出现的sql面试题，我们用他来分析
|
table1 |
||
|
月份mon |
部门dep |
业绩yj |
|
一月份 |
01 |
10 |
|
一月份 |
02 |
10 |
|
一月份 |
03 |
5 |
|
二月份 |
02 |
8 |
|
二月份 |
04 |
9 |
|
三月份 |
03 |
8 |
|
table2 |
|
|
部门dep |
部门名称dname |
|
01 |
国内业务一部 |
|
02 |
国内业务二部 |
|
03 |
国内业务三部 |
|
04 |
国际业务部 |
请用sql得到下面的结果
|
table3 （result） |
|||
|
部门dep |
一月份 |
二月份 |
三月份 |
|
01 |
10 |
null |
null |
|
02 |
10 |
8 |
null |
|
03 |
null |
5 |
8 |
|
04 |
null |
null |
9 |
分析：table1中的mon列的数据1月份，2月份，3月份在table3里面变成了列，而这些列的数据是table1的yj列的sum聚合，所以这里只用透视转换
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份'
from table2 a left join table1 b on a.dep=b.dep
注意下这里是left join，因为你不可能知道部门的所有id，所以用部门的表去left join业绩表。
另外一个重点
就是在聚合数据的时候，需要考虑聚合数据是否存在NULL，如果存在，为了防止数字和NULL计算结果为NULL，应该使用ISNULL函数进行检查。
select 1+null
select sum(1+null)
--结果为NULL
select 1+isnull(null,0)
select sum(1+isnull(null,0))
|
考点2---求符合聚合结果的数据(HAVING) |
这样的题出现在面试题中的机会是很大的，题意的答案几乎都是先分组聚合，再在分组集上使用Having寻找符合题意的数据。
下面给出个最经典的题目。
为管理岗位业务培训信息，建立3个表:
|
S-学员表 |
|||
|
S#-学号 |
SN-学员姓名 |
SD-所属单位 |
SA-学员年龄 |
|
C-课程表 |
|
|
C#-课程编号 |
CN-课程名称 |
|
SC-成绩表 |
||
|
S#-学号 |
C#-课程编号 |
G-学习成绩 |
要求：
使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
做这样的题目需要对Group by 和having有比较好的理解。
创建表和插入数据的sql
分析：
实际应用当中我们一般是不知道状态表会有多少数据的，也就是课程表，所以课程表的课程总数是应该在sql查询中去得到，然后再结合group by 和having来实现解这种类型题的经典解法。
select * from S
where S# in
(
select SC.S# from SC right join C --注意这里能确定数据的表是课程表，所以用课程表来做连接主表
on SC.C#=C.C# --课程ID是连接的条件
group by SC.S# --根据学生ID分组
having count(distinct(SC.C#)) --注意一个同学可能存在一门课多个成绩
=(select count(C#) from C)
)
另外几个相关题目
列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
select S.S#,S.SN,SC1.SG from S join
(
select S#,avg(G) as SG from SC
where G<60 group by S#
having count(distinct C#)>=2
) as SC1
on S.S#=SC1.S#
列出既学过“数学”号课程，又学过“英语”号课程的所有学生姓名
select S.S# from S where S# in
(
select SC.S# from SC join C on SC.C#=C.C#
where C.CN in ('数学','英语')
group by SC.S#
having count(distinct C.CN)=2
)
列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
--这里要考虑到实际应用中可能出现一门课程多条成绩，所以取最高的成绩比较
select t1.S#,t1.CN,t1.G,t2.CN,t2.G from
(select top 1 SC1.S#,C1.CN,SC1.G from SC as SC1 join C as C1
on SC1.C#=C1.C# where C1.CN='英语' order by SC1.G desc)
as t1
join
(select top 1 SC2.S#,C2.CN,SC2.G from SC as SC2 join C as C2
on SC2.C#=C2.C# where C2.CN='数学' order by SC2.G desc)
as t2
on t1.S#=t2.S# where t1.G>t2.G
|
考点3 取出表中的第31条到40条记录 |
取出表A中的第31条到40条记录，ID这里肯定是被破坏了的，不可能让你那么容易的得到。
这里主要是考你分页的知识点，不过比较简单，不多说了。
注意：虽然这里我们表中有ID和number这2个int字段，但是都是不完整的！而且题目不会让你怎么简单的去依靠连续体字段！
--错误的方案-注意order by
select top 10 * from
(
select top 40 * from test1
order by id desc
) as t order by t.id asc
--正确的方案1-结果降序
select top 10 * from
(
select top 40 * from test1
order by id asc
) as t order by t.id desc
--正确的方案2-结果升序
select top 10 * from test1
where id not in
(
select top 30 id from test1
order by id asc
) order by id asc
这里的第一个错误解决方案是因为最后一个order by把结果给升序了，导致出来的不是正确数据，关键在于order by比select 数据列先执行。
|
考点4 数据库中重复的数据 |
这个题目就是超级经典的面试题了，而且解法比较多，这里我做个总结。
|
4-1 查询姓名相同所有重复数据(单字段重复) |
常见的思路：
1：通过子查询去查询每个姓名的分组数据总和，然后筛选大于1的分组。
2：使用exists查询是否存在相同姓名不同ID的数据。
--判断重复数据条数
select * from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name] group by S2.[name]
)>1
--判断ID
select S1.* from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name] and S2.id<>S1.id
)
这2中方法属于比较常见的，如果园友有其它更好的方法请告知。
|
4-2 查询重复数据中最早创建的数据(单字段重复) |
分组已经筛选大于1的分组，然后select分组中最小的id
select * from Student where id in
(
select min(id) from Student
group by [name] having count(id)>1
)
|
4-3 查询重复数据中非最早创建的数据(单字段重复) |
去掉重复数据中最早创建的数据，只查询出后面创建的重复数据
1：使用row_number函数，根据重复字段name分区生成行号，去大于1的行号。
2：求出重复name最小的id，非最早创建的重复数据大于这个最小的id.
select * from Student where id in
(
select id from
(
select id,row_number() over(partition by [name] order by id)
as num from Student
) as t where num>1
)
select S1.* from Student as S1 where S1.id>
(
select min(S2.id) from Student as S2
where S2.[name]=S1.[name]
)
|
4-3 查询多字段重复的数据(name,age,sex) |
这个和查询单字段重复数据思想都是一样，只是在判断重复字段的地方要写上需要判断的所有字段，如果有更好的方法请联系我谢谢。
--判断重复数据条数
select * from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name]
and S2.age=S1.age
and S2.sex=S1.sex
group by S2.[name]
)>1
--判断ID
select S1.* from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name]
and S2.age=S1.age
and S2.sex=S1.sex
and S2.id<>S1.id
)
|
4-5 删除重复数据 |
要求：删除全部重复数据
--判断重复数据条数
delete from Student where id in
(
select id from Student as S1 where
(
select count(id) from Student as S2
where S2.[name]=S1.[name] group by S2.[name]
)>1
)
--判断ID
delete from Student where id in
(
select S1.id from Student as S1 where exists
(
select S2.id from Student as S2
where S2.[name]=S1.[name] and S2.id<>S1.id
)
)
对于要求保留一条重复数据的题目，可以使用重复数据中最早创建的数据的SQL，获取到id再删除，多字段重复都是相同思路。
作者：海不是蓝
博客：http://www.cnblogs.com/hailan2012/
邮箱：hailan2012@sina.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
