1. 用一條SQL語句 查詢出每門課都大于80分的學(xué)生姓名

• name kecheng fenshu
• 張三   語文       81
• 張三   數(shù)學(xué)       75
• 李四   語文       76
• 李四   數(shù)學(xué)       90
• 王五   語文       81
• 王五   數(shù)學(xué)     100
• 王五   英語       90

思路：這里不能直接用 分?jǐn)?shù)>80這樣的比較條件來查詢的到結(jié)果，因為要求沒門成績都大于80。我們可以反過來思考，如果有一門成績小于80，那么就不符合要求。先找出成績表中成績<80的多有學(xué)生姓名，不能重復(fù)，然后再用not in找出不再這個集合中的學(xué)生姓名。

create table #成績(姓名varchar(20),課程名稱varchar(20),分?jǐn)?shù)int) 
insert into #成績values 
('張三',     '語文',       81), 
('張三',     '數(shù)學(xué)',       75), 
('李四',     '語文',       76), 
('李四',     '數(shù)學(xué)',       90), 
('王五',     '語文',      81), 
('王五',     '數(shù)學(xué)',       100), 
('王五',     '英語',       90) 
 
select distinct(姓名) from #成績 where 姓名 not in(select distinct(姓名) from #成績 where 分?jǐn)?shù)<=80) 

經(jīng)luofer提示還有一種思路，是用group by + hvaing，這絕對是一種好方法。我估計出這個題的人就是要考察這個知識，代碼如下：

select 姓名 from #成績  
group by 姓名 
having min(分?jǐn)?shù))>80 

2. 學(xué)生表 如下:

• 自動編號     學(xué)號       姓名 課程編號 課程名稱 分?jǐn)?shù)
•        1         2005001    張三      0001          數(shù)學(xué)       69
•        2         2005002    李四      0001          數(shù)學(xué)       89
•        3         2005001    張三      0001          數(shù)學(xué)       69

刪除除了自動編號不同,其他都相同的學(xué)生冗余信息

思路：這個和上面的一樣，也不能直接刪除，而是要先找出自動編號不相同，其他都相同的行，這個要使用group by語句，并且將其他的字段都放在group by后面，這樣找出來的行都是沒有冗余的行，然后隨便保留其中一個自動編號，刪除其他的行。

create table #成績(自動編號 int, 學(xué)號 int,姓名 varchar(20),課程編號 int,課程名稱 varchar(20),分?jǐn)?shù) 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 學(xué)號,姓名,課程編號,課程名稱,分?jǐn)?shù)) 

經(jīng)【廣島之戀】的提醒發(fā)現(xiàn)另外一種思路,代碼如下：

delete from #成績 where 自動編號 not in 
(select distinct(a.自動編號) from #成績 a join #成績 b on a.自動編號>b.自動編號  
where a.學(xué)號=b.學(xué)號 and a.姓名=b.姓名 and a.課程編號=b.課程編號 and a.分?jǐn)?shù)=b.分?jǐn)?shù)) 

3. 一個叫department的表，里面只有一個字段name,一共有4條紀(jì)錄，分別是a,b,c,d,對應(yīng)四個球?qū)ΓF(xiàn)在四個球?qū)M(jìn)行比賽，用一條sql語句顯示所有可能的比賽組合。

思路：這是一個組合問題，就是說四個不同的元素有多少種不同的兩兩組合。現(xiàn)在要把這個問題用sql語句實現(xiàn)。既然這四個元素是不相同的，我們可以將這個表當(dāng)成兩個集合，求他們的笛卡爾積，然后再從笛卡爾積中找到那些元素不相同的，并且不重復(fù)的組合。

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

查成這樣一個結(jié)果

• year  m1 m2 m3 m4
• 1991 1.1 1.2 1.3 1.4
• 1992 2.1 2.2 2.3 2.4

思路：這個很明顯是一個行列轉(zhuǎn)換，首先會想到pivot。結(jié)果中有m1，m2，m3，m4四個新的列，他們需要從原來的行中轉(zhuǎn)換。

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個子查詢進(jìn)而得到新的列：

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中對應(yīng)的value。這道題的SQL語句怎么寫?

思路：這個問題看似簡單，只要一個update語句，然后找到相同的key，更新value字段就可以了。可能你首先會寫成這樣：update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys)。但是要注意的是如果僅僅找相同的key會有很多匹配，更新的時候會出現(xiàn)錯誤，所有要在外層限制。

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. 兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息。

思路：這個就是存在關(guān)系，可以使用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

為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為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

思路：依舊是行列轉(zhuǎn)換，這個在面試中的幾率很高。這個語句還是有兩種寫法，如下：

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能選成以下結(jié)果嗎?

1 ab

2 xyz

思路：這個開始想使用行列轉(zhuǎn)換來寫，沒有成功，后來沒有辦法只好用游標(biāo)，代碼如下：

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字段初始值如果不設(shè)置的話默認(rèn)是null，后面更新的時候null+'a'任然是null，***得到的value永遠(yuǎn)是null。所以默認(rèn)是''

b.第二個fetch語句一定要放在begin和end之間，要不然會死循環(huán)的，不常用的語句寫起來很不爽快

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/10/07/2200500.html

【編輯推薦】

1. 數(shù)據(jù)庫點滴之SQL級聯(lián)刪除
2. 數(shù)據(jù)庫點滴之精妙SQL語句
3. SQL點滴之性能優(yōu)化其實沒有那么神秘
4. SQL點滴之幾個有點偏的語句
5. SQL點滴之如何編輯數(shù)據(jù)