一道褒貶不一的 SQL 考試題 (轉)
《一道褒貶不一的 考試題》
playyuer 命題,playyuer、ccat 撰稿
相信有不少網友最近在網上見過一則名為《一道褒貶不一的 SQL 考試題》的帖子,這份
試題應當說還是有很多值得思考的地方。有興趣的讀者可以在閱讀本文的答案分析部分
之前,嘗試著作一下,如果你可以順利地完成,至少說明你是一個有的 SQL 使用者。
下面我們先看一下題設:
二維表 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 在右邊沒有比它更小的資料可以與之匹配,
因此被過濾了。如果資料大量重複,會差強人意,幸虧不是 ,而是 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 │ 89 │ 80 │
├──────────┼──────────┼──────────┤
│ C │ 79 │ 70 │
├──────────┼──────────┼──────────┤
│ D │ 69 │ 60 │
├──────────┼──────────┼──────────┤
│ E │ 60 │ 0 │
└──────────┴──────────┴──────────┘
就可以實現一個非常有實用價值的應用:
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 T as 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 平均成績
,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
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 子句合用才有真正意義。
8.統計列印各科成績,各分數段人數:
課程ID,課程名稱,[100-85],[85-70],[70-60],[<60]
儘管表面看上去不那麼容易,其實用 CASE 可以很容易地實現:
SELECT 課程ID, 課程名稱
,SUM(CASE WHEN 成績 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN 成績 BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN 成績 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN 成績 < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM 成績表
GROUP BY 課程ID, 課程名稱
注意這裡的 BETWEEN,雖然欄位名都是從高至低,可 BETWEEN 中還是要從低到高,這裡
如果不小心,會犯一個很難發現的邏輯錯誤: 在數學上,當a > b 時,[a, b]是一個空集。
9.列印學生平均成績及其名次
select count(distinct b.f) as 名次,a.學生ID,max(a.學生姓名),max(a.f)
from (select distinct t.學生ID,t.學生姓名,(select avg(成績)
from t t1
where t1.學生id = t.學生id) as F
from T
) as a,
(select distinct t.學生ID,t.學生姓名,(select avg(成績)
from t t1
where t1.學生id = t.學生id) as F
from T
) as b
where a.f <= b.f
group by a.學生ID
order by count(b.f)
這裡有很多值得一提的地方,先利用兩個完全相同的自相關子查詢生成兩個派生表作
為基本表用於作小於或等於的連線,這樣就可以透過表中小於或等於每個值的其他值
的 COUNT(distinct) 的計數聚集函式來體現名次了。
SELECT 1+(SELECT COUNT(distinct [平均成績])
FROM (SELECT [學生ID],MAX([學生姓名]) AS 學生姓名 ,AVG([成績]) AS [平均成績]
FROM T
GROUP BY [學生ID]
) AS T1
WHERE [平均成績] > T2.[平均成績]) as 名次,
[學生ID],[學生姓名],[平均成績]
FROM (SELECT [學生ID],max([學生姓名]) AS 學生姓名,AVG([成績]) AS [平均成績]
FROM T
GROUP BY [學生ID]
) AS T2
ORDER BY T2.[平均成績] desc
方法二也使用了兩個完全相同的自相關子查詢生成兩個派生表作為基本表,再利用它
們之間作大於的相關子查詢取 COUNT(distinct) + 1 的計數聚集函式同樣實現了名
次的顯示。
這道題從應用角度來看,查詢結果是相當合理的,並列情況的名次也都一樣。但如果想
實現類似自動序列的行號,該解決方案的侷限性突顯,不能處理並列相等的情況了,所
以有必要強調:一定要選擇不重複的連線條件,可以根據實際情況利用欄位組合的不等
連線 (T1.f1 + ... + T1.fn <= T2.f1 + ... + T2.fn)。繼續引申還可以透過判斷
COUNT(distinct) % 2 是否為 0 的 HAVING 或 WHERE 子句實現只顯示偶數或奇數行:
HAVING count(distinct b.f) % 2 = 1
或:
WHERE 1+(SELECT COUNT(distinct [平均成績])
FROM (SELECT [學生ID],MAX([學生姓名]) AS 學生姓名 ,AVG([成績]) AS [平均成績]
FROM T
GROUP BY [學生ID]
) AS T1
WHERE [平均成績] > T2.[平均成績]) % 2 =1
再簡單說一下 HAVING 和 WHERE 在含有 GROUP BY 分組的查詢中的區別,HAVING 是
在資料分組後才篩選記錄的,WHERE 是先進行篩選在分組的,而且 HAVING 一般應與聚
集函式合用才有真正含義。
兩種方法再次體現了子查詢與連線可以殊途同歸之妙,第二種子查詢方法值得推薦,因
為比較利於程式構造,便於為沒有該功能的原有查詢新增此項功能。本題僅僅是為了示
範一種比較新穎的解題思路,迴避了效率的問題。
10.列印各科成績前三名的記錄:(不考慮成績並列情況)
學生ID,學生姓名,課程ID,課程名稱,成績,教師ID,教師姓名
如果僅從成績考慮前三名的人,利用相關子查詢的知識:
SELECT *
FROM 成績表 t1
WHERE 成績 IN (SELECT TOP 3 成績
FROM 成績表
WHERE t1.課程id = 課程id
ORDER BY 成績 DESC
)
ORDER BY t1.課程id
這樣查詢的結果各科成績前三名的記錄數應該大於等於三,因為可能有並列情況,
如果小於三自然是該門課還沒有那麼多人考試!
如果不考慮並列情況,嚴格控制各科只列印三條記錄,則使用"學生id"構造相關
子查詢條件亦可:
SELECT *
FROM 成績表 t1
WHERE 學生id IN (SELECT TOP 2 學生id
FROM 成績表
WHERE t1.課程id = 課程id
ORDER BY 成績 DESC
)
ORDER BY t1.課程id
如果利用第 10 題的思路也可實現該應用。
11.規範化
規範化的問題可以說是仁者見仁,智者見智。而且不做肯定不好,但過猶不及,搞到太
規範也不一定是好事。首先分析資訊的對應關係,這個表中有四種資訊。學生、課程、教師、成績。其中前三個可以獨立存在,最
後一個可以看做是基於前三個存在的。然後,我們按這四種分類,建立四個表:
關於學生的資訊,有以下兩個:學生ID,姓名;
教師則會有教師ID,姓名,課程ID 這也就是為什麼我要把學生和教師會為兩個表的原因;
課程則有課程ID,課程名稱兩種;
而最後一個成績資訊,就成為了聯接它們的一個部分,在這裡,它要有學生ID,教師ID,課程ID,成績四項,相
對與其它表應屬應用級別,除了成績欄位,其它都引用的另外的表。
這樣一來,幾個表的指令碼大概是這個樣子:
CREATE TABLE "學生資訊"
(
"ID" CHAR(4),
"姓名" CHAR(16),
PRIMARY KEY ("ID")
)
CREATE TABLE "課程資訊"
(
"ID" CHAR(4),
"名稱" CHAR(16),
PRIMARY KEY ("ID"),
)
CREATE TABLE "教師資訊"
(
"ID" CHAR(4),
"姓名" CHAR(16),
"課程ID" CHAR(4),
PRIMARY KEY ("ID"),
FOREIGN KEY("課程ID") REFERENCES "課程資訊"("ID")
)
CREATE TABLE "成績資訊"
(
"學生ID" CHAR(4),
"教師ID" CHAR(4),
"課程ID" CHAR(4),
成績 NUMERIC(5, 2),
PRIMARY KEY("學生ID", "教師ID", "課程ID"),
FOREIGN KEY("學生ID") REFERENCES "學生資訊"("ID"),
FOREIGN KEY("教師ID") REFERENCES "教師資訊"("ID"),
FOREIGN KEY("課程ID") REFERENCES "課程資訊"("ID")
)
這樣建表很明顯是為了儘可能的細化資訊的分類。它的好處在於各種資訊分劃明確,不
過問題也很明顯,比如,一個教師不能同時帶兩門不同的課(當然,這可能正是業務規則所
要求的),而且,這樣做分類過於細膩了。
如果不需要對教師進行人事管理,那麼,完全可以把教師資訊和課程資訊合為一表。也就是說,不同教師帶的同
一名稱課程,視做不同課程。這樣做當然也有其應用背景,很多教師,特別是高等教育和名師,往往有他們自
己的風格,完全可以視做兩種課程,相信同樣教授 C++ , Lippman 和 Stroustrup 教出的學生總會有所不同。
要說問題,那就是,如果想要限制學生不能重複修某一門課,就得用了,沒有太好的辦法,不過這個問題,
前面的第一種設計同樣解決不了,就算針對教師和課程的關係單建一個表也不一定就可以,還把問題複雜化了。
現在把第二種設計的指令碼列出來:
CREATE TABLE "學生資訊"
(
"ID" CHAR(4),
"姓名" CHAR(16),
PRIMARY KEY ("ID")
)
CREATE TABLE "課程資訊"
(
"ID" CHAR(4),
"課程分類" CHAR(4),
"名稱 "CHAR(16),
"教師ID" CHAR(4),
"教師姓名" CHAR(16),
PRIMARY KEY ("ID")
)
CREATE TABLE "成績資訊"
(
"學生ID" CHAR(4),
"課程ID" CHAR(4),
成績 NUMERIC(5, 2),
PRIMARY KEY("學生ID", "課程ID"),
FOREIGN KEY("學生ID") REFERENCES "學生資訊"("ID"),
FOREIGN KEY("課程ID") REFERENCES "課程資訊"("ID")-
)
這樣是不是能清爽一點?這樣一來,如果不存在一個教師教不同的課程的情況,並且我
們希望簡化管理,甚至都可以不用"課程分類"和"教師ID"欄位。當然,視業務需要而定,
如果希望在限制學生學習的課程分類的同時,不想帶來額外的開銷,使用第一種設
計,或將課程分類欄位也列入成績資訊表,是一個更好的辦法。
關於資料庫的設計和管理,有幾條經驗,拿出來在這裡和大家交流一下:
對資料進行規範化時,最好要符合它的應用背景。這樣易於理解和管理;
資料的規範化不一定是越細化越好,粒度適當地大一點,後面的一般會容易一點;
雖說不是越細越好,不過要是不做規範化,卻幾乎是一定要出問題;
很重要的一點: 千萬不要濫用自動標識列! 特別是,不要濫用自動標識列來做為一個表中唯一的約束條件,通常,
那和沒有約束沒什麼不同!
關於這些試題,我們的看法就到這裡,希望朋友們可以拿出更多更好的意見,我們一起討論。
原題含答案:
CREATE TABLE [T] (
[ID] [int] NTITY (1, 1) NOT NULL,
[學生ID] [varchar] (50) NULL,
[學生姓名] [varchar] (50) NULL,
[課程ID] [varchar] (50) NULL,
[課程名稱] [varchar] (50) NULL,
[成績] [real] NULL,
[教師ID] [varchar] (50) NULL ,
[教師姓名] [varchar] (50) NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('S3','王五','K2','語文',81,'T2','王老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('S3','王五','K4','政治',53,'T4','趙老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('S4','趙六','K1','數學',99,'T1','張老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('S4','趙六','K2','語文',33,'T2','王老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('S4','趙六','K4','政治',59,'T4','趙老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s1','張三','K4','政治',79,'T4','趙老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s1','張三','K1','數學',98,'T1','張老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s1','張三','K3','英語',69,'T3','李老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s7','peter','K1','數學',64,'T1','張老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s7','peter','K2','語文',81,'T2','王老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s7','peter','K4','政治',53,'T4','趙老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s2','mike','K1','數學',64,'T1','張老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s2','mike','K2','語文',81,'T2','王老師')
INSERT INTO T ([學生ID],[學生姓名],[課程ID],[課程名稱],[成績],[教師ID],[教師姓名])
valueS ('s2','mike','K4','政治',53,'T4','趙老師')
二維表 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(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 │ 張老師 │
│ .... │ │ │ │ │ │ │
│ .... │ │ │ │ │ │ │
------------------------------------------------------------------------------
1.規範化
請以一句 T-SQL (Ms ) 或 Jet SQL (Ms Access) 作答!
2.如果 T 表還有一欄位 F0 資料型別為自動增量整型(唯一,不會重複),
而且 T 表中含有除 F0 欄位外,請刪除其它欄位完全相同的重複多餘的髒記錄資料(要保留其中的一條):
Delete T
from T, T AS T1
where T.學生ID=T1.學生ID and T.課程ID=T.課程ID and T.F0 < T1.F0
DELETE
FROM T
WHERE [F0] NOT IN (SELECT MAX([F0])
FROM [T]
GROUP BY T.F1,T.F2,T.F3
HAVING COUNT(*)>1
)
AND F0 NOT IN (SELECT MAX([F0])
FROM [T]
GROUP BY T.F1,T.F2,T.F3
HAVING COUNT(*)=1
)
DELETE
FROM T
WHERE [F0] < (SELECT MAX([F0])
FROM [T] AS T1
WHERE T1.F1=T.F1
AND T1.F2=T.F2
AND T1.F3=T.F3
GROUP BY T1.F1,T1.F2,T1.F3
)
3.列印各科成績最高和最低的記錄: (就是各門課程的最高、最低分的學生和老師)
課程ID,課程名稱,最高分,學生ID,學生姓名,教師ID,教師姓名,最低分,學生ID,學生姓名,教師ID,教師姓名
SELECT T.課程ID,T.課程名稱,T.[成績] AS 最高分,T.[學生ID],T.[學生姓名],T.[教師ID],T.[教師姓名]
,T1.[成績] AS 最低分,T1.[學生ID],T1.[學生姓名],T1.[教師ID],T1.[教師姓名]
FROM T
LEFT JOIN T AS T1 ON T.[課程ID] = T1.[課程ID]
WHERE T.[成績] = (SELECT MAX(T2.[成績])
FROM T AS [T2]
WHERE T.[課程ID] = T2.[課程ID]
GROUP BY T2.[課程ID])
AND T1.[成績] = (SELECT MIN(T3.[成績])
FROM T AS [T3]
WHERE T1.[課程ID] = T3.[課程ID]
GROUP BY T3.[課程ID])
4.按成績從高到低順序,列印所有學生四門(數學,語文,英語,政治)課程成績: (就是每個學生的四門課程的成績單)
學生ID,學生姓名,數學,語文,英語,政治,有效課程數,有效平均分
(注: 有效課程即在 T 表中有該學生的成績記錄,如不明白可不列印"有效課程數"和"有效平均分")
select 學生ID,MAX(學生姓名) as 學生姓名
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K1') as 數學
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K2') as 語文
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K3') as 英語
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K4') as 政治
,count(*),avg(t0.成績)
from T as T0
group by 學生ID
select 學生ID,MAX(學生姓名) as 學生姓名
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K1') as 數學
,(SELECT max(class)
from classes ,t
where t.成績 >= Classes.MinV
and t.成績 <= Classes.MaxV
and t.學生ID=T0.學生ID and t.課程ID='K1'
) as 數學級別
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K2') as 語文
,(SELECT min(class)
from classes ,t
where t.成績 >= Classes.MinV
and t.成績 <= Classes.MaxV
and t.學生ID=T0.學生ID and t.課程ID='K2'
) as 語文級別
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K3') as 英語
,(SELECT max(class)
from classes ,t
where t.成績 >= Classes.MinV
and t.成績 <= Classes.MaxV
and t.學生ID=T0.學生ID and t.課程ID='K3'
) as 英語級別
,(select 成績 from T where 學生ID=T0.學生ID and 課程ID='K4') as 政治
,(SELECT min(class)
from classes ,t
where t.成績 >= Classes.MinV
and t.成績 <= Classes.MaxV
and t.學生ID=T0.學生ID and t.課程ID='K4'
) as 政治級別
,count(*),avg(t0.成績)
,(SELECT max(class)
from classes
where AVG(T0.成績) >= Classes.MinV
and AVG(T0.成績) <= Classes.MaxV
) AS 平均級別
from T as T0
group by 學生ID
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 [T] AS [T1]
ON [T].[學生ID] = [T1].[學生ID] AND [T1].[課程ID] = 'k1'
LEFT JOIN [T] AS [T2]
ON [T].[學生ID] = [T2].[學生ID] AND [T2].[課程ID] = 'k2'
LEFT JOIN [T] AS [T3]
ON [T].[學生ID] = [T3].[學生ID] AND [T3].[課程ID] = 'k3'
LEFT JOIN [T] AS [T4]
ON [T].[學生ID] = [T4].[學生ID] AND [T4].[課程ID] = 'k4'
GROUP BY [T].[學生ID]
ORDER BY (ISNULL(MAX([T1].[成績]),0) + ISNULL(MAX([T2].[成績]),0) + ISNULL(MAX([T3].[成績]),0) + ISNULL(MAX([T4].[成績]),0)) / COUNT([T].[課程ID]) DESC
5.列印數學成績第 10 名到第 15 名的學生成績單
或列印平均成績第 10 名到第 15 名的學生成績單
[學生ID],[學生姓名],數學,語文,英語,政治,平均成績
SELECT DISTINCT
[T].[學生ID],
[T].[學生姓名] AS 學生姓名,
[T1].[成績] AS 數學,
[T2].[成績] AS 語文,
[T3].[成績] AS 英語,
[T4].[成績] AS 政治,
ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0) as 總分
FROM [T]
LEFT JOIN [T] AS [T1]
ON [T].[學生ID] = [T1].[學生ID] AND [T1].[課程ID] = 'k1'
LEFT JOIN [T] AS [T2]
ON [T].[學生ID] = [T2].[學生ID] AND [T2].[課程ID] = 'k2'
LEFT JOIN [T] AS [T3]
ON [T].[學生ID] = [T3].[學生ID] AND [T3].[課程ID] = 'k3'
LEFT JOIN [T] AS [T4]
ON [T].[學生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 3 WITH TIES
ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0)
FROM [T]
LEFT JOIN [T] AS [T1]
ON [T].[學生ID] = [T1].[學生ID] AND [T1].[課程ID] = 'k1'
LEFT JOIN [T] AS [T2]
ON [T].[學生ID] = [T2].[學生ID] AND [T2].[課程ID] = 'k2'
LEFT JOIN [T] AS [T3]
ON [T].[學生ID] = [T3].[學生ID] AND [T3].[課程ID] = 'k3'
LEFT JOIN [T] AS [T4]
ON [T].[學生ID] = [T4].[學生ID] AND [T4].[課程ID] = 'k4'
ORDER BY ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0) DESC)
AND ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0)
IN (SELECT
DISTINCT
TOP 4 WITH TIES
ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0)
FROM [T]
LEFT JOIN [T] AS [T1]
ON [T].[學生ID] = [T1].[學生ID] AND [T1].[課程ID] = 'k1'
LEFT JOIN [T] AS [T2]
ON [T].[學生ID] = [T2].[學生ID] AND [T2].[課程ID] = 'k2'
LEFT JOIN [T] AS [T3]
ON [T].[學生ID] = [T3].[學生ID] AND [T3].[課程ID] = 'k3'
LEFT JOIN [T] AS [T4]
ON [T].[學生ID] = [T4].[學生ID] AND [T4].[課程ID] = 'k4'
ORDER BY ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0) DESC)
ORDER BY ISNULL([T1].[成績],0) + ISNULL([T2].[成績],0) + ISNULL([T3].[成績],0) + ISNULL([T4].[成績],0) DESC
6.按各科不及格率的百分數從低到高和平均成績從高到低順序,統計並列印各科平均成績和不及格率的百分數(用"N行"表示): (就是分析哪門課程難)
課程ID,課程名稱,平均成績,及格百分數
SELECT 課程ID,MAX(課程名稱) AS 課程名稱,AVG(成績) AS 平均成績,100 * SUM(CASE WHEN 成績 >=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分數
FROM T
GROUP BY 課程ID
ORDER BY 及格百分數 DESC
7.列印四門課程平均成績和及格率的百分數(用"1行4列"表示): (就是分析哪門課程難)
數學平均分,數學及格百分數,語文平均分,語文及格百分數,英語平均分,英語及格百分數,政治平均分,政治及格百分數
SELECT SUM(CASE WHEN 課程ID = 'K1' THEN 成績 ELSE 0 END)/ (SELECT COUNT(*) FROM T WHERE 課程ID = 'K1') 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)/ (SELECT COUNT(*) FROM T WHERE 課程ID = 'K2') 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)/ (SELECT COUNT(*) FROM T WHERE 課程ID = 'K3') 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)/ (SELECT COUNT(*) FROM T WHERE 課程ID = 'K4') 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
8.按不同老師所教不同課程平均分從高到低列印: (就是分析哪個老師的哪個課程水平高)
教師ID,教師姓名,課程ID,課程名稱,平均分 (平均分按去掉一個最高分和一個最低分後取)
SELECT 教師ID,MAX(教師姓名),課程ID,MAX(課程名稱) AS 課程名稱,AVG(成績) AS 平均成績
FROM T
GROUP BY 課程ID,教師ID
ORDER BY AVG(成績)
平均分按去掉一個最高分和一個最低分後取得,則也不難寫出:
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
9.統計列印各科成績,各分數段人數:
課程ID,課程名稱,[100-85],[85-70],[70-60],[<60]
SELECT 課程ID, 課程名稱
,SUM(CASE WHEN 成績 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN 成績 BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN 成績 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN 成績 < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM T
GROUP BY 課程ID, 課程名稱
11.列印學生平均成績及其名次
select count(distinct b.f),a.學生ID,max(a.學生姓名),max(a.f)
from (select distinct t.學生ID,t.學生姓名,(select avg(成績)
from t t1
where t1.學生id = t.學生id) as F
from T
) as a,
(select distinct t.學生ID,t.學生姓名,(select avg(成績)
from t t1
where t1.學生id = t.學生id) as F
from T
) as b
where a.f <= b.f
group by a.學生ID
order by count(b.f)
SELECT 1+(SELECT COUNT(distinct [平均成績])
FROM (SELECT [學生ID],MAX([學生姓名]) AS 學生姓名 ,AVG([成績]) AS [平均成績]
FROM T
GROUP BY [學生ID]
) AS T1
WHERE [平均成績] > T2.[平均成績]) ,
[學生ID],[學生姓名],[平均成績]
FROM
(
SELECT [學生ID],max([學生姓名]) AS 學生姓名,AVG([成績]) AS [平均成績]
FROM T
GROUP BY [學生ID]
) AS T2
ORDER BY T2.[平均成績] desc
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10752019/viewspace-977230/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 為什麼開發人員對於PHP語言褒貶不一PHP
- MySQL CEO褒貶甲骨文 稱要努力打造自家產品(轉)MySql
- 從全球熱銷第一到口碑褒貶不一，《天神鎮》究竟是一款怎樣的遊戲？遊戲
- 一道JAVA 考試題 請高手指點Java
- 一道SQL題SQL
- 一道sql面試題的解答SQL面試題
- 女生轉行IT和男生要考慮的問題有哪些不一樣？
- 女生轉行IT和男生要考慮的問題有什麼不一樣？
- 一道sql面試題的求解方法SQL面試題
- 用C#實現的一道公務員考題C#
- 走向世界的中國IT考試--軟體水平考試 (轉)
- 一道小學題的sql實現~~~SQL
- 面安服的一道筆試題筆試
- 一道TCL的筆試題---遞迴筆試遞迴
- 變數提升的考試題變數
- 從一道筆試題題說起筆試
- GRE計算機專項考試題（96） (轉)計算機
- GRE計算機專項考試題（98） (轉)計算機
- 一道面試題考驗了你對java的理解程度面試題Java
- 最考驗換位思考的一道演算法題演算法
- 價值 100 RMB 的一道 SQL 題SQL
- 又一道小學題的sql實現~~~SQL
- 關於一道前端筆試題的思考前端筆試
- 大學考試的LINUX試題, 全做對了，保證你面試沒問題(轉)Linux面試
- SQL語法參考(轉)SQL
- C語言的考試題型C語言
- 軟體工程考試題軟體工程
- DBA筆試試題-考試認證(zt)筆試
- OCP考試題庫-185題
- SQL面試題一道（偏實際業務）SQL面試題
- 阿里巴巴的Oracle DBA筆試題參考答案 - SQL tuning類阿里Oracle筆試SQL
- 考試系統案例之 -- 隨機生成考題隨機
- PMP 考試心得分享（轉）
- 一道關於block尺寸計算的筆試題BloC筆試
- SQL 筆試題SQL筆試
- oracle ocp考試程式的登入問題Oracle
- oracle ocp 19c考題，科目082考試題-temporary undoOracle
- oracle ocp 19c考題，科目082考試題-Oracle NetOracle