数据库点滴之T-SQL面试语句，练练手

1. 用一条SQL语句 查询出每门课都大于80分的学生姓名

临沂网站建设公司成都创新互联公司,临沂网站设计制作，有大型网站制作公司丰富经验。已为临沂千余家提供企业网站建设服务。企业网站搭建\成都外贸网站建设要多少钱，请找那个售后服务好的临沂做网站的公司定做！

  • name kecheng fenshu
  • 张三   语文       81
  • 张三   数学       75
  • 李四   语文       76
  • 李四   数学       90
  • 王五   语文       81
  • 王五   数学     100
  • 王五   英语       90

思路：这里不能直接用 分数>80这样的比较条件来查询的到结果，因为要求没门成绩都大于80。我们可以反过来思考，如果有一门成绩小于80，那么就不符合要求。先找出成绩表中成绩<80的多有学生姓名，不能重复，然后再用not in找出不再这个集合中的学生姓名。

 
 
 
    
  
  
  
  1. create table #成绩(姓名varchar(20),课程名称varchar(20),分数int)
    2. 
  
  
  
  2. insert into #成绩values
    3. 
  
  
  
  3. ('张三',     '语文',       81),
    4. 
  
  
  
  4. ('张三',     '数学',       75),
    5. 
  
  
  
  5. ('李四',     '语文',       76),
    6. 
  
  
  
  6. ('李四',     '数学',       90),
    7. 
  
  
  
  7. ('王五',     '语文',      81),
    8. 
  
  
  
  8. ('王五',     '数学',       100),
    9. 
  
  
  
  9. ('王五',     '英语',       90)
    10. 
  
  
  
  10. 
  
  
  
  11. select distinct(姓名) from #成绩 where 姓名 not in(select distinct(姓名) from #成绩 where 分数<=80)
    12.

经luofer提示还有一种思路，是用group by + hvaing，这绝对是一种好方法。我估计出这个题的人就是要考察这个知识，代码如下：

 
 
 
    
  
  
  
  1. select 姓名 from #成绩 
    2. 
  
  
  
  2. group by 姓名
    3. 
  
  
  
  3. having min(分数)>80
    4.

2. 学生表 如下:

  • 自动编号     学号       姓名 课程编号 课程名称 分数
  •        1         2005001    张三      0001          数学       69
  •        2         2005002    李四      0001          数学       89
  •        3         2005001    张三      0001          数学       69

删除除了自动编号不同,其他都相同的学生冗余信息

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

 
 
 
    
  
  
  
  1. create table #成绩(自动编号 int, 学号 int,姓名 varchar(20),课程编号 int,课程名称 varchar(20),分数 int)
    2. 
  
  
  
  2. insert into #成绩 values
    3. 
  
  
  
  3. (1,2005001 ,'张三',  1,   '语文',       81),
    4. 
  
  
  
  4. (2,2005001 ,'李四',  1,   '语文',       81),
    5. 
  
  
  
  5. (3,2005001 ,'张三',  1,   '语文',       81),
    6. 
  
  
  
  6. (4,2005001 ,'张三',  1,   '语文',       81)
    7. 
  
  
  
  7. 
  
  
  
  8. select * from #成绩
    9. 
  
  
  
  9. drop table #成绩
    10. 
  
  
  
  10. 
  
  
  
  11. delete from #成绩 where 自动编号 not in
    12. 
  
  
  
  12. (select MIN(自动编号) from #成绩 group by 学号,姓名,课程编号,课程名称,分数)
    13.

经【广岛之恋】的提醒发现另外一种思路,代码如下：

 
 
 
    
  
  
  
  1. delete from #成绩 where 自动编号 not in
    2. 
  
  
  
  2. (select distinct(a.自动编号) from #成绩 a join #成绩 b on a.自动编号>b.自动编号 
    3. 
  
  
  
  3. where a.学号=b.学号 and a.姓名=b.姓名 and a.课程编号=b.课程编号 and a.分数=b.分数)
    4.

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

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

 
 
 
    
  
  
  
  1. create table #department(taname char(1))
    2. 
  
  
  
  2. insert into #department values
    3. 
  
  
  
  3. ('a'),('b'),('c'),('d')
    4. 
  
  
  
  4. 
  
  
  
  5. --下面两条语句都可以，多谢wanglinglong提醒
    6. 
  
  
  
  6. select a.taname,b.taname from #department a,#department b where a.taname < b.taname
    7. 
  
  
  
  7. select a.taname,b.taname from #department a,#department b where a.taname > b.taname
    8.

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四个新的列，他们需要从原来的行中转换。

 
 
 
    
  
  
  
  1. create table #sales(years int,months int,amount float)
    2. 
  
  
  
  2. insert into #sales values
    3. 
  
  
  
  3. (1991,   1,     1.1),
    4. 
  
  
  
  4. (1991,   2,     1.2),
    5. 
  
  
  
  5. (1991,   3,     1.3),
    6. 
  
  
  
  6. (1991,   4,     1.4),
    7. 
  
  
  
  7. (1992,   1,     2.1),
    8. 
  
  
  
  8. (1992,   2,     2.2),
    9. 
  
  
  
  9. (1992,   3,     2.3),
    10. 
  
  
  
  10. (1992,   4,     2.4)
    11. 
  
  
  
  11. 
  
  
  
  12. select pt.years,[1] as m1,[2] as m2,[3] as m3,[4] as m4 
    13. 
  
  
  
  13. from (select sod.amount,sod.months,sod.years as years from  #sales sod)  so 
    14. 
  
  
  
  14. pivot
    15. 
  
  
  
  15. (min(so.amount) for so.months in ([1], [2],[3],[4])) as pt
    16.

注意[1],[2],[3],[4]中括号不可缺少，否则会出错。还有一种写法是使用子查询，这个要新建4个子查询进而得到新的列：

 
 
 
    
  
  
  
  1. select a.years,
    2. 
  
  
  
  2. (select m.amount from #sales m where months=1 and m.years=a.years) as m1,
    3. 
  
  
  
  3. (select m.amount from #sales m where months=2 and m.years=a.years) as m2,
    4. 
  
  
  
  4. (select m.amount from #sales m where months=3 and m.years=a.years) as m3,
    5. 
  
  
  
  5. (select m.amount from #sales m where months=4 and m.years=a.years) as m4
    6. 
  
  
  
  6. from #sales a group by a.years
    7.

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会有很多匹配，更新的时候会出现错误，所有要在外层限制。

 
 
 
    
  
  
  
  1. create table #a(keys int , value varchar(10))
    2. 
  
  
  
  2. insert into #a values
    3. 
  
  
  
  3. (1,'aa'),
    4. 
  
  
  
  4. (2,'ab'),
    5. 
  
  
  
  5. (3,'ac')
    6. 
  
  
  
  6. create table #b(keys int , value varchar(10))
    7. 
  
  
  
  7. insert into #b values
    8. 
  
  
  
  8. (1,'aa'),
    9. 
  
  
  
  9. (2,'a'),
    10. 
  
  
  
  10. (3,'a')
    11. 
  
  
  
  11. 
  
  
  
  12. update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys) where #b.keys in
    13. 
  
  
  
  13. (select #b.keys from #b,#a where #a.keys=#b.keys and #a.value<>#b.value)
    14.

在luofer的提醒之，有了第二个思路

 
 
 
    
  
  
  
  1. update #b set #b.value=s.value
    2. 
  
  
  
  2. from (select * from #a except select * from #b) s where s.keys=#b.keys
    3.

luofer是牛人啊!

6. 两张关联表，删除主表中已经在副表中没有的信息。

思路：这个就是存在关系，可以使用in，也可以使用exists。

 
 
 
    
  
  
  
  1. create table #zhubiao(id int,name varchar(5))
    2. 
  
  
  
  2. insert into #zhubiao values
    3. 
  
  
  
  3. (1,'aa'),
    4. 
  
  
  
  4. (2,'ab'),
    5. 
  
  
  
  5. (3,'ac')
    6. 
  
  
  
  6. create table #fubiao(id int, grade varchar(5))
    7. 
  
  
  
  7. insert into #fubiao values
    8. 
  
  
  
  8. (1,'aa'),
    9. 
  
  
  
  9. (2,'ab')
    10. 
  
  
  
  10. 
  
  
  
  11. delete from #zhubiao where id not in(select b.id from #fubiao b)
    12. 
  
  
  
  12. delete from #zhubiao where not exists(select 1 from #fubiao where #zhubiao.id=#fubiao.id)
    13.

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语句判断一下。

 
 
 
    
  
  
  
  1. create table #scores(course int,coursename varchar(10),score int)
    2. 
  
  
  
  2. insert into #scores values
    3. 
  
  
  
  3. (1, 'java', 70 ),
    4. 
  
  
  
  4. (2, 'oracle', 90),
    5. 
  
  
  
  5. (3, 'xmls', 40),
    6. 
  
  
  
  6. (4, 'jsp', 30), 
    7. 
  
  
  
  7. (5, 'servlet', 80 )
    8. 
  
  
  
  8. 
  
  
  
  9. select course,coursename,
    10. 
  
  
  
  10. case when score>60 then 'pass' else 'fail' end as mark
    11. 
  
  
  
  11. from #scores
    12.

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

思路：依旧是行列转换，这个在面试中的几率很高。这个语句还是有两种写法，如下：

 
 
 
    
  
  
  
  1. create table #table1(id int,proid int,proname char)
    2. 
  
  
  
  2. insert into #table1 values
    3. 
  
  
  
  3. (1, 1, 'M'),
    4. 
  
  
  
  4. (1, 2, 'F'), 
    5. 
  
  
  
  5. (2, 1, 'N'), 
    6. 
  
  
  
  6. (2, 2, 'G'), 
    7. 
  
  
  
  7. (3, 1, 'B'), 
    8. 
  
  
  
  8. (3, 2, 'A')
    9. 
  
  
  
  9. 
  
  
  
  10. select id, 
    11. 
  
  
  
  11. (select proname from #table1 where proid=1 and id=b.id) as pro1,
    12. 
  
  
  
  12. (select proname from #table1 where proid=2 and id=b.id) as pro2
    13. 
  
  
  
  13. from #table1 b group by id
    14. 
  
  
  
  14. 
  
  
  
  15. select d.id,[1] as pro1,[2] as pro2 from
    16. 
  
  
  
  16. (select b.id,b.proid,b.proname from #table1 b) as c
    17. 
  
  
  
  17. pivot
    18. 
  
  
  
  18. (min(c.proname) for c.proid in([1],[2])) as d
    19.

9. 如下

  • 表a
  • 列     a1 a2
  • 记录 1   a
  •           1   b
  •           2   x
  •           2   y
  •           2   z

用select能选成以下结果吗?

1 ab

2 xyz

思路：这个开始想使用行列转换来写，没有成功，后来没有办法只好用游标，代码如下：

 
 
 
    
  
  
  
  1. create table #table2(id int , value varchar(10))
    2. 
  
  
  
  2. insert into #table2 values
    3. 
  
  
  
  3. (1,'a'),
    4. 
  
  
  
  4. (1,'b'),
    5. 
  
  
  
  5. (2,'x'),
    6. 
  
  
  
  6. (2,'y'),
    7. 
  
  
  
  7. (2,'z')
    8. 
  
  
  
  8. create table #table3(id int,value varchar(100) );insert into #table3(id,value) select distinct(id),'' from #table2
    9. 
  
  
  
  9. 
  
  
  
  10. declare @id int,@name varchar(10)
    11. 
  
  
  
  11. declare mycursor cursor for select * from #table2
    12. 
  
  
  
  12. open mycursor
    13. 
  
  
  
  13. fetch next from mycursor into @id,@name
    14. 
  
  
  
  14. while (@@Fetch_Status = 0)
    15. 
  
  
  
  15. begin
    16. 
  
  
  
  16. update #table3 set value=value+@name where id=@id
    17. 
  
  
  
  17. fetch next from mycursor into @id,@name 
    18. 
  
  
  
  18. end
    19. 
  
  
  
  19. close mycursor
    20. 
  
  
  
  20. deallocate mycursor
    21. 
  
  
  
  21. 
  
  
  
  22. select * from #table3
    23.

有两个要注意的地方，

a.#table3里面的value字段初始值如果不设置的话默认是null，后面更新的时候null+'a'任然是null，***得到的value永远是null。所以默认是''

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

分享文章：数据库点滴之T-SQL面试语句，练练手
网站地址：http://www.36103.cn/qtweb/news18/14018.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

品牌网站建设知识

行业网站建设

成都主机托管　　 　成都网站制作　　 　网络推广公司　　 　成都外贸网站建设公司　　 　免备案空间　　 　二枢服务器托管　　 　草坪音箱　　 　攀枝花网站建设　　 　成都宣传海报　　 　网站营销　　 　企业网站建设公司　　 　成都定制网站建设　　 　软文发稿　　 　app开发公司　　 　成都雪糕加盟　　 　网站设计　　 　王君美油画　　 　无边发光字厂家　　 　成都IDC机房托管　　 　自动门