數據庫點滴之T-SQL面試語句，練練手
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
【編輯推薦】
