SQL随笔_1

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('')

 

转载于:https://www.cnblogs.com/altman29/archive/2012/05/13/2498652.html