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
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.分数)
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
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之间，要不然会死循环的，不常用的语句写起来很不爽快
原文链接：http://www.cnblogs.com/tylerdonet/archive/2011/10/07/2200500.html
【编辑推荐】
