SQL点滴25—T-SQL面试语句（转载）

最新推荐文章于 2024-07-06 19:10:24 发布

z470208239

最新推荐文章于 2024-07-06 19:10:24 发布

阅读量572

点赞数

分类专栏： SQLServer 文章标签： sql 面试 insert table servlet delete

SQLServer 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

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)

2. 学生表如下:

自动编号   学号   姓名课程编号课程名称分数
1      2005001 张三 0001   数学    69
2      2005002 李四 0001   数学    89
3      2005001 张三 0001   数学    69
删除除了自动编号不同,其他都相同的学生冗余信息

思路：这个和上面的一样，也不能直接删除，而是要先找出自动编号不相同，其他都相同的行，这个要使用group by语句，并且将其他的字段都放在group by后面，这样找出来的行都是没有冗余的行，然后随便保留其中一个自动编号，删除其他的行。

create table #成绩(姓名varchar(20),课程名称varchar(20),分数int)
insert into #成绩values
('张三',     '语文',       81),
('张三',     '数学',       75),
('李四',     '语文',       76),
('李四',     '数学',       90),
('王五',     '语文',      81),
('王五',     '数学',       100),
('王五',     '英语',       90)

delete #学生表
where 自动编号not in
(select MIN(自动编号) from #学生表group by 学号,姓名,课程编号,课程名称,分数)

3. 一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合。

思路：这是一个组合问题，就是说四个不同的元素有多少种不同的两两组合。现在要把这个问题用sql语句实现。既然这四个元素是不相同的，我们可以将这个表当成连个集合，求他们的笛卡尔积，然后再从笛卡尔积中找到那些元素不相同的，并且不重复的组合。

create table #department(taname char(1))
insert into #department values
('a'),('b'),('c'),('d')

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

注意中括号不可缺少，否则会出错。还有一种写法是使用子查询，这个要新建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

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)

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

有个要注意的地方，第二个fetch语句一定要放在begin和end之间，要不然会死循环的，不常用的语句写起来很不爽快。