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é)果，因?yàn)橐鬀]門成績都大于 80 。我們可以反過來思考，如果有一門成績小于 80 ，那么就不符合要求。先找出成績表中成績 <80 的多有學(xué)生姓名，不能重復(fù)，然后再用 not in 找出不再這個(gè)集合中的學(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，這絕對(duì)是一種好方法。我估計(jì)出這個(gè)題的人就是要考察這個(gè)知識(shí)，代碼如下：
select
姓名
from
#成績
group
by
姓名
having
min
(分?jǐn)?shù))
>
80
還有一種方法類似于第一種
select
distinct
a.姓名
from
#成績 a
where
not
exists
(
select
1
from
#成績
where
分?jǐn)?shù)
<
80
and
姓名
=
a.姓名)
?
?
?
2. 學(xué)生表 如下 :
自動(dòng)編號(hào)
??
學(xué)號(hào)
??
姓名
?
課程編號(hào)
?
課程名稱
?
分?jǐn)?shù)
1??????2005001?
張三
?
0001???
數(shù)學(xué)
??? 69
2??????2005002?
李四
?
0001???
數(shù)學(xué)
??? 89
3??????2005001?
張三
?0001???
數(shù)學(xué)
??? 69
刪除除了自動(dòng)編號(hào)不同
,
其他都相同的學(xué)生冗余信息
?
思路：這個(gè)和上面的一樣，也不能直接刪除，而是要先找出自動(dòng)編號(hào)不相同，其他都相同的行，這個(gè)要使用 group by 語句，并且將其他的字段都放在 group by 后面，這樣找出來的行都是沒有冗余的行，然后隨便保留其中一個(gè)自動(dòng)編號(hào)，刪除其他的行。
?
create
table
#成績(自動(dòng)編號(hào)
int
, 學(xué)號(hào)
int
,姓名
varchar
(
20
),課程編號(hào)
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
自動(dòng)編號(hào)
not
in
(
select
MIN
(自動(dòng)編號(hào))
from
#成績
group
by
學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù))
經(jīng) 廣島之戀 的提醒發(fā)現(xiàn)另外一種思路,代碼如下：
delete
from
#成績
where
自動(dòng)編號(hào)
not
in
(
select
distinct
(a.自動(dòng)編號(hào))
from
#成績 a
join
#成績 b
on
a.自動(dòng)編號(hào)
>
b.自動(dòng)編號(hào)
where
a.學(xué)號(hào)
=
b.學(xué)號(hào)
and
a.姓名
=
b.姓名
and
a.課程編號(hào)
=
b.課程編號(hào)
and
a.分?jǐn)?shù)
=
b.分?jǐn)?shù))
如果不考慮自動(dòng)編號(hào)，還可以這樣
?
--
注意identity用法，只能用在有into的select語句中
select
identity
(
int
,
1
,
1
)
as
id, 學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù)
into
#
temp
from
#成績
group
by
學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù)
truncate
table
#成績
insert
into
#成績
select
*
from
#
temp
?
3. 一個(gè)叫 department 的表，里面只有一個(gè)字段 name, 一共有 4 條紀(jì)錄，分別是 a,b,c,d, 對(duì)應(yīng)四個(gè)球?qū)ΓF(xiàn)在四個(gè)球?qū)M(jìn)行比賽，用一條 sql 語句顯示所有可能的比賽組合。
思路：這是一個(gè)組合問題，就是說四個(gè)不同的元素有多少種不同的兩兩組合。現(xiàn)在要把這個(gè)問題用 sql 語句實(shí)現(xiàn)。既然這四個(gè)元素是不相同的，我們可以將這個(gè)表當(dāng)成兩個(gè)集合，求他們的笛卡爾積，然后再從笛卡爾積中找到那些元素不相同的，并且不重復(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.
怎么把這樣一個(gè)表
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
查成這樣一個(gè)結(jié)果
year
?
m1??m2??m3??m4
1991
?
1.1
??
1.2
??
1.3
??
1.4
1992
?
2.1
??
2.2
??
2.3
??
2.4
思路：這個(gè)很明顯是一個(gè)行列轉(zhuǎn)換，首先會(huì)想到 pivot 。結(jié)果中有 m1 ， m2 ， m3 ， m4 四個(gè)新的列，他們需要從原來的行中轉(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]中括號(hào)不可缺少，否則會(huì)出錯(cuò)。還有一種寫法是使用子查詢，這個(gè)要新建 4 個(gè)子查詢進(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
還可以這樣寫，大同小異：
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. 有兩個(gè)表 A 和 B ，均有 key 和 value 兩個(gè)字段，如果 B 的 key 在 A 中也有，就把 B 的 value 換為 A 中對(duì)應(yīng)的 value 。這道題的 SQL 語句怎么寫？
思路：這個(gè)問題看似簡單，只要一個(gè) update 語句，然后找到相同的 key ，更新 value 字段就可以了。可能你首先會(huì)寫成這樣： update #b set #b.value=(select #a.value from #a where #a.keys=#b.keys) 。但是要注意的是如果僅僅找相同的 key 會(huì)有很多匹配，更新的時(shí)候會(huì)出現(xiàn)錯(cuò)誤，所以要在外層限制。
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 的提醒之，有了第二個(gè)思路
update
#b
set
#b.value
=
s.value
from
(
select
*
from
#a
except
select
*
from
#b) s
where
s.keys
=
#b.keys
luofer 是牛人啊！
再舉一個(gè)例子，已知有一個(gè)課程表PressErp..Course，里面已經(jīng)有一些數(shù)據(jù)，現(xiàn)在要向教師表[Press].[dbo].[Teacher]中添加新數(shù)據(jù)，但是有的老師課程可能不在這個(gè)已知的課程表中，這時(shí)候就可以用到這個(gè)except了，它的作用就是向這個(gè)課程表中添加以前不存在的課程名稱。注意關(guān)鍵字distinct和except。
insert
into
Course
select
distinct
(課程)
from
[
Press
]
.
[
dbo
]
.
[
Teacher
]
a
except
select
b.CourseName
from
Course b
?
?
6. 兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息。
思路：這個(gè)就是存在關(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
思路：這個(gè)就很直接了，使用 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)換，這個(gè)在面試中的幾率很高。這個(gè)語句還是有兩種寫法，如下：
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
思路：這個(gè)開始想使用行列轉(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
?
有兩個(gè)要注意的地方，
a.#table3里面的value字段初始值如果不設(shè)置的話默認(rèn)是null，后面更新的時(shí)候null+'a'任然是null，最后得到的value永遠(yuǎn)是null。所以默認(rèn)是''
b.第二個(gè) fetch 語句一定要放在 begin 和 end 之間，要不然會(huì)死循環(huán)的，不常用的語句寫起來很不爽快
經(jīng) 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(
''
)
?
10.一個(gè)業(yè)務(wù)有多個(gè)訪談信息，要求每次添加訪談信息的時(shí)候都要更新業(yè)務(wù)中的當(dāng)前添加的訪談信息的ID，這樣可以快速查找當(dāng)前業(yè)務(wù)的最新狀態(tài)。
update
MaintainMessage
set
CurrentCommunicateID
=
(
select
MAX
(a.ID)
from
Communicate a
where
a.MaintainId
=
MaintainMessage.ID)
注意MaintainMessage.ID中的MaintainMessage不能省略。
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
