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

臨沂網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián)公司,臨沂網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為臨沂千余家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)要多少錢，請找那個售后服務(wù)好的臨沂做網(wǎng)站的公司定做！

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

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

 
 
 

  
  
  
create table #成績(姓名varchar(20),課程名稱varchar(20),分數(shù)int)
  
  
  
insert into #成績values
  
  
  
('張三',     '語文',       81),
  
  
  
('張三',     '數(shù)學',       75),
  
  
  
('李四',     '語文',       76),
  
  
  
('李四',     '數(shù)學',       90),
  
  
  
('王五',     '語文',      81),
  
  
  
('王五',     '數(shù)學',       100),
  
  
  
('王五',     '英語',       90)
  
  
  

  
  
  
select distinct(姓名) from #成績 where 姓名 not in(select distinct(姓名) from #成績 where 分數(shù)<=80)
 
 
 

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

 
 
 

  
  
  
select 姓名 from #成績 
  
  
  
group by 姓名
  
  
  
having min(分數(shù))>80
 
 
 

2. 學生表 如下:

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

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

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

 
 
 

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

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

 
 
 

  
  
  
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.分數(shù)=b.分數(shù))
 
 
 

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

思路：這是一個組合問題，就是說四個不同的元素有多少種不同的兩兩組合?，F(xiàn)在要把這個問題用sql語句實現(xiàn)。既然這四個元素是不相同的，我們可以將這個表當成兩個集合，求他們的笛卡爾積，然后再從笛卡爾積中找到那些元素不相同的，并且不重復(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個子查詢進而得到新的列：

 
 
 

  
  
  
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é)果顯式如下(及格分數(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)換來寫，沒有成功，后來沒有辦法只好用游標，代碼如下：

 
 
 

  
  
  
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è)置的話默認是null，后面更新的時候null+'a'任然是null，***得到的value永遠是null。所以默認是''

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

文章標題：數(shù)據(jù)庫點滴之T-SQL面試語句，練練手
標題來源：http://uogjgqi.cn/article/dhjhjhs.html