1. 用一條 SQL 語句 查詢出每門課都大于 80 分的學生姓名 ?
name?? kecheng??
??
fenshu
張三
????
語文
?????81
張三
????
數(shù)學
?????75
李四
????
語文
?????76
李四
????
數(shù)學
?????90
王五
????
語文
?????81
王五
????
數(shù)學
?????100
王五
????
英語
?????90
思路：這里不能直接用 分數(shù) >80 這樣的比較條件來查詢的到結果，因為要求沒門成績都大于 80 。我們可以反過來思考，如果有一門成績小于 80 ，那么就不符合要求。先找出成績表中成績 <80 的多有學生姓名，不能重復，然后再用 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
還有一種方法類似于第一種
select
distinct
a.姓名
from
#成績 a
where
not
exists
(
select
1
from
#成績
where
分數(shù)
<
80
and
姓名
=
a.姓名)
?
?
?
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ù))
如果不考慮自動編號，還可以這樣
?
--
注意identity用法，只能用在有into的select語句中
select
identity
(
int
,
1
,
1
)
as
id, 學號,姓名,課程編號,課程名稱,分數(shù)
into
#
temp
from
#成績
group
by
學號,姓名,課程編號,課程名稱,分數(shù)
truncate
table
#成績
insert
into
#成績
select
*
from
#
temp
?
3. 一個叫 department 的表，里面只有一個字段 name, 一共有 4 條紀錄，分別是 a,b,c,d, 對應四個球對，現(xiàn)在四個球對進行比賽，用一條 sql 語句顯示所有可能的比賽組合。
思路：這是一個組合問題，就是說四個不同的元素有多少種不同的兩兩組合。現(xiàn)在要把這個問題用 sql 語句實現(xiàn)。既然這四個元素是不相同的，我們可以將這個表當成兩個集合，求他們的笛卡爾積，然后再從笛卡爾積中找到那些元素不相同的，并且不重復的組合。
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 會有很多匹配，更新的時候會出現(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 是牛人啊！
再舉一個例子，已知有一個課程表PressErp..Course，里面已經(jīng)有一些數(shù)據(jù)，現(xiàn)在要向教師表[Press].[dbo].[Teacher]中添加新數(shù)據(jù)，但是有的老師課程可能不在這個已知的課程表中，這時候就可以用到這個except了，它的作用就是向這個課程表中添加以前不存在的課程名稱。注意關鍵字distinct和except。
insert
into
Course
select
distinct
(課程)
from
[
Press
]
.
[
dbo
]
.
[
Teacher
]
a
except
select
b.CourseName
from
Course b
?
?
6. 兩張關聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息。
思路：這個就是存在關系，可以使用 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
為了便于閱讀 , 查詢此表后的結果顯式如下 ( 及格分數(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
思路：依舊是行列轉換，這個在面試中的幾率很高。這個語句還是有兩種寫法，如下：
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 之間，要不然會死循環(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.一個業(yè)務有多個訪談信息，要求每次添加訪談信息的時候都要更新業(yè)務中的當前添加的訪談信息的ID，這樣可以快速查找當前業(yè)務的最新狀態(tài)。
update
MaintainMessage
set
CurrentCommunicateID
=
(
select
MAX
(a.ID)
from
Communicate a
where
a.MaintainId
=
MaintainMessage.ID)
注意MaintainMessage.ID中的MaintainMessage不能省略。
?
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
