看完測試完下面這些試題，你的ｓｑｌ水平一定會有新的提高。
下面我們先看一下題設:
二維表 t(f1,f2,f3,f4,f5,f6,f7) 表示如下關系:
┌─────┬────┬─────┬─────┬─────┬─────┬─────┐
│ 學生id │學生姓名│ 課程id │ 課程名稱 │ 成績 │ 教師id │ 教師姓名 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k4 │ 政治 │ 53 │ t4 │ 趙老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 張三 │ k1 │ 數學 │ 61 │ t1 │ 張老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k3 │ 英語 │ 88 │ t3 │ 李老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 張三 │ k4 │ 政治 │ 77 │ t4 │ 趙老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k4 │ 政治 │ 67 │ t5 │ 周老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k2 │ 語文 │ 90 │ t2 │ 王老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s3 │ 王五 │ k1 │ 數學 │ 55 │ t1 │ 張老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 張三 │ k2 │ 語文 │ 81 │ t2 │ 王老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s4 │ 趙六 │ k2 │ 語文 │ 59 │ t1 │ 王老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s1 │ 張三 │ k3 │ 英語 │ 37 │ t3 │ 李老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ s2 │ 李四 │ k1 │ 數學 │ 81 │ t1 │ 張老師 │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ .... │ │ │ │ │ │ │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│ .... │ │ │ │ │ │ │
└─────┴────┴─────┴─────┴─────┴─────┴─────┘
為便于大家更好的理解,我們將 t 表起名為"成績表"
1.如果 t 表還有一字段 f 數據類型為自動增量整型(唯一,不會重復),
而且 t 表中含有除 f 字段外,請刪除其它字段完全相同的重復多余的臟記錄數據:
本問題就是一個清理"邏輯重復"記錄的問題,當然,這種情況完全可以利用主鍵約束來
杜絕!然而,現實情況經常是原始數據在"洗滌"后,方可安全使用,而且邏輯主鍵過早的
約束,將會給采集原始數據帶來不便,例如:從刷卡機上讀取考勤記錄。到了應用數據
的時候,臟數據就該被掃地出門了! 之所以題中要保留一個自動標識列,是因為它的確
是下面答案所必須的前提:
delete l
from "成績表" l
join "成績表" r
on l."學生id" = r."學生id" and l."課程id" = r."課程id" and l.f > r.f
這是思路最精巧且最直接有效的方法之一。用不等自聯接,正好可以將同一組重復數
據中 f 字段值最小的那一條留下,并選出其它的刪掉,如果只有一條,自然也不會被選
中了。這里還要強調一下,大家一定要分清楚被操作的基本表也就是 delete 關鍵字
后的表和過濾條件所使用的由基本表連接而成的二維表數據集,也就是 from 子句的
全部。在自連接的 from 子句至少要取一個別名來引用基本表。別名的使用在編寫大
量類似結構的 sql 時非常方便,而且利于統一程序構造動態 sql。如有必要加強條件,
還可繼續使用 where 子句。如果上面的例子還不夠直觀,下面模仿一個不等自聯接,
有一組數 (1,2,3),作一個不等自聯接,令左子集大于右子集,是:
2 1
3 1
3 2
如果現在選出左子集,就是 2 和 3 了。1 在右邊沒有比它更小的數據可以與之匹配,
因此被過濾了。如果數據大量重復,效率會差強人意,幸虧不是 select ,而是 delete
無需返回結果集,影響自然小多了。
delete t
from 成績表 t
where f not in (select min(f)
from 成績表 i
group by i.學生id,i.課程id
having count(*)>1
)
and f not in (select min(f)
from 成績表 i
group by i.學生id, i.課程id
having count(*)=1
)
這種方法思路很簡單,就像翻譯自然語言,很精確地描述了符合條件記錄的特性,甚至
第二個條件的確多余。至少應該用一個 >= 號合并這兩個條件或只保留任意一個條件,
提高效率。
delete t
from 成績表 t
where f > (select min(f)
from 成績表 as i
where i.學生id = t.學生id
and i.課程id = t.課程id
group by i.學生id, i.課程id
)
這種方法,基本上是方法一的相關子查詢版本,了解笛卡爾積的讀者能會好理解些,而
且用到了統計函數,因此效率不是太高。細心的讀者會發現子查詢里的 group by 子
句沒有必要,去掉它應該會提高一些效率的。
關于 delete 語句的調試,有經驗的程序員都會先用無害的 select 暫時代替危險的
delete。例如:
select l.*
--delect l 暫時注釋掉
from "成績表" l
join "成績表" r
on l."學生id" = r."學生id" and l."課程id" = r."課程id" and l.f>r.f
這樣,極大地減小了在線數據被無意破壞的可能性,當然數據提前備份也很重要。同理
update 和 insert 寫操作也應照此行事。從數據庫原理的關系運算的角度來看 insert、
update 和 delete 這些寫操作都屬于典型的"選擇(selection)"運算,update 和 insert
而且還是"投影(projection)"運算,它們都是這些關系運算的"寫"應用的表現形式。
其實,查詢的目的也本來無非就是瀏覽、刪除、更
新或插入。通常寫操作也比讀操作消耗更大,如果索引過多,只會降低效率。
選擇"子查詢"還是"連接"在效率是有差別的,但最關鍵的差別還是表現在查詢的結果
集的讀寫性上,開發人員在寫一個"只讀"應用的查詢記錄集時,"子查詢"和"連接"各自
的效率就是應該首先考慮的問題,但是如果要實現"可寫"應用的查詢結果集,則無論是
相關還是非相關子查詢都是在復雜應用中難以避免的。
以上解決方案中,應該說第一種方法,簡潔有效,很有創意,是值得推薦的方法。當然,
最簡單的寫法應該是:
delete t
from t,t t1
where t.學生id=t1.學生id and t.課程id=t.課程id and t.f < t1.f
其實這就是方法一的"標準"(但確實實不是《ansi/iso sql》標準)連接寫法,以下各
題答案為了便于讀者理解,一般不采用這種寫法,這也是《ansi/iso sql》標準所鼓
勵的,join 確實更容易地表達表之間的關系,有興趣的讀者可自行改寫。如果使用
"*="實現兩表以上的外連接時,要注意此時 where 子句的 and 條件是有順序的,盡
管《ansi/iso sql》標準不允許 where 條件的順序影響查詢結果,但是 from 子句
的各表連接的順序可以影響查詢結果。
2.列印各科成績最高和最低的相關記錄: (就是各門課程的最高、最低分的學生和老師)
課程id,課程名稱, 最高分,學生id,學生姓名,教師id,教師姓名, 最低分,學生id,學生姓名,教師id,教師姓名
如果這道題要是僅僅求出各科成績最高分或最低分,則是一道非常簡單的題了:
select l.課程id, max(l.課程名稱), max(l.成績) as 最高分, min(l.成績) as 最低分
from 成績表 l
group by l.課程id
但是,刁鉆的題目卻是要列出各科最高和最低成績的相關記錄,這也往往才是真正需求。
既然已經選出各科最高和最低分,那么,剩下的就是把學生和教師的信息并入這個結果
集。如果照這樣寫下去,非常麻煩,因為要添加的字段太多了,很快就使代碼變得難于
管理。還是換個思路吧:
select l.課程id,l.課程名稱,l.[成績] as 最高分,l.[學生id],l.[學生姓名],l.[教師id],l.[教師姓名]
,r.[成績] as 最低分,r.[學生id],r.[學生姓名],r.[教師id],r.[教師姓名]
from 成績表 l
join 成績表 as r on l.[課程id] = r.[課程id]
where l.[成績] = (select max(il.[成績])
from 成績表 as [il]
where l.[課程id] = il.[課程id]
group by il.[課程id]
)
and
r.[成績] = (select min(ir.[成績])
from 成績表 as [ir]
where r.[課程id] = ir.[課程id]
group by ir.[課程id]
)
乍一看答案,好像很復雜,其實如果掌握了構造交叉透視表的基本方法和相關子查詢的
知識,問題迎刃而解。由于最低和最高分都是針對課程信息的,該答案巧妙地把課程信
息合并到了最高分的數據集中,當然也可以合并到最低分中。代碼中規中矩,風格很好,
可讀性也是不錯的。
3.按平均成績從高到低順序,列印所有學生的四門(數學,語文,英語,政治)課程成績: (就是每個學生的四門課程的成績單)
學生id,學生姓名,數學,語文,英語,政治,有效課程數,有效平均分
(注: 有效課程即在 t 表中有該學生的成績記錄,如不明白可不列印"有效課程數"和"有效平均分")
需要說明的是: 題目之所以明確提出"四門(數學,語文,英語,政治)課程"是有道理的,
因為實現時,的確無法避免使原基本表中的行上的數據的值影響列,這又是一個典型的
"行變列"的相關子查詢:
select 學生id,max(學生姓名) as 學生姓名,
(select 成績 from 成績表 where 學生id=t.學生id and 課程id='k1') as 數學 ,
(select 成績 from 成績表 where 學生id=t.學生id and 課程id='k2') as 語文 ,
(select 成績 from 成績表 where 學生id=t.學生id and 課程id='k3') as 英語 ,
(select 成績 from 成績表 where 學生id=t.學生id and 課程id='k4') as 政治 ,
count(*) as 有效課程數, avg(t.成績) as 平均成績
from 成績表 as t
group by 學生id
order by 平均成績
這可以說也是一個很規矩的解法,在這種應用場合,子查詢要比聯接代碼可讀性強得多。
如果數據庫引擎認為把它解析成聯接更好,那就由它去吧,其實本來相關子查詢也肯定含有連接。
這里再補充一下,在實際應用中如果再加一張表 ranks(rank,minvalue,maxvalue):
┌─────┬─────┬─────┐
│ rank │ minvalue │ maxvalue │
├─────┼─────┼─────┤
│ a │ 90 │ 100 │
├─────┼─────┼─────┤
│ b │ 80 │ 89 │
├─────┼─────┼─────┤
│ c │ 70 │ 79 │
├─────┼─────┼─────┤
│ d │ 60 │ 69 │
├─────┼─────┼─────┤
│ e │ 0 │ 59 │
└─────┴─────┴─────┘
就可以實現一個非常有實用價值的應用:
select 學生id,max(學生姓名) as 學生姓名
,(select 成績 from 成績表 t where 學生id=t0.學生id and 課程id='k1') as 數學
,(select max(rank) from ranks ,成績表 t
where t.成績 >= ranks.minvalue
and t.成績 <= ranks.maxvalue
and t.學生id=t0.學生id and t.課程id='k1'
) as 數學級別
,(select 成績 from 成績表 t where 學生id=t0.學生id and 課程id='k2') as 語文
,(select min(rank)
from ranks ,成績表 t
where t.成績 >= ranks.minvalue
and t.成績 <= ranks.maxvalue
and t.學生id=t0.學生id and t.課程id='k2'
) as 語文級別
,(select 成績 from 成績表 t where 學生id=t0.學生id and 課程id='k3') as 英語
,(select max(rank)
from ranks ,成績表 t
where t.成績 >= ranks.minvalue
and t.成績 <= ranks.maxvalue
and t.學生id=t0.學生id and t.課程id='k3'
) as 英語級別
,(select 成績 from 成績表 t where 學生id=t0.學生id and 課程id='k4') as 政治
,(select min(rank)
from ranks ,成績表 t
where t.成績 >= ranks.minvalue
and t.成績 <= ranks.maxvalue
and t.學生id=t0.學生id and t.課程id='k4'
) as 政治級別
,count(*),avg(t0.成績)
,(select max(rank)
from ranks
where avg(t0.成績) >= ranks.minvalue
and avg(t0.成績) <= ranks.maxvalue
) as 平均級別
from 成績表 t0
group by 學生id
這里表面上使用了不等連接,再仔細想想,ranks 表中每條記錄的區間是沒有交集的,
其實也可以認為是等值連接,這樣的表設計無疑存在著良好的擴展性,如果題目只要求
列印(學生id,學生姓名,有效課程數,有效平均分,平均分級別):
select 學生id,max(學生姓名) as 學生姓名,count(*),avg(t0.成績)
,(select max(rank)
from ranks
where avg(t0.成績) >= ranks.minvalue
and avg(t0.成績) <= ranks.maxvalue
) as 平均級別
from t as t0
group by 學生id
則這樣的解決方案就比較全面了。
回到原題,再介紹一個比較取巧的辦法,僅需一個簡單分組查詢就可解決問題,有經驗的讀者可能已經想到了
,那就是 case:
select 學生id, min(學生姓名),
sum(case 課程id when 'k1' then 成績 else 0 end) as 數學,
sum(case 課程id when 'k2' then 成績 else 0 end) as 語文,
sum(case 課程id when 'k3' then 成績 else 0 end) as 英語,
sum(case 課程id when 'k4' then 成績 else 0 end) as 政治,
count(*) as 有效課程數, avg(t.成績) as 平均成績
from 成績表 as t
group by 學生id
order by 平均成績 desc
雖然可能初看答案感覺有點怪,其實很好理解,可讀性并不低,效率也很高。但它不能
像前一個答案那樣,在成績中區分出某一門課這個學生究竟是缺考 (null),還是真得
零分。這個解法充分利用了 case 語句進行數據分類的作用: case 將成績按課程分
成四類,sum 用來消去多余的 0。
select [t].[學生id],max([t].[學生姓名]) as 學生姓名,
max([t1].[成績]) as 數學,
max([t2].[成績]) as 語文,
max([t3].[成績]) as 英語,
max([t4].[成績]) as 政治,
count([t].[課程id]) as 有效課程數,
(isnull(max([t1].[成績]),0) +
isnull(max([t2].[成績]),0) +
isnull(max([t3].[成績]),0) +
isnull(max([t4].[成績]),0)) / count([t].[課程id]) as 有效平均分
from 成績表 t
left join 成績表 as [t1] on [t].[學生id] = [t1].[學生id] and [t1].[課程id] = 'k1'
left join 成績表 as [t2] on [t].[學生id] = [t2].[學生id] and [t2].[課程id] = 'k2'
left join 成績表 as [t3] on [t].[學生id] = [t3].[學生id] and [t3].[課程id] = 'k3'
left join 成績表 as [t4] on [t].[學生id] = [t4].[學生id] and [t4].[課程id] = 'k4'
group by [t].[學生id]
order by 有效平均分 desc
這個方法是相當正統的聯接解法,盡管寫起來麻煩了些,但還是不難理解的。再從實用
角度考慮一下,真實需求往往不是象本題明確提出"列印四門 (數學,語文,英語,政治)
課程"這樣的相對靜態的需求,該是動態 sql 大顯身手的時候了,很明顯方法一的寫法
無疑是利用程序構造動態 sql 的最好選擇,當然另兩個 sql 規律還是挺明顯的,同樣
不難構造。以 case 版答案為例: 先用一個游標遍歷,取出所有課程湊成:
sum(case '課程id' when '課程名稱' then 成績 else 0 end) as 課程名稱 形式,
再補上 select 和 from、where 等必要條件,一個生成動態成績單的 sql 就誕生了,
只要再由相關程序調用執行即可,這樣就可以算一個更完善的解決方案了。
其實,最類似的典型應用是在主、細關系中的主表投影中實現細表的匯總統計行,
例如兩張表:
master(f,f1,f2 ...) 一對多 details(f,f3,f4 ...)
select *
,( select count(*)
from details
where master.f = details.f
)
,( select sum(f3)
from details
where master.f = details.f
)
from master
4.按各科不平均成績從低到高和及格率的百分數從高到低順序,統計并列印各科平均成績和不及格率的百分數(用"n行"表示):
(就是分析哪門課程難)
課程id,課程名稱,平均成績,及格百分比
select 課程id,max(課程名稱) as 課程名稱,avg(成績) as 平均成績
,str(100 * sum(case when 成績 >=60 then 1 else 0 end)/count(*))+'%' as 及格百分比
from 成績表 t
group by 課程id
order by 及格百分比 desc
這道題應該說是算簡單的了,就是用"行"來提供表現形式的。只要想明白要對數據如
何分組,取統計聚集函數,就萬事大吉了。
5.列印四門課程平均成績和及格率的百分數(用"1行4列"表示): (就是分析哪門課程難)
數學平均分,數學及格百分數,語文平均分,語文及格百分數,英語平均分,英語及格百分數,政治平均分,政治及格百分數
這道題其實就是上一題的"列"表現形式版本,相對于上一題,本題是靜態的,因為本題
同第三題一樣利用行上的數據構造了列,要實現擴展必須再利用另外的程序構造動態
sql:
select sum(case when 課程id = 'k1' then 成績 else 0 end)/sum(case 課程id when 'k1' then 1 else 0 end) as 數學平均分
,100 * sum(case when 課程id = 'k1' and 成績 >= 60 then 1 else 0 end)/sum(case when 課程id = 'k1' then 1 else 0 end) as 數學及格百分數
,sum(case when 課程id = 'k2' then 成績 else 0 end)/sum(case 課程id when 'k2' then 1 else 0 end) as 語文平均分
,100 * sum(case when 課程id = 'k2' and 成績 >= 60 then 1 else 0 end)/sum(case when 課程id = 'k2' then 1 else 0 end) as 語文及格百分數
,sum(case when 課程id = 'k3' then 成績 else 0 end)/sum(case 課程id when 'k3' then 1 else 0 end) as 英語平均分
,100 * sum(case when 課程id = 'k3' and 成績 >= 60 then 1 else 0 end)/sum(case when 課程id = 'k3' then 1 else 0 end) as 英語及格百分數
,sum(case when 課程id = 'k4' then 成績 else 0 end)/sum(case 課程id when 'k4' then 1 else 0 end) as 政治平均分
,100 * sum(case when 課程id = 'k4' and 成績 >= 60 then 1 else 0 end)/sum(case when 課程id = 'k4' then 1 else 0 end) as 政治及格百分數
from 成績表 t
這一句看起來很長,但實際上是最經典的 case 運用,很實用的數據分析技術。先將原
表中的成績一列連續投影 8 次備用于四門不同課程,充分利用 case 和數據的值域
['k1','k2','k3','k4']來劃分數據,再利用 sum() [1 + ...+ 1] 實現了看似本來應
該用 count(*) 的計數器的功能,這里面不要說聯接和子查詢,甚至連 group by 分組
的痕跡都找不到!如果讀起來吃力,完全可以先只保留一個字段,相對好理解些,看懂后
逐一補全。本題也可以算一個"行變列"的交叉透視表示例吧! 另外,"行"相對于"列"
是動態的,"行"是相對無限的,"列"是相對有限的,"行"的增刪是應用級的,可"隨意"增
刪,"列"的增刪是管理級的,不要輕易變動!
6.按不同老師所教不同課程平均分從高到低列印: (就是分析哪個老師的哪個課程水平高)
教師id,教師姓名,課程id,課程名稱,平均分
select 教師id,max(教師姓名) as 教師姓名,課程id,max(課程名稱) as 課程名稱,avg(成績) as 平均成績
from 成績表 t
group by 課程id,教師id
order by avg(成績) desc
這道題的確沒啥好說的,就算閉著眼,不動手,答案也應脫口而出!
如果平均分按去掉一個最高分和一個最低分后取得,則也不難寫出:
select 教師id,max(教師姓名),課程id,max(課程名稱) as 課程名稱 --,avg(成績) as 平均成績
,(sum(成績)
-(select max(成績)
from 成績表
where 課程id= t1.課程id and 教師id = t1.教師id)
-(select min(成績)
from 成績表
where 課程id= t1.課程id and 教師id = t1.教師id))
/ cast((select count(*) -2
from 成績表
where 課程id= t1.課程id and 教師id = t1.教師id) as float) as 平均分
from 成績表 as t1
where (select count(*) -2
from 成績表
where 課程id = t1.課程id and 教師id = t1.教師id) >0
group by 課程id,教師id
order by 平均分 desc
********************************************************************************************
7.列印數學成績第 10 名到第 15 名的學生成績單
或列印平均成績第 10 名到第 15 名的學生成績單
[學生id],[學生姓名],數學,語文,英語,政治,平均成績
如果只考慮一門課程,如:數學成績,非常簡單:
select top 5 *
from t
where 課程id ='k1'
and 成績 not in(select top 15 成績
from t
order by 成績 desc
)
order by 成績 desc
union
select *
from t
where 課程id ='k1'
and 成績 not in(select top 10 成績
from t
order by 成績 desc
)
and 成績 in(select top 15 成績
from t
order by 成績 desc
)
order by 成績 desc
從邏輯上說,第 10 名到第 15 名就是從原前 15 名,"再"挑出前 5 名不要,保留剩下
的 5 名。第二種寫法是從前 15 名里挑出不屬于原前 10 名的記錄,把兩個數據集做
一個差,因此要多用一個
子查詢,效率相對較低,它,如果要有《ansi/iso sql》的 except
關鍵字就是最理想的了。
這種技巧在數據"分頁"的應用中經常利用,只要遵循如下原則即可:
select top @pagesize *
from t
where sortfield not in (select top @pagesize * @pagei sortfield
from t
order by sortfield
)
order by sortfield
至此,該題考察的主要目的已經達到。至于列印明晰成績單:
[學生id],[學生姓名],數學,語文,英語,政治,平均成績 前面也有類似的題目,做起來
確實麻煩,因此下面僅提供參考答案,就不贅述了:
select distinct top 5
[成績表].[學生id],
[成績表].[學生姓名] as 學生姓名,
[t1].[成績] as 數學,
[t2].[成績] as 語文,
[t3].[成績] as 英語,
[t4].[成績] as 政治,
isnull([t1].[成績],0) + isnull([t2].[成績],0) + isnull([t3].[成績],0) + isnull([t4].[成績],0) as 總分
from [成績表]
left join [成績表] as [t1]
on [成績表].[學生id] = [t1].[學生id] and [t1].[課程id] = 'k1'
left join [成績表] as [t2]
on [成績表].[學生id] = [t2].[學生id] and [t2].[課程id] = 'k2'
left join [成績表] as [t3]
on [成績表].[學生id] = [t3].[學生id] and [t3].[課程id] = 'k3'
left join [成績表] as [t4]
on [成績表].[學生id] = [t4].[學生id] and [t4].[課程id] = 'k4'
where isnull([t1].[成績],0) + isnull([t2].[成績],0) + isnull([t3].[成績],0) + isnull([t4].[成績],0)
not in
(select
distinct
top 15 with ties
isnull([t1].[成績],0) + isnull([t2].[成績],0) + isnull([t3].[成績],0) + isnull([t4].[成績],0)
from [成績表]
left join [成績表] as [t1]
on [成績表].[學生id] = [t1].[學生id] and [t1].[課程id] = 'k1'
left join [成績表] as [t2]
on [成績表].[學生id] = [t2].[學生id] and [t2].[課程id] = 'k2'
left join [成績表] as [t3]
on [成績表].[學生id] = [t3].[學生id] and [t3].[課程id] = 'k3'
left join [成績表] as [t4]
on [成績表].[學生id] = [t4].[學生id] and [t4].[課程id] = 'k4'
order by isnull([t1].[成績],0) + isnull([t2].[成績],0) + isnull([t3].[成績],0) + isnull([t4].[成績],0) desc)
最后還要多說一句: 一般 top 關鍵字與 order by 子句合用才有真正意義。
