SQL点滴25—T-SQL面试语句，练练手
1. 用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90
思路：这里不能直接用 分数>80这样的比较条件来查询的到结果，因为要求没门成绩都大于80。我们可以反过来思考，如果有一门成绩小于80，那么就不符合要求。先找出成绩表中成绩<80的多有学生姓名，不能重复，然后再用not in找出不再这个集合中的学生姓名。
create table #成绩(姓名varchar(20),课程名称varchar(20),分数int)
insert into #成绩values
('张三', '语文', 81),
('张三', '数学', 75),
('李四', '语文', 76),
('李四', '数学', 90),
('王五', '语文', 81),
('王五', '数学', 100),
('王五', '英语', 90)
select distinct(姓名) from #成绩 where 姓名 not in(select distinct(姓名) from #成绩 where 分数<=80)
经luofer提示还有一种思路，是用group by + hvaing，这绝对是一种好方法。我估计出这个题的人就是要考察这个知识，代码如下：
select 姓名 from #成绩
group by 姓名
having min(分数)>80
还有一种方法类似于第一种
select distinct a.姓名 from #成绩 a where not exists (select 1 from #成绩 where 分数<80 and 姓名=a.姓名)
2. 学生表 如下:
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息
思路：这个和上面的一样，也不能直接删除，而是要先找出自动编号不相同，其他都相同的行，这个要使用group by语句，并且将其他的字段都放在group by后面，这样找出来的行都是没有冗余的行，然后随便保留其中一个自动编号，删除其他的行。
create table #成绩(自动编号 int, 学号 int,姓名 varchar(20),课程编号 int,课程名称 varchar(20),分数 int)
insert into #成绩 values
(1,2005001 ,'张三', 1, '语文', 81),
(2,2005001 ,'李四', 1, '语文', 81),
(3,2005001 ,'张三', 1, '语文', 81),
(4,2005001 ,'张三', 1, '语文', 81)
select * from #成绩
drop table #成绩
delete from #成绩 where 自动编号 not in
(select MIN(自动编号) from #成绩 group by 学号,姓名,课程编号,课程名称,分数)
经广岛之恋的提醒发现另外一种思路,代码如下：
delete from #成绩 where 自动编号 not in
(select distinct(a.自动编号) from #成绩 a join #成绩 b on a.自动编号>b.自动编号
where a.学号=b.学号 and a.姓名=b.姓名 and a.课程编号=b.课程编号 and a.分数=b.分数)
如果不考虑自动编号，还可以这样
--注意identity用法，只能用在有into的select语句中
select identity(int,1,1) as id, 学号,姓名,课程编号,课程名称,分数
into #temp
from #成绩 group by 学号,姓名,课程编号,课程名称,分数
truncate table #成绩
insert into #成绩 select * from #temp
3. 一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合。
思路：这是一个组合问题，就是说四个不同的元素有多少种不同的两两组合。现在要把这个问题用sql语句实现。既然这四个元素是不相同的，我们可以将这个表当成两个集合，求他们的笛卡尔积，然后再从笛卡尔积中找到那些元素不相同的，并且不重复的组合。
create table #department(taname char(1))
insert into #department values
('a'),('b'),('c'),('d')
--下面两条语句都可以，多谢wanglinglong提醒
select a.taname,b.taname from #department a,#department b where a.taname < b.taname
select a.taname,b.taname from #department a,#department b where a.taname > b.taname
4.怎么把这样一个表
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
思路：这个很明显是一个行列转换，首先会想到pivot。结果中有m1，m2，m3，m4四个新的列，他们需要从原来的行中转换。
create table #sales(years int,months int,amount float)
insert into #sales values
(1991, 1, 1.1),
(1991, 2, 1.2),
(1991, 3, 1.3),
(1991, 4, 1.4),
(1992, 1, 2.1),
(1992, 2, 2.2),
(1992, 3, 2.3),
(1992, 4, 2.4)
select pt.years,[1] as m1,[2] as m2,[3] as m3,[4] as m4
from (select sod.amount,sod.months,sod.years as years from #sales sod) so
pivot
(min(so.amount) for so.months in ([1], [2],[3],[4])) as pt
注意[1],[2],[3],[4]中括号不可缺少，否则会出错。还有一种写法是使用子查询，这个要新建4个子查询进而得到新的列：
select a.years,
(select m.amount from #sales m where months=1 and m.years=a.years) as m1,
(select m.amount from #sales m where months=2 and m.years=a.years) as m2,
(select m.amount from #sales m where months=3 and m.years=a.years) as m3,
(select m.amount from #sales m where months=4 and m.years=a.years) as m4
from #sales a group by a.years
还可以这样写，大同小异：
select a.years,
sum(case months when 1 then amount else 0 end) as m1,
sum(case months when 2 then amount else 0 end) as m2,
sum(case months when 3 then amount else 0 end) as m3,
sum(case months when 4 then amount else 0 end) as m4
from #sales a group by a.years
5.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value。这道题的SQL语句怎么写？
思路：这个问题看似简单，只要一个update语句，然后找到相同的key，更新value字段就可以了。可能你首先会写成这样：update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys)。但是要注意的是如果仅仅找相同的key会有很多匹配，更新的时候会出现错误，所以要在外层限制。
create table #a(keys int , value varchar(10))
insert into #a values
(1,'aa'),
(2,'ab'),
(3,'ac')
create table #b(keys int , value varchar(10))
insert into #b values
(1,'aa'),
(2,'a'),
(3,'a')
update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys) where #b.keys in
(select #b.keys from #b,#a where #a.keys=#b.keys and #a.value<>#b.value)
在luofer的提醒之，有了第二个思路
update #b set #b.value=s.value
from (select * from #a except select * from #b) s where s.keys=#b.keys
luofer是牛人啊！
6. 两张关联表，删除主表中已经在副表中没有的信息。
思路：这个就是存在关系，可以使用in，也可以使用exists。
create table #zhubiao(id int,name varchar(5))
insert into #zhubiao values
(1,'aa'),
(2,'ab'),
(3,'ac')
create table #fubiao(id int, grade varchar(5))
insert into #fubiao values
(1,'aa'),
(2,'ab')
delete from #zhubiao where id not in(select b.id from #fubiao b)
delete from #zhubiao where not exists(select 1 from #fubiao where #zhubiao.id=#fubiao.id)
7. 原表:
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
思路：这个就很直接了，使用case语句判断一下。
create table #scores(course int,coursename varchar(10),score int)
insert into #scores values
(1, 'java', 70 ),
(2, 'oracle', 90),
(3, 'xmls', 40),
(4, 'jsp', 30),
(5, 'servlet', 80 )
select course,coursename,
case when score>60 then 'pass' else 'fail' end as mark
from #scores
8. 原表:
id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查询后的表:
id pro1 pro2
1 M F
2 N G
3 B A
思路：依旧是行列转换，这个在面试中的几率很高。这个语句还是有两种写法，如下：
create table #table1(id int,proid int,proname char)
insert into #table1 values
(1, 1, 'M'),
(1, 2, 'F'),
(2, 1, 'N'),
(2, 2, 'G'),
(3, 1, 'B'),
(3, 2, 'A')
select id,
(select proname from #table1 where proid=1 and id=b.id) as pro1,
(select proname from #table1 where proid=2 and id=b.id) as pro2
from #table1 b group by id
select d.id,[1] as pro1,[2] as pro2 from
(select b.id,b.proid,b.proname from #table1 b) as c
pivot
(min(c.proname) for c.proid in([1],[2])) as d
9. 如下
表a
列 a1 a2
记录 1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗？
1 ab
2 xyz
思路：这个开始想使用行列转换来写，没有成功，后来没有办法只好用游标，代码如下：
create table #table2(id int , value varchar(10))
insert into #table2 values
(1,'a'),
(1,'b'),
(2,'x'),
(2,'y'),
(2,'z')
create table #table3(id int,value varchar(100) );insert into #table3(id,value) select distinct(id),'' from #table2
declare @id int,@name varchar(10)
declare mycursor cursor for select * from #table2
open mycursor
fetch next from mycursor into @id,@name
while (@@Fetch_Status = 0)
begin
update #table3 set value=value+@name where id=@id
fetch next from mycursor into @id,@name
end
close mycursor
deallocate mycursor
select * from #table3
有两个要注意的地方，
a.#table3里面的value字段初始值如果不设置的话默认是null，后面更新的时候null+'a'任然是null，最后得到的value永远是null。所以默认是''
b.第二个fetch语句一定要放在begin和end之间，要不然会死循环的，不常用的语句写起来很不爽快
经 scottshen提醒，使用for xml更加的简单，看下面的语句：
SELECT id,
(SELECT value + '' FROM #table2 WHERE id=a.id FOR XML PATH('')) AS [values]
FROM #table2 AS a GROUP BY a.id
--或者这样写
select distinct a.id,
(select b.value+'' from #table2 b where b.id=a.id for XML path('')) as value
from #table2 a
下面这一句帮助我们理解for xml的工作原理
select ''+a.value from #table2 a where id=2 for xml path('')
10. 已经知道原表
year salary
2000 1000
2001 2000
2002 3000
2003 4000
怎么查询的到下面的结果，就是累积工资
year salary
2000 1000
2001 3000
2002 6000
2003 10000
思路：这个需要两个表交叉查询得到当前年的所有过往年，然后再对过往年进行聚合。代码如下：
create table #salary(years int ,salary int )
insert into #salary values
(2000, 1000),
(2001, 2000),
(2002, 3000),
(2003, 4000)
select b.years,SUM(a.salary)
from #salary a,#salary b
where a.years<=b.years
group by b.years
order by b.years
还有一种方法是使用子查询，第一列是年，第二列是所有小于等于第一列这年的工资总和，也比较直接，代码如下：
select
s1.years as years,
(select sum(s2.salary) from #salary s2 where s2.years<=s1.years) as salary
from #salary s1
11. 现在我们假设只有一个table，名为pages，有四个字段，id, url,title,body。里面储存了很多网页，网页的url地址，title和网页的内容，然后你用一个sql查询将url匹配的排在最前， title匹配的其次，body匹配最后，没有任何字段匹配的，不返回。
思路：做过模糊搜索对这个应该很熟悉的，可以使用union all依次向一个临时表中添加记录。这里使用order by和charindex来是实现，代码如下：
create table #page(id int, url varchar(100),title varchar(100), body varchar(100))
insert into #page values
(1,null,'abcde','abcde'),
(2,null,'abcde',null),
(3,'abcde','e',null)
select *
from #page
where url like '%e%' or title like '%e%' or body like '%e%'
order by
case when (charindex('e', url)>0) then 1 else 0 end desc,
case when (charindex('e', title)>0) then 1 else 0 end desc,
case when (charindex('e', body)>0) then 1 else 0 end desc
只要出现一次就会排在前面，这种情况如果两行都出现就会比较下一个字段，以此类推。
还有一种实现，类似于记分牌的思想，如下：
select a.[id],sum(a.mark) as summark from
(
select #page.*,10 as mark from #page where #page.[url] like '%b%'
union
select #page.*,5 as mark from #page where #page.[title] like '%b%'
union
select #page.*,1 as mark from #page where #page.[body] like '%b%'
) as a group by id order by summark desc
12. 表内容：
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
思路：首先要有group by 时间，然后是使用sum统计胜负的个数。代码如下：
create table #scores(dates varchar(10),score varchar(2))
insert into #scores values
('2005-05-09', '胜'),
('2005-05-09', '胜'),
('2005-05-09', '负'),
('2005-05-09', '负'),
('2005-05-10', '胜'),
('2005-05-10', '负'),
('2005-05-10', '负')
select a.dates as [比赛时间],
SUM(case a.score when '胜' then 1 else 0 end) as [胜],
SUM(case a.score when '负' then 1 else 0 end) as [负]
from #scores a
group by a.dates
还有一种方法是使用子查询，先用两个子查询得到这些日期中的胜负常数，然后连接查询，代码如下：
select
t1.dates as [比赛时间],
t1.score as [胜],
t2.score as [负]
from
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='胜' group by a.dates) t1 inner join
(select a.dates as dates, COUNT(1) as score from #scores a where a.score='负' group by a.dates) t2 on t1.dates=t2.dates
13. 表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列
思路：这个字面意思很简单了，就是二者选其一，使用case就可以实现，代码如下：
create table #table3(A int, B int ,C int)
insert into #table3 values
(2,1,3),
(4,2,5)
select
case when A>B then A else B end as AB,
case when B>C then B else C end as BC
from #table3
14. 请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
table1
月份 部门业绩
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门 部门名称
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 （result）
部门部门名称 一月份 二月份 三月份
01 国内业务一部 10 null null
02 国内业务二部 10 8 null
03 国内业务三部 null 5 8
04 国际业务部 null null 9
思路：又是行列转换，不过这个稍微复杂一点代码如下：
create table #table4([月份] varchar(10),[部门] varchar(10),[业绩] int)
insert into #table4 values
('一月份','01','10'),
('一月份','02','10'),
('一月份','03','5'),
('二月份','02','8'),
('二月份','04','9'),
('三月份','03','8')
create table #table5([部门] varchar(10),[部门名称] varchar(50))
insert into #table5 values
('01','国内业务一部'),
('02','国内业务二部'),
('03','国内业务三部'),
('04','国际业务部')
select [部门],[部门名称],[一月份],[二月份],[三月份]
from(select a.[月份] ,a.[部门] as [部门],b.[部门名称],a.[业绩] from #table4 a join #table5 b on a.[部门]=b.[部门] ) sod
pivot(min(sod.[业绩]) for sod.[月份] in([一月份],[二月份],[三月份])) pvt
order by [部门]
注意，这里每个月份每个部门只有一行数据，所以pivot运算的时候可以使用min函数，使用max，min都可以。如果这里有多行数据，那么一般会让计算合计，只能用sum了
还有一种方法是使用子查询，这个代码要多一点，如下：
select a.[部门] ,b.[部门名称],
SUM(case when a.月份='一月份' then a.[业绩] else 0 end) as [一月份],
SUM(case when a.月份='二月份' then a.[业绩] else 0 end) as [二月份],
SUM(case when a.月份='三月份' then a.[业绩] else 0 end) as [三月份]
from #table4 a inner join #table5 b on a.[部门] =b.[部门]group by a.[部门],b.[部门名称]
15. 表结构以及数据如下：
CREATE TABLE #table6
(ID int, 日期 varchar(11), 单据 char(3))
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 1 , '2004-08-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 2 , '2004-09-02' , '001' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 3 , '2004-10-02' , '002' );
INSERT INTO 表 (ID , 日期 , 单据 ) VALUES ( 4 , '2004-09-02' , '002' );
要求:设计一个查询，返回结果如下：
ID 日期 单据
1 2004-08-02 001
4 2004-09-02 002
思路：这个是要找到日期比较小的那一条单据，这个有多种方法实现。第一种方法是相关子查询，如下：
create table #table6
(id int, 日期varchar(11), 单据char(3))
insert into #table6 (id , 日期, 单据) values ( 1 , '2004-08-02' , '001' );
insert into #table6 (id , 日期, 单据) values ( 2 , '2004-09-02' , '001' );
insert into #table6 (id , 日期, 单据) values ( 3 , '2004-10-02' , '002' );
insert into #table6 (id , 日期, 单据) values ( 4 , '2004-09-02' , '002' );
select * from #table6 a
where a.[日期] = (select MIN(b.[日期]) from #table6 b where b.[单据] =a.[单据] )
还可以使用join连接，如下：
select a.*
from #table6 a join
(select b.[单据] , MIN(b.[日期]) as [日期] from #table6 b group by b.[单据]) c
on a.[日期] = c.[日期] and a.[单据] = c.[单据]
注意最后on条件必须是a.[日期] = c.[日期] and a.[单据] = c.[单据]，因为c表只是找出来两组符合条件的数据，如果只是a.[日期] = c.[日期]的话会找出多条不符合要求的数据。
还可以不使用join连接，如下：
select a.*
from #table6 a ,
(select b.[单据] , MIN(b.[日期]) as [日期] from #table6 b group by b.[单据]) c
where a.[日期] = c.[日期] and a.[单据] = c.[单据]
还可以使用谓词exist，如下：
select * from #table6 a
where not exists
(select 1 from #table6 where [单据]=a.[单据] and a.[日期]>[日期])
注意not exists查询筛选得到时间最小的那条记录，注意这里不能使用exists，exists会得到多条。可以理解为a中的日期不会大于子查询中所有日期，就是那个最小的日期。还有去掉[单据]=a.[单据],也会得到更多的数据，这个和普通的情况刚好相反。因为加上这个条件整个子查询会得到更多的数据，否则只保留a.[日期]>[日期]只会得到一条数据。
16. 已知下面的表
id strvalue type
1 how 1
2 are 1
3 you 1
4 fine 2
5 thank 2
6 you 2
要求用sql把它们搜索出来成为这样的
#how are you#fine thank you#
思路：这个和上一篇中的最后一题很相似，也是连接有相同字段的字符，上回使用游标实现的，这次用for xml来实现，代码如下：
create table #table7(id int,strvalue varchar(20),typ int)
insert into #table7 values
(1,'how',1),
(2,'are',1),
(3,'you',1),
(4,'fine',2),
(5,'thank',2),
(6,'you',2)
select * from #table7
select
(select '#'+replace(replace((select strvalue from #table7 t where typ = 1 for xml auto),'<t strvalue="',''),'"/>', '')+'#')
+
(select replace(replace((select strvalue from #table7 t where typ = 2 for xml auto),'<t strvalue="',''),'"/>', '')+'#')
或者这样
select '#'+
ltrim((select ''+a.strvalue from #table7 a where a.typ=1 for xml path('')))+'#'+
ltrim((select ''+a.strvalue from #table7 a where a.typ=2 for xml path('')))+'#'
或者这样，用变量来处理
declare @value varchar(1000)='#'
select @value=''+@value+ a.strvalue+'' from #table7 a where a.typ=1
select @value=@value+'#'
select @value= @value+ a.strvalue+'' from #table7 a where a.typ=2
select @value=@value+'#'
print @value
