《一道褒貶不一的 sql 考試題》
playyuer 命題,playyuer、ccat 撰稿
相信有不少網(wǎng)友最近在網(wǎng)上見過一則名為《一道褒貶不一的 sql 考試題》的帖子,這份
試題應(yīng)當(dāng)說(shuō)還是有很多值得思考的地方。有興趣的讀者可以在閱讀本文的答案分析部分
之前,嘗試著作一下,如果你可以順利地完成,至少說(shuō)明你是一個(gè)有經(jīng)驗(yàn)的 sql 使用者。
下面我們先看一下題設(shè):
二維表 t(f1,f2,f3,f4,f5,f6,f7) 表示如下關(guān)系:
┌──────────┬──────────┬──────────┬──────────┬──────────┬──────────┬──────────┐
│ 學(xué)生id │ 學(xué)生姓名 │ 課程id │ 課程名稱 │ 成績(jī) │ 教師id │ 教師姓名 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k4 │ 政治 │ 53 │ t4 │ 趙老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k1 │ 數(shù)學(xué) │ 61 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k3 │ 英語(yǔ) │ 88 │ t3 │ 李老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k4 │ 政治 │ 77 │ t4 │ 趙老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k4 │ 政治 │ 67 │ t5 │ 周老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k2 │ 語(yǔ)文 │ 90 │ t2 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k1 │ 數(shù)學(xué) │ 55 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k2 │ 語(yǔ)文 │ 81 │ t2 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s4 │ 趙六 │ k2 │ 語(yǔ)文 │ 59 │ t1 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k3 │ 英語(yǔ) │ 37 │ t3 │ 李老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k1 │ 數(shù)學(xué) │ 81 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ .... │ │ │ │ │ │ │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ .... │ │ │ │ │ │ │
└──────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘
為便于大家更好的理解,我們將 t 表起名為"成績(jī)表"
1.如果 t 表還有一字段 f 數(shù)據(jù)類型為自動(dòng)增量整型(唯一,不會(huì)重復(fù)),
而且 t 表中含有除 f 字段外,請(qǐng)刪除其它字段完全相同的重復(fù)多余的臟記錄數(shù)據(jù):
本問題就是一個(gè)清理"邏輯重復(fù)"記錄的問題,當(dāng)然,這種情況完全可以利用主鍵約束來(lái)
杜絕!然而,現(xiàn)實(shí)情況經(jīng)常是原始數(shù)據(jù)在"洗滌"后,方可安全使用,而且邏輯主鍵過早的
約束,將會(huì)給采集原始數(shù)據(jù)帶來(lái)不便,例如:從刷卡機(jī)上讀取考勤記錄。到了應(yīng)用數(shù)據(jù)
的時(shí)候,臟數(shù)據(jù)就該被掃地出門了! 之所以題中要保留一個(gè)自動(dòng)標(biāo)識(shí)列,是因?yàn)樗拇_
是下面答案所必須的前提:
delete l
from "成績(jī)表" l
join "成績(jī)表" r
on l."學(xué)生id" = r."學(xué)生id" and l."課程id" = r."課程id" and l.f > r.f
這是思路最精巧且最直接有效的方法之一。用不等自聯(lián)接,正好可以將同一組重復(fù)數(shù)
據(jù)中 f 字段值最小的那一條留下,并選出其它的刪掉,如果只有一條,自然也不會(huì)被選
中了。這里還要強(qiáng)調(diào)一下,大家一定要分清楚被操作的基本表也就是 delete 關(guān)鍵字
后的表和過濾條件所使用的由基本表連接而成的二維表數(shù)據(jù)集,也就是 from 子句的
全部。在自連接的 from 子句至少要取一個(gè)別名來(lái)引用基本表。別名的使用在編寫大
量類似結(jié)構(gòu)的 sql 時(shí)非常方便,而且利于統(tǒng)一程序構(gòu)造動(dòng)態(tài) sql。如有必要加強(qiáng)條件,
還可繼續(xù)使用 where 子句。如果上面的例子還不夠直觀,下面模仿一個(gè)不等自聯(lián)接,
有一組數(shù) (1,2,3),作一個(gè)不等自聯(lián)接,令左子集大于右子集,是:
2 1
3 1
3 2
如果現(xiàn)在選出左子集,就是 2 和 3 了。1 在右邊沒有比它更小的數(shù)據(jù)可以與之匹配,
因此被過濾了。如果數(shù)據(jù)大量重復(fù),效率會(huì)差強(qiáng)人意,幸虧不是 select ,而是 delete
無(wú)需返回結(jié)果集,影響自然小多了。
delete t
from 成績(jī)表 t
where f not in (select min(f)
from 成績(jī)表 i
group by i.學(xué)生id,i.課程id
having count(*)>1
)
and f not in (select min(f)
from 成績(jī)表 i
group by i.學(xué)生id, i.課程id
having count(*)=1
)
這種方法思路很簡(jiǎn)單,就像翻譯自然語(yǔ)言,很精確地描述了符合條件記錄的特性,甚至
第二個(gè)條件的確多余。至少應(yīng)該用一個(gè) >= 號(hào)合并這兩個(gè)條件或只保留任意一個(gè)條件,
提高效率。
delete t
from 成績(jī)表 t
where f > (select min(f)
from 成績(jī)表 as i
where i.學(xué)生id = t.學(xué)生id
and i.課程id = t.課程id
group by i.學(xué)生id, i.課程id
)
這種方法,基本上是方法一的相關(guān)子查詢版本,了解笛卡爾積的讀者能會(huì)好理解些,而
且用到了統(tǒng)計(jì)函數(shù),因此效率不是太高。細(xì)心的讀者會(huì)發(fā)現(xiàn)子查詢里的 group by 子
句沒有必要,去掉它應(yīng)該會(huì)提高一些效率的。
關(guān)于 delete 語(yǔ)句的調(diào)試,有經(jīng)驗(yàn)的程序員都會(huì)先用無(wú)害的 select 暫時(shí)代替危險(xiǎn)的
delete。例如:
select l.*
--delect l 暫時(shí)注釋掉
from "成績(jī)表" l
join "成績(jī)表" r
on l."學(xué)生id" = r."學(xué)生id" and l."課程id" = r."課程id" and l.f>r.f
這樣,極大地減小了在線數(shù)據(jù)被無(wú)意破壞的可能性,當(dāng)然數(shù)據(jù)提前備份也很重要。同理
update 和 insert 寫操作也應(yīng)照此行事。從數(shù)據(jù)庫(kù)原理的關(guān)系運(yùn)算的角度來(lái)看 insert、
update 和 delete 這些寫操作都屬于典型的"選擇(selection)"運(yùn)算,update 和 insert
而且還是"投影(projection)"運(yùn)算,它們都是這些關(guān)系運(yùn)算的"寫"應(yīng)用的表現(xiàn)形式。
其實(shí),查詢的目的也本來(lái)無(wú)非就是瀏覽、刪除、更
新或插入。通常寫操作也比讀操作消耗更大,如果索引過多,只會(huì)降低效率。
選擇"子查詢"還是"連接"在效率是有差別的,但最關(guān)鍵的差別還是表現(xiàn)在查詢的結(jié)果
集的讀寫性上,開發(fā)人員在寫一個(gè)"只讀"應(yīng)用的查詢記錄集時(shí),"子查詢"和"連接"各自
的效率就是應(yīng)該首先考慮的問題,但是如果要實(shí)現(xiàn)"可寫"應(yīng)用的查詢結(jié)果集,則無(wú)論是
相關(guān)還是非相關(guān)子查詢都是在復(fù)雜應(yīng)用中難以避免的。
以上解決方案中,應(yīng)該說(shuō)第一種方法,簡(jiǎn)潔有效,很有創(chuàng)意,是值得推薦的方法。當(dāng)然,
最簡(jiǎn)單的寫法應(yīng)該是:
delete t
from t,t t1
where t.學(xué)生id=t1.學(xué)生id and t.課程id=t.課程id and t.f < t1.f
其實(shí)這就是方法一的"標(biāo)準(zhǔn)"(但確實(shí)實(shí)不是《ansi/iso sql》標(biāo)準(zhǔn))連接寫法,以下各
題答案為了便于讀者理解,一般不采用這種寫法,這也是《ansi/iso sql》標(biāo)準(zhǔn)所鼓
勵(lì)的,join 確實(shí)更容易地表達(dá)表之間的關(guān)系,有興趣的讀者可自行改寫。如果使用
"*="實(shí)現(xiàn)兩表以上的外連接時(shí),要注意此時(shí) where 子句的 and 條件是有順序的,盡
管《ansi/iso sql》標(biāo)準(zhǔn)不允許 where 條件的順序影響查詢結(jié)果,但是 from 子句
的各表連接的順序可以影響查詢結(jié)果。
2.列印各科成績(jī)最高和最低的相關(guān)記錄: (就是各門課程的最高、最低分的學(xué)生和老師)
課程id,課程名稱,最高分,學(xué)生id,學(xué)生姓名,教師id,教師姓名,最低分,學(xué)生id,學(xué)生姓名,教師id,教師姓名
如果這道題要是僅僅求出各科成績(jī)最高分或最低分,則是一道非常簡(jiǎn)單的題了:
select l.課程id, max(l.課程名稱), max(l.成績(jī)) as 最高分, min(l.成績(jī)) as 最低分
from 成績(jī)表 l
group by l.課程id
但是,刁鉆的題目卻是要列出各科最高和最低成績(jī)的相關(guān)記錄,這也往往才是真正需求。
既然已經(jīng)選出各科最高和最低分,那么,剩下的就是把學(xué)生和教師的信息并入這個(gè)結(jié)果
集。如果照這樣寫下去,非常麻煩,因?yàn)橐砑拥淖侄翁嗔?很快就使代碼變得難于
管理。還是換個(gè)思路吧:
select l.課程id,l.課程名稱,l.[成績(jī)] as 最高分,l.[學(xué)生id],l.[學(xué)生姓名],l.[教師id],l.[教師姓名]
,r.[成績(jī)] as 最低分,r.[學(xué)生id],r.[學(xué)生姓名],r.[教師id],r.[教師姓名]
from 成績(jī)表 l
join 成績(jī)表 as r on l.[課程id] = r.[課程id]
where l.[成績(jī)] = (select max(il.[成績(jī)])
from 成績(jī)表 as [il]
where l.[課程id] = il.[課程id]
group by il.[課程id]
)
and
r.[成績(jī)] = (select min(ir.[成績(jī)])
from 成績(jī)表 as [ir]
where r.[課程id] = ir.[課程id]
group by ir.[課程id]
)
乍一看答案,好像很復(fù)雜,其實(shí)如果掌握了構(gòu)造交叉透視表的基本方法和相關(guān)子查詢的
知識(shí),問題迎刃而解。由于最低和最高分都是針對(duì)課程信息的,該答案巧妙地把課程信
息合并到了最高分的數(shù)據(jù)集中,當(dāng)然也可以合并到最低分中。代碼中規(guī)中矩,風(fēng)格很好,
可讀性也是不錯(cuò)的。
3.按平均成績(jī)從高到低順序,列印所有學(xué)生的四門(數(shù)學(xué),語(yǔ)文,英語(yǔ),政治)課程成績(jī): (就是每個(gè)學(xué)生的四門課程的成績(jī)單)
學(xué)生id,學(xué)生姓名,數(shù)學(xué),語(yǔ)文,英語(yǔ),政治,有效課程數(shù),有效平均分
(注: 有效課程即在 t 表中有該學(xué)生的成績(jī)記錄,如不明白可不列印"有效課程數(shù)"和"有效平均分")
需要說(shuō)明的是: 題目之所以明確提出"四門(數(shù)學(xué),語(yǔ)文,英語(yǔ),政治)課程"是有道理的,
因?yàn)閷?shí)現(xiàn)時(shí),的確無(wú)法避免使原基本表中的行上的數(shù)據(jù)的值影響列,這又是一個(gè)典型的
"行變列"的相關(guān)子查詢:
select 學(xué)生id,max(學(xué)生姓名) as 學(xué)生姓名
,(select 成績(jī) from 成績(jī)表 where 學(xué)生id=t.學(xué)生id and 課程id='k1') as 數(shù)學(xué)
,(select 成績(jī) from 成績(jī)表 where 學(xué)生id=t.學(xué)生id and 課程id='k2') as 語(yǔ)文
,(select 成績(jī) from 成績(jī)表 where 學(xué)生id=t.學(xué)生id and 課程id='k3') as 英語(yǔ)
,(select 成績(jī) from 成績(jī)表 where 學(xué)生id=t.學(xué)生id and 課程id='k4') as 政治
,count(*) as 有效課程數(shù), avg(t.成績(jī)) as 平均成績(jī)
from 成績(jī)表 as t
group by 學(xué)生id
order by 平均成績(jī)
這可以說(shuō)也是一個(gè)很規(guī)矩的解法,在這種應(yīng)用場(chǎng)合,子查詢要比聯(lián)接代碼可讀性強(qiáng)得多。
如果數(shù)據(jù)庫(kù)引擎認(rèn)
為把它解析成聯(lián)接更好,那就由它去吧,其實(shí)本來(lái)相關(guān)子查詢也肯定含有連接。這里再補(bǔ)充一下,在實(shí)際應(yīng)用
中如果再加一張表 ranks(rank,minvalue,maxvalue):
┌──────────┬──────────┬──────────┐
│ rank │ minvalue │ maxvalue │
├──────────┼──────────┼──────────┤
│ a │ 90 │ 100 │
├──────────┼──────────┼──────────┤
│ b │ 89 │ 80 │
├──────────┼──────────┼──────────┤
│ c │ 79 │ 70 │
├──────────┼──────────┼──────────┤
│ d │ 69 │ 60 │
├──────────┼──────────┼──────────┤
│ e │ 60 │ 0 │
└──────────┴──────────┴──────────┘
就可以實(shí)現(xiàn)一個(gè)非常有實(shí)用價(jià)值的應(yīng)用:
select 學(xué)生id,max(學(xué)生姓名) as 學(xué)生姓名
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k1') as 數(shù)學(xué)
,(select max(rank)
from ranks ,t
where t.成績(jī) >= ranks.minvalue
and t.成績(jī) <= ranks.maxvalue
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k1'
) as 數(shù)學(xué)級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k2') as 語(yǔ)文
,(select min(rank)
from ranks ,t
where t.成績(jī) >= ranks.minvalue
and t.成績(jī) <= ranks.maxvalue
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k2'
) as 語(yǔ)文級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k3') as 英語(yǔ)
,(select max(rank)
from ranks ,t
where t.成績(jī) >= ranks.minvalue
and t.成績(jī) <= ranks.maxvalue
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k3'
) as 英語(yǔ)級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k4') as 政治
,(select min(rank)
from ranks ,t
where t.成績(jī) >= ranks.minvalue
and t.成績(jī) <= ranks.maxvalue
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k4'
) as 政治級(jí)別
,count(*),avg(t0.成績(jī))
,(select max(rank)
from ranks
where avg(t0.成績(jī)) >= ranks.minvalue
and avg(t0.成績(jī)) <= ranks.maxvalue
) as 平均級(jí)別
from t as t0
group by 學(xué)生id
這里表面上使用了不等連接,再仔細(xì)想想,ranks 表中每條記錄的區(qū)間是沒有交集的,
其實(shí)也可以認(rèn)為是等值連接,這樣的表設(shè)計(jì)無(wú)疑存在著良好的擴(kuò)展性,如果題目只要求
列印(學(xué)生id,學(xué)生姓名,有效課程數(shù),有效平均分,平均分級(jí)別):
select 學(xué)生id,max(學(xué)生姓名) as 學(xué)生姓名,count(*),avg(t0.成績(jī))
,(select max(rank)
from ranks
where avg(t0.成績(jī)) >= ranks.minvalue
and avg(t0.成績(jī)) <= ranks.maxvalue
) as 平均級(jí)別
from t as t0
group by 學(xué)生id
則這樣的解決方案就比較全面了。
回到原題,再介紹一個(gè)比較取巧的辦法,僅需一個(gè)簡(jiǎn)單分組查詢就可解決問題,有經(jīng)驗(yàn)的讀者可能已經(jīng)想到了
,那就是 case:
select 學(xué)生id, min(學(xué)生姓名)
,sum(case 課程id when 'k1' then 成績(jī) else 0 end) as 數(shù)學(xué)
,sum(case 課程id when 'k2' then 成績(jī) else 0 end) as 語(yǔ)文
,sum(case 課程id when 'k3' then 成績(jī) else 0 end) as 英語(yǔ)
,sum(case 課程id when 'k4' then 成績(jī) else 0 end) as 政治
,count(*) as 有效課程數(shù), avg(t.成績(jī)) as 平均成績(jī)
from 成績(jī)表 as t
group by 學(xué)生id
order by 平均成績(jī) desc
雖然可能初看答案感覺有點(diǎn)怪,其實(shí)很好理解,可讀性并不低,效率也很高。但它不能
像前一個(gè)答案那樣,在成績(jī)中區(qū)分出某一門課這個(gè)學(xué)生究竟是缺考 (null),還是真得
零分。這個(gè)解法充分利用了 case 語(yǔ)句進(jìn)行數(shù)據(jù)分類的作用: case 將成績(jī)按課程分
成四類,sum 用來(lái)消去多余的 0。
select [t].[學(xué)生id],max([t].[學(xué)生姓名]) as 學(xué)生姓名
,max([t1].[成績(jī)]) as 數(shù)學(xué),max([t2].[成績(jī)]) as 語(yǔ)文,max([t3].[成績(jī)]) as 英語(yǔ),max([t4].[成績(jī)]) as 政治, count([t].[課程id]) as 有效課程數(shù)
,(isnull(max([t1].[成績(jī)]),0) + isnull(max([t2].[成績(jī)]),0) + isnull(max([t3].[成績(jī)]),0) + isnull(max([t4].[成績(jī)]),0)) / count([t].[課程id]) as 有效平均分
from 成績(jī)表 t
left join 成績(jī)表 as [t1]
on [t].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join 成績(jī)表 as [t2]
on [t].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join 成績(jī)表 as [t3]
on [t].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join 成績(jī)表 as [t4]
on [t].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
group by [t].[學(xué)生id]
order by 有效平均分 desc
這個(gè)方法是相當(dāng)正統(tǒng)的聯(lián)接解法,盡管寫起來(lái)麻煩了些,但還是不難理解的。再?gòu)膶?shí)用
角度考慮一下,真實(shí)需求往往不是象本題明確提出"列印四門 (數(shù)學(xué),語(yǔ)文,英語(yǔ),政治)
課程"這樣的相對(duì)靜態(tài)的需求,該是動(dòng)態(tài) sql 大顯身手的時(shí)候了,很明顯方法一的寫法
無(wú)疑是利用程序構(gòu)造動(dòng)態(tài) sql 的最好選擇,當(dāng)然另兩個(gè) sql 規(guī)律還是挺明顯的,同樣
不難構(gòu)造。以 case 版答案為例: 先用一個(gè)游標(biāo)遍歷,取出所有課程湊成:
sum(case '課程id' when '課程名稱' then 成績(jī) else 0 end) as 課程名稱 形式,
再補(bǔ)上 select 和 from、where 等必要條件,一個(gè)生成動(dòng)態(tài)成績(jī)單的 sql 就誕生了,
只要再由相關(guān)程序調(diào)用執(zhí)行即可,這樣就可以算一個(gè)更完善的解決方案了。
其實(shí),最類似的典型應(yīng)用是在主、細(xì)關(guān)系中的主表投影中實(shí)現(xiàn)細(xì)表的匯總統(tǒng)計(jì)行,
例如兩張表:
master(f,f1,f2 ...) 一對(duì)多 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.按各科不平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序,統(tǒng)計(jì)并列印各科平均成績(jī)和不及格率的百分?jǐn)?shù)(用"n行"表示): (就是分析哪門課程難)
課程id,課程名稱,平均成績(jī),及格百分比
select 課程id,max(課程名稱) as 課程名稱,avg(成績(jī)) as 平均成績(jī)
,100 * sum(case when 成績(jī) >=60 then 1 else 0 end)/count(*) as 及格百分?jǐn)?shù)
from 成績(jī)表 t
group by 課程id
order by 及格百分比 desc
這道題應(yīng)該說(shuō)是算簡(jiǎn)單的了,就是用"行"來(lái)提供表現(xiàn)形式的。只要想明白要對(duì)數(shù)據(jù)如
何分組,取統(tǒng)計(jì)聚集函數(shù),就萬(wàn)事大吉了。
5.列印四門課程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行4列"表示): (就是分析哪門課程難)
數(shù)學(xué)平均分,數(shù)學(xué)及格百分?jǐn)?shù),語(yǔ)文平均分,語(yǔ)文及格百分?jǐn)?shù),英語(yǔ)平均分,英語(yǔ)及格百分?jǐn)?shù),政治平均分,政治及格百分?jǐn)?shù)
這道題其實(shí)就是上一題的"列"表現(xiàn)形式版本,相對(duì)于上一題,本題是靜態(tài)的,因?yàn)楸绢}
同第三題一樣利用行上的數(shù)據(jù)構(gòu)造了列,要實(shí)現(xiàn)擴(kuò)展必須再利用另外的程序構(gòu)造動(dòng)態(tài)
sql:
select sum(case when 課程id = 'k1' then 成績(jī) else 0 end)/sum(case 課程id when 'k1' then 1 else 0 end) as 數(shù)學(xué)平均分
,100 * sum(case when 課程id = 'k1' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k1' then 1 else 0 end) as 數(shù)學(xué)及格百分?jǐn)?shù)
,sum(case when 課程id = 'k2' then 成績(jī) else 0 end)/sum(case 課程id when 'k2' then 1 else 0 end) as 語(yǔ)文平均分
,100 * sum(case when 課程id = 'k2' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k2' then 1 else 0 end) as 語(yǔ)文及格百分?jǐn)?shù)
,sum(case when 課程id = 'k3' then 成績(jī) else 0 end)/sum(case 課程id when 'k3' then 1 else 0 end) as 英語(yǔ)平均分
,100 * sum(case when 課程id = 'k3' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k3' then 1 else 0 end) as 英語(yǔ)及格百分?jǐn)?shù)
,sum(case when 課程id = 'k4' then 成績(jī) else 0 end)/sum(case 課程id when 'k4' then 1 else 0 end) as 政治平均分
,100 * sum(case when 課程id = 'k4' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k4' then 1 else 0 end) as 政治及格百分?jǐn)?shù)
from 成績(jī)表 t
這一句看起來(lái)很長(zhǎng),但實(shí)際上是最經(jīng)典的 case 運(yùn)用,很實(shí)用的數(shù)據(jù)分析技術(shù)。先將原
表中的成績(jī)一列連續(xù)投影 8 次備用于四門不同課程,充分利用 case 和數(shù)據(jù)的值域
['k1','k2','k3','k4']來(lái)劃分?jǐn)?shù)據(jù),再利用 sum() [1 + ...+ 1] 實(shí)現(xiàn)了看似本來(lái)應(yīng)
該用 count(*) 的計(jì)數(shù)器的功能,這里面不要說(shuō)聯(lián)接和子查詢,甚至連 group by 分組
的痕跡都找不到!如果讀起來(lái)吃力,完全可以先只保留一個(gè)字段,相對(duì)好理解些,看懂后
逐一補(bǔ)全。本題也可以算一個(gè)"行變列"的交叉透視表示例吧! 另外,"行"相對(duì)于"列"
是動(dòng)態(tài)的,"行"是相對(duì)無(wú)限的,"列"是相對(duì)有限的,"行"的增刪是應(yīng)用級(jí)的,可"隨意"增
刪,"列"的增刪是管理級(jí)的,不要輕易變動(dòng)!
6.按不同老師所教不同課程平均分從高到低列印: (就是分析哪個(gè)老師的哪個(gè)課程水平高)
教師id,教師姓名,課程id,課程名稱,平均分
select 教師id,max(教師姓名) as 教師姓名,課程id,max(課程名稱) as 課程名稱,avg(成績(jī)) as 平均成績(jī)
from 成績(jī)表 t
group by 課程id,教師id
order by avg(成績(jī)) desc
這道題的確沒啥好說(shuō)的,就算閉著眼,不動(dòng)手,答案也應(yīng)脫口而出!
如果平均分按去掉一個(gè)最高分和一個(gè)最低分后取得,則也不難寫出:
select 教師id,max(教師姓名),課程id,max(課程名稱) as 課程名稱 --,avg(成績(jī)) as 平均成績(jī)
,(sum(成績(jī))
-(select max(成績(jī))
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id)
-(select min(成績(jī))
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id))
/ cast((select count(*) -2
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id) as float) as 平均分
from 成績(jī)表 as t1
where (select count(*) -2
from 成績(jī)表
where 課程id = t1.課程id and 教師id = t1.教師id) >0
group by 課程id,教師id
order by 平均分 desc
7.列印數(shù)學(xué)成績(jī)第 10 名到第 15 名的學(xué)生成績(jī)單
或列印平均成績(jī)第 10 名到第 15 名的學(xué)生成績(jī)單
[學(xué)生id],[學(xué)生姓名],數(shù)學(xué),語(yǔ)文,英語(yǔ),政治,平均成績(jī)
如果只考慮一門課程,如:數(shù)學(xué)成績(jī),非常簡(jiǎn)單:
select top 5 *
from t
where 課程id ='k1'
and 成績(jī) not in(select top 15 成績(jī)
from t
order by 成績(jī) desc
)
order by 成績(jī) desc
select *
from t
where 課程id ='k1'
and 成績(jī) not in(select top 10 成績(jī)
from t
order by 成績(jī) desc
)
and 成績(jī) in(select top 15 成績(jī)
from t
order by 成績(jī) desc
)
order by 成績(jī) desc
從邏輯上說(shuō),第 10 名到第 15 名就是從原前 15 名,"再"挑出前 5 名不要,保留剩下
的 5 名。第二種寫法是從前 15 名里挑出不屬于原前 10 名的記錄,把兩個(gè)數(shù)據(jù)集做
一個(gè)差,因此要多用一個(gè)
子查詢,效率相對(duì)較低,它,如果要有《ansi/iso sql》的 except
關(guān)鍵字就是最理想的了。
這種技巧在數(shù)據(jù)"分頁(yè)"的應(yīng)用中經(jīng)常利用,只要遵循如下原則即可:
select top @pagesize *
from t
where sortfield not in (select top @pagesize * @pagei sortfield
from t
order by sortfield
)
order by sortfield
至此,該題考察的主要目的已經(jīng)達(dá)到。至于列印明晰成績(jī)單:
[學(xué)生id],[學(xué)生姓名],數(shù)學(xué),語(yǔ)文,英語(yǔ),政治,平均成績(jī) 前面也有類似的題目,做起來(lái)
確實(shí)麻煩,因此下面僅提供參考答案,就不贅述了:
select distinct top 5
[成績(jī)表].[學(xué)生id],
[成績(jī)表].[學(xué)生姓名] as 學(xué)生姓名,
[t1].[成績(jī)] as 數(shù)學(xué),
[t2].[成績(jī)] as 語(yǔ)文,
[t3].[成績(jī)] as 英語(yǔ),
[t4].[成績(jī)] as 政治,
isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) as 總分
from [成績(jī)表]
left join [成績(jī)表] as [t1]
on [成績(jī)表].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [成績(jī)表] as [t2]
on [成績(jī)表].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [成績(jī)表] as [t3]
on [成績(jī)表].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [成績(jī)表] as [t4]
on [成績(jī)表].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
where isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
not in
(select
distinct
top 15 with ties
isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
from [成績(jī)表]
left join [成績(jī)表] as [t1]
on [成績(jī)表].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [成績(jī)表] as [t2]
on [成績(jī)表].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [成績(jī)表] as [t3]
on [成績(jī)表].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [成績(jī)表] as [t4]
on [成績(jī)表].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
order by isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) desc)
最后還要多說(shuō)一句: 一般 top 關(guān)鍵字與 order by 子句合用才有真正意義。
8.統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):
課程id,課程名稱,[100-85],[85-70],[70-60],[<60]
盡管表面看上去不那么容易,其實(shí)用 case 可以很容易地實(shí)現(xiàn):
select 課程id, 課程名稱
,sum(case when 成績(jī) between 85 and 100 then 1 else 0 end) as [100 - 85]
,sum(case when 成績(jī) between 70 and 85 then 1 else 0 end) as [85 - 70]
,sum(case when 成績(jī) between 60 and 70 then 1 else 0 end) as [70 - 60]
,sum(case when 成績(jī) < 60 then 1 else 0 end) as [60 -]
from 成績(jī)表
group by 課程id, 課程名稱
注意這里的 between,雖然字段名都是從高至低,可 between 中還是要從低到高,這里
如果不小心,會(huì)犯一個(gè)很難發(fā)現(xiàn)的邏輯錯(cuò)誤: 在數(shù)學(xué)上,當(dāng)a > b 時(shí),[a, b]是一個(gè)空集。
9.列印學(xué)生平均成績(jī)及其名次
select count(distinct b.f) as 名次,a.學(xué)生id,max(a.學(xué)生姓名),max(a.f)
from (select distinct t.學(xué)生id,t.學(xué)生姓名,(select avg(成績(jī))
from t t1
where t1.學(xué)生id = t.學(xué)生id) as f
from t
) as a,
(select distinct t.學(xué)生id,t.學(xué)生姓名,(select avg(成績(jī))
from t t1
where t1.學(xué)生id = t.學(xué)生id) as f
from t
) as b
where a.f <= b.f
group by a.學(xué)生id
order by count(b.f)
這里有很多值得一提的地方,先利用兩個(gè)完全相同的自相關(guān)子查詢生成兩個(gè)派生表作
為基本表用于作小于或等于的連接,這樣就可以通過表中小于或等于每個(gè)值的其他值
的 count(distinct) 的計(jì)數(shù)聚集函數(shù)來(lái)體現(xiàn)名次了。
select 1+(select count(distinct [平均成績(jī)])
from (select [學(xué)生id],max([學(xué)生姓名]) as 學(xué)生姓名 ,avg([成績(jī)]) as [平均成績(jī)]
from t
group by [學(xué)生id]
) as t1
where [平均成績(jī)] > t2.[平均成績(jī)]) as 名次,
[學(xué)生id],[學(xué)生姓名],[平均成績(jī)]
from (select [學(xué)生id],max([學(xué)生姓名]) as 學(xué)生姓名,avg([成績(jī)]) as [平均成績(jī)]
from t
group by [學(xué)生id]
) as t2
order by t2.[平均成績(jī)] desc
方法二也使用了兩個(gè)完全相同的自相關(guān)子查詢生成兩個(gè)派生表作為基本表,再利用它
們之間作大于的相關(guān)子查詢?nèi)?count(distinct) + 1 的計(jì)數(shù)聚集函數(shù)同樣實(shí)現(xiàn)了名
次的顯示。
這道題從應(yīng)用角度來(lái)看,查詢結(jié)果是相當(dāng)合理的,并列情況的名次也都一樣。但如果想
實(shí)現(xiàn)類似自動(dòng)序列的行號(hào),該解決方案的局限性突顯,不能處理并列相等的情況了,所
以有必要強(qiáng)調(diào):一定要選擇不重復(fù)的連接條件,可以根據(jù)實(shí)際情況利用字段組合的不等
連接 (t1.f1 + ... + t1.fn <= t2.f1 + ... + t2.fn)。繼續(xù)引申還可以通過判斷
count(distinct) % 2 是否為 0 的 having 或 where 子句實(shí)現(xiàn)只顯示偶數(shù)或奇數(shù)行:
having count(distinct b.f) % 2 = 1
或:
where 1+(select count(distinct [平均成績(jī)])
from (select [學(xué)生id],max([學(xué)生姓名]) as 學(xué)生姓名 ,avg([成績(jī)]) as [平均成績(jī)]
from t
group by [學(xué)生id]
) as t1
where [平均成績(jī)] > t2.[平均成績(jī)]) % 2 =1
再簡(jiǎn)單說(shuō)一下 having 和 where 在含有 group by 分組的查詢中的區(qū)別,having 是
在數(shù)據(jù)分組后才篩選記錄的,where 是先進(jìn)行篩選在分組的,而且 having 一般應(yīng)與聚
集函數(shù)合用才有真正含義。
兩種方法再次體現(xiàn)了子查詢與連接可以殊途同歸之妙,第二種子查詢方法值得推薦,因
為比較利于程序構(gòu)造,便于為沒有該功能的原有查詢添加此項(xiàng)功能。本題僅僅是為了示
范一種比較新穎的解題思路,回避了效率的問題。
10.列印各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
學(xué)生id,學(xué)生姓名,課程id,課程名稱,成績(jī),教師id,教師姓名
如果僅從成績(jī)考慮前三名的人,利用相關(guān)子查詢的知識(shí):
select *
from 成績(jī)表 t1
where 成績(jī) in (select top 3 成績(jī)
from 成績(jī)表
where t1.課程id = 課程id
order by 成績(jī) desc
)
order by t1.課程id
這樣查詢的結(jié)果各科成績(jī)前三名的記錄數(shù)應(yīng)該大于等于三,因?yàn)榭赡苡胁⒘星闆r,
如果小于三自然是該門課還沒有那么多人考試!
如果不考慮并列情況,嚴(yán)格控制各科只列印三條記錄,則使用"學(xué)生id"構(gòu)造相關(guān)
子查詢條件亦可:
select *
from 成績(jī)表 t1
where 學(xué)生id in (select top 2 學(xué)生id
from 成績(jī)表
where t1.課程id = 課程id
order by 成績(jī) desc
)
order by t1.課程id
如果利用第 10 題的思路也可實(shí)現(xiàn)該應(yīng)用。
11.規(guī)范化
規(guī)范化的問題可以說(shuō)是仁者見仁,智者見智。而且不做肯定不好,但過猶不及,搞到太
規(guī)范也不一定是好事。首先分析信息的對(duì)應(yīng)關(guān)系,這個(gè)表中有四種信息。學(xué)生、課程、教師、成績(jī)。其中前三個(gè)可以獨(dú)立存在,最
后一個(gè)可以看做是基于前三個(gè)存在的。然后,我們按這四種分類,建立四個(gè)表:
關(guān)于學(xué)生的信息,有以下兩個(gè):學(xué)生id,姓名;
教師則會(huì)有教師id,姓名,課程id 這也就是為什么我要把學(xué)生和教師會(huì)為兩個(gè)表的原因;
課程則有課程id,課程名稱兩種;
而最后一個(gè)成績(jī)信息,就成為了聯(lián)接它們的一個(gè)部分,在這里,它要有學(xué)生id,教師id,課程id,成績(jī)四項(xiàng),相
對(duì)與其它表應(yīng)屬應(yīng)用級(jí)別,除了成績(jī)字段,其它都引用的另外的表。
這樣一來(lái),幾個(gè)表的腳本大概是這個(gè)樣子:
create table "學(xué)生信息"
(
"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 "成績(jī)信息"
(
"學(xué)生id" char(4),
"教師id" char(4),
"課程id" char(4),
成績(jī) numeric(5, 2),
primary key("學(xué)生id", "教師id", "課程id"),
foreign key("學(xué)生id") references "學(xué)生信息"("id"),
foreign key("教師id") references "教師信息"("id"),
foreign key("課程id") references "課程信息"("id")
)
這樣建表很明顯是為了盡可能的細(xì)化信息的分類。它的好處在于各種信息分劃明確,不
過問題也很明顯,比如,一個(gè)教師不能同時(shí)帶兩門不同的課(當(dāng)然,這可能正是業(yè)務(wù)規(guī)則所
要求的),而且,這樣做分類過于細(xì)膩了。
如果不需要對(duì)教師進(jìn)行人事管理,那么,完全可以把教師信息和課程信息合為一表。也就是說(shuō),不同教師帶的同
一名稱課程,視做不同課程。這樣做當(dāng)然也有其應(yīng)用背景,很多教師,特別是高等教育和名師,往往有他們自
己的風(fēng)格,完全可以視做兩種課程,相信同樣教授 c++ , lippman 和 stroustrup 教出的學(xué)生總會(huì)有所不同。
要說(shuō)問題,那就是,如果想要限制學(xué)生不能重復(fù)修某一門課,就得用觸發(fā)器了,沒有太好的辦法,不過這個(gè)問題,
前面的第一種設(shè)計(jì)同樣解決不了,就算針對(duì)教師和課程的關(guān)系單建一個(gè)表也不一定就可以,還把問題復(fù)雜化了。
現(xiàn)在把第二種設(shè)計(jì)的腳本列出來(lái):
create table "學(xué)生信息"
(
"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 "成績(jī)信息"
(
"學(xué)生id" char(4),
"課程id" char(4),
成績(jī) numeric(5, 2),
primary key("學(xué)生id", "課程id"),
foreign key("學(xué)生id") references "學(xué)生信息"("id"),
foreign key("課程id") references "課程信息"("id")-
)
這樣是不是能清爽一點(diǎn)?這樣一來(lái),如果不存在一個(gè)教師教不同的課程的情況,并且我
們希望簡(jiǎn)化管理,甚至都可以不用"課程分類"和"教師id"字段。當(dāng)然,視業(yè)務(wù)需要而定,
如果希望在限制學(xué)生學(xué)習(xí)的課程分類的同時(shí),不想帶來(lái)額外的性能開銷,使用第一種設(shè)
計(jì),或?qū)⒄n程分類字段也列入成績(jī)信息表,是一個(gè)更好的辦法。
關(guān)于數(shù)據(jù)庫(kù)的設(shè)計(jì)和管理,有幾條經(jīng)驗(yàn),拿出來(lái)在這里和大家交流一下:
對(duì)數(shù)據(jù)進(jìn)行規(guī)范化時(shí),最好要符合它的應(yīng)用背景。這樣易于理解和管理;
數(shù)據(jù)的規(guī)范化不一定是越細(xì)化越好,粒度適當(dāng)?shù)卮笠稽c(diǎn),后面的編程一般會(huì)容易一點(diǎn);
雖說(shuō)不是越細(xì)越好,不過要是不做規(guī)范化,卻幾乎是一定要出問題;
很重要的一點(diǎn): 千萬(wàn)不要濫用自動(dòng)標(biāo)識(shí)列! 特別是,不要濫用自動(dòng)標(biāo)識(shí)列來(lái)做為一個(gè)表中唯一的約束條件,通常,
那和沒有約束沒什么不同!
關(guān)于這些試題,我們的看法就到這里,希望朋友們可以拿出更多更好的意見,我們一起討論。
原題含答案:
create table [t] (
[id] [int] identity (1, 1) not null,
[學(xué)生id] [varchar] (50) null,
[學(xué)生姓名] [varchar] (50) null,
[課程id] [varchar] (50) null,
[課程名稱] [varchar] (50) null,
[成績(jī)] [real] null,
[教師id] [varchar] (50) null ,
[教師姓名] [varchar] (50) null,
constraint [pk_t] primary key clustered
(
[id]
) on [primary]
) on [primary]
go
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s3','王五','k2','語(yǔ)文',81,'t2','王老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s3','王五','k4','政治',53,'t4','趙老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s4','趙六','k1','數(shù)學(xué)',99,'t1','張老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s4','趙六','k2','語(yǔ)文',33,'t2','王老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s4','趙六','k4','政治',59,'t4','趙老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s1','張三','k4','政治',79,'t4','趙老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s1','張三','k1','數(shù)學(xué)',98,'t1','張老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s1','張三','k3','英語(yǔ)',69,'t3','李老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s7','peter','k1','數(shù)學(xué)',64,'t1','張老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s7','peter','k2','語(yǔ)文',81,'t2','王老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s7','peter','k4','政治',53,'t4','趙老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s2','mike','k1','數(shù)學(xué)',64,'t1','張老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s2','mike','k2','語(yǔ)文',81,'t2','王老師')
insert into t ([學(xué)生id],[學(xué)生姓名],[課程id],[課程名稱],[成績(jī)],[教師id],[教師姓名])
values ('s2','mike','k4','政治',53,'t4','趙老師')
二維表 t(f1,f2,f3,f4,f5,f6,f7) 表示如下關(guān)系:
┌──────────┬──────────┬──────────┬──────────┬──────────┬──────────┬──────────┐
│ 學(xué)生id │ 學(xué)生姓名 │ 課程id │ 課程名稱 │ 成績(jī) │ 教師id │ 教師姓名 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k4 │ 政治 │ 53 │ t4 │ 趙老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k1 │ 數(shù)學(xué) │ 61 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k3 │ 英語(yǔ) │ 88 │ t3 │ 李老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k4 │ 政治 │ 77 │ t4 │ 趙老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k4 │ 政治 │ 67 │ t5 │ 周老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k2 │ 語(yǔ)文 │ 90 │ t2 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s3 │ 王五 │ k1 │ 數(shù)學(xué) │ 55 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k2 │ 語(yǔ)文 │ 81 │ t2 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s4 │ 趙六 │ k2 │ 語(yǔ)文 │ 59 │ t1 │ 王老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s1 │ 張三 │ k3 │ 英語(yǔ) │ 37 │ t3 │ 李老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ s2 │ 李四 │ k1 │ 數(shù)學(xué) │ 81 │ t1 │ 張老師 │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ .... │ │ │ │ │ │ │
├──────────┼──────────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ .... │ │ │ │ │ │ │
└──────────┴──────────┴──────────┴──────────┴──────────┴──────────┴──────────┘
二維表 t(f1,f2,f3,f4,f5,f6,f7) 表示如下關(guān)系:
------------------------------------------------------------------------------
│ 學(xué)生id │ 學(xué)生姓名 │ 課程id │ 課程名稱 │ 成績(jī) │ 教師id │ 教師姓名 │
│ s3 │ 王五 │ k4 │ 政治 │ 53 │ t4 │ 趙老師 │
│ s1 │ 張三 │ k1 │ 數(shù)學(xué) │ 61 │ t1 │ 張老師 │
│ s2 │ 李四 │ k3 │ 英語(yǔ) │ 88 │ t3 │ 李老師 │
│ s1 │ 張三 │ k4 │ 政治 │ 77 │ t4 │ 趙老師 │
│ s2 │ 李四 │ k4 │ 政治 │ 67 │ t5 │ 周老師 │
│ s3 │ 王五 │ k2 │ 語(yǔ)文 │ 90 │ t2 │ 王老師 │
│ s3 │ 王五 │ k1 │ 數(shù)學(xué) │ 55 │ t1 │ 張老師 │
│ s1 │ 張三 │ k2 │ 語(yǔ)文 │ 81 │ t2 │ 王老師 │
│ s4 │ 趙六 │ k2 │ 語(yǔ)文 │ 59 │ t1 │ 王老師 │
│ s1 │ 張三 │ k3 │ 英語(yǔ) │ 37 │ t3 │ 李老師 │
│ s2 │ 李四 │ k1 │ 數(shù)學(xué) │ 81 │ t1 │ 張老師 │
│ .... │ │ │ │ │ │ │
│ .... │ │ │ │ │ │ │
------------------------------------------------------------------------------
1.規(guī)范化
請(qǐng)以一句 t-sql (ms sql server) 或 jet sql (ms access) 作答!
2.如果 t 表還有一字段 f0 數(shù)據(jù)類型為自動(dòng)增量整型(唯一,不會(huì)重復(fù)),
而且 t 表中含有除 f0 字段外,請(qǐng)刪除其它字段完全相同的重復(fù)多余的臟記錄數(shù)據(jù)(要保留其中的一條):
delete t
from t, t as t1
where t.學(xué)生id=t1.學(xué)生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.列印各科成績(jī)最高和最低的記錄: (就是各門課程的最高、最低分的學(xué)生和老師)
課程id,課程名稱,最高分,學(xué)生id,學(xué)生姓名,教師id,教師姓名,最低分,學(xué)生id,學(xué)生姓名,教師id,教師姓名
select t.課程id,t.課程名稱,t.[成績(jī)] as 最高分,t.[學(xué)生id],t.[學(xué)生姓名],t.[教師id],t.[教師姓名]
,t1.[成績(jī)] as 最低分,t1.[學(xué)生id],t1.[學(xué)生姓名],t1.[教師id],t1.[教師姓名]
from t
left join t as t1 on t.[課程id] = t1.[課程id]
where t.[成績(jī)] = (select max(t2.[成績(jī)])
from t as [t2]
where t.[課程id] = t2.[課程id]
group by t2.[課程id])
and t1.[成績(jī)] = (select min(t3.[成績(jī)])
from t as [t3]
where t1.[課程id] = t3.[課程id]
group by t3.[課程id])
4.按成績(jī)從高到低順序,列印所有學(xué)生四門(數(shù)學(xué),語(yǔ)文,英語(yǔ),政治)課程成績(jī): (就是每個(gè)學(xué)生的四門課程的成績(jī)單)
學(xué)生id,學(xué)生姓名,數(shù)學(xué),語(yǔ)文,英語(yǔ),政治,有效課程數(shù),有效平均分
(注: 有效課程即在 t 表中有該學(xué)生的成績(jī)記錄,如不明白可不列印"有效課程數(shù)"和"有效平均分")
select 學(xué)生id,max(學(xué)生姓名) as 學(xué)生姓名
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k1') as 數(shù)學(xué)
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k2') as 語(yǔ)文
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k3') as 英語(yǔ)
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k4') as 政治
,count(*),avg(t0.成績(jī))
from t as t0
group by 學(xué)生id
select 學(xué)生id,max(學(xué)生姓名) as 學(xué)生姓名
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k1') as 數(shù)學(xué)
,(select max(class)
from classes ,t
where t.成績(jī) >= classes.minv
and t.成績(jī) <= classes.maxv
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k1'
) as 數(shù)學(xué)級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k2') as 語(yǔ)文
,(select min(class)
from classes ,t
where t.成績(jī) >= classes.minv
and t.成績(jī) <= classes.maxv
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k2'
) as 語(yǔ)文級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k3') as 英語(yǔ)
,(select max(class)
from classes ,t
where t.成績(jī) >= classes.minv
and t.成績(jī) <= classes.maxv
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k3'
) as 英語(yǔ)級(jí)別
,(select 成績(jī) from t where 學(xué)生id=t0.學(xué)生id and 課程id='k4') as 政治
,(select min(class)
from classes ,t
where t.成績(jī) >= classes.minv
and t.成績(jī) <= classes.maxv
and t.學(xué)生id=t0.學(xué)生id and t.課程id='k4'
) as 政治級(jí)別
,count(*),avg(t0.成績(jī))
,(select max(class)
from classes
where avg(t0.成績(jī)) >= classes.minv
and avg(t0.成績(jī)) <= classes.maxv
) as 平均級(jí)別
from t as t0
group by 學(xué)生id
select [t].[學(xué)生id],max([t].[學(xué)生姓名]) as 學(xué)生姓名,max([t1].[成績(jī)]) as 數(shù)學(xué),max([t2].[成績(jī)]) as 語(yǔ)文,max([t3].[成績(jī)]) as 英語(yǔ),max([t4].[成績(jī)]) as 政治, count([t].[課程id]) as 有效課程數(shù) ,(isnull(max([t1].[成績(jī)]),0) + isnull(max([t2].[成績(jī)]),0) + isnull(max([t3].[成績(jī)]),0) + isnull(max([t4].[成績(jī)]),0)) / count([t].[課程id]) as 有效平均分
from [t]
left join [t] as [t1]
on [t].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [t] as [t2]
on [t].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [t] as [t3]
on [t].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [t] as [t4]
on [t].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
group by [t].[學(xué)生id]
order by (isnull(max([t1].[成績(jī)]),0) + isnull(max([t2].[成績(jī)]),0) + isnull(max([t3].[成績(jī)]),0) + isnull(max([t4].[成績(jī)]),0)) / count([t].[課程id]) desc
5.列印數(shù)學(xué)成績(jī)第 10 名到第 15 名的學(xué)生成績(jī)單
或列印平均成績(jī)第 10 名到第 15 名的學(xué)生成績(jī)單
[學(xué)生id],[學(xué)生姓名],數(shù)學(xué),語(yǔ)文,英語(yǔ),政治,平均成績(jī)
select distinct
[t].[學(xué)生id],
[t].[學(xué)生姓名] as 學(xué)生姓名,
[t1].[成績(jī)] as 數(shù)學(xué),
[t2].[成績(jī)] as 語(yǔ)文,
[t3].[成績(jī)] as 英語(yǔ),
[t4].[成績(jī)] as 政治,
isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) as 總分
from [t]
left join [t] as [t1]
on [t].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [t] as [t2]
on [t].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [t] as [t3]
on [t].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [t] as [t4]
on [t].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
where isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
not in
(select
distinct
top 3 with ties
isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
from [t]
left join [t] as [t1]
on [t].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [t] as [t2]
on [t].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [t] as [t3]
on [t].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [t] as [t4]
on [t].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
order by isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) desc)
and isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
in (select
distinct
top 4 with ties
isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0)
from [t]
left join [t] as [t1]
on [t].[學(xué)生id] = [t1].[學(xué)生id] and [t1].[課程id] = 'k1'
left join [t] as [t2]
on [t].[學(xué)生id] = [t2].[學(xué)生id] and [t2].[課程id] = 'k2'
left join [t] as [t3]
on [t].[學(xué)生id] = [t3].[學(xué)生id] and [t3].[課程id] = 'k3'
left join [t] as [t4]
on [t].[學(xué)生id] = [t4].[學(xué)生id] and [t4].[課程id] = 'k4'
order by isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) desc)
order by isnull([t1].[成績(jī)],0) + isnull([t2].[成績(jī)],0) + isnull([t3].[成績(jī)],0) + isnull([t4].[成績(jī)],0) desc
6.按各科不及格率的百分?jǐn)?shù)從低到高和平均成績(jī)從高到低順序,統(tǒng)計(jì)并列印各科平均成績(jī)和不及格率的百分?jǐn)?shù)(用"n行"表示): (就是分析哪門課程難)
課程id,課程名稱,平均成績(jī),及格百分?jǐn)?shù)
select 課程id,max(課程名稱) as 課程名稱,avg(成績(jī)) as 平均成績(jī),100 * sum(case when 成績(jī) >=60 then 1 else 0 end)/count(*) as 及格百分?jǐn)?shù)
from t
group by 課程id
order by 及格百分?jǐn)?shù) desc
7.列印四門課程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行4列"表示): (就是分析哪門課程難)
數(shù)學(xué)平均分,數(shù)學(xué)及格百分?jǐn)?shù),語(yǔ)文平均分,語(yǔ)文及格百分?jǐn)?shù),英語(yǔ)平均分,英語(yǔ)及格百分?jǐn)?shù),政治平均分,政治及格百分?jǐn)?shù)
select sum(case when 課程id = 'k1' then 成績(jī) else 0 end)/ (select count(*) from t where 課程id = 'k1') as 數(shù)學(xué)平均分
,100 * sum(case when 課程id = 'k1' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k1' then 1 else 0 end) as 數(shù)學(xué)及格百分?jǐn)?shù)
,sum(case when 課程id = 'k2' then 成績(jī) else 0 end)/ (select count(*) from t where 課程id = 'k2') as 語(yǔ)文平均分
,100 * sum(case when 課程id = 'k2' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k2' then 1 else 0 end) as 語(yǔ)文及格百分?jǐn)?shù)
,sum(case when 課程id = 'k3' then 成績(jī) else 0 end)/ (select count(*) from t where 課程id = 'k3') as 英語(yǔ)平均分
,100 * sum(case when 課程id = 'k3' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k3' then 1 else 0 end) as 英語(yǔ)及格百分?jǐn)?shù)
,sum(case when 課程id = 'k4' then 成績(jī) else 0 end)/ (select count(*) from t where 課程id = 'k4') as 政治平均分
,100 * sum(case when 課程id = 'k4' and 成績(jī) >= 60 then 1 else 0 end)/sum(case when 課程id = 'k4' then 1 else 0 end) as 政治及格百分?jǐn)?shù)
from t
8.按不同老師所教不同課程平均分從高到低列印: (就是分析哪個(gè)老師的哪個(gè)課程水平高)
教師id,教師姓名,課程id,課程名稱,平均分 (平均分按去掉一個(gè)最高分和一個(gè)最低分后取)
select 教師id,max(教師姓名),課程id,max(課程名稱) as 課程名稱,avg(成績(jī)) as 平均成績(jī)
from t
group by 課程id,教師id
order by avg(成績(jī))
平均分按去掉一個(gè)最高分和一個(gè)最低分后取得,則也不難寫出:
select 教師id,max(教師姓名),課程id,max(課程名稱) as 課程名稱 --,avg(成績(jī)) as 平均成績(jī)
,(sum(成績(jī))
-(select max(成績(jī))
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id)
-(select min(成績(jī))
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id))
/ cast((select count(*) -2
from 成績(jī)表
where 課程id= t1.課程id and 教師id = t1.教師id) as float) as 平均分
from 成績(jī)表 as t1
where (select count(*) -2
from 成績(jī)表
where 課程id = t1.課程id and 教師id = t1.教師id) >0
group by 課程id,教師id
order by 平均分 desc
9.統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):
課程id,課程名稱,[100-85],[85-70],[70-60],[<60]
select 課程id, 課程名稱
,sum(case when 成績(jī) between 85 and 100 then 1 else 0 end) as [100 - 85]
,sum(case when 成績(jī) between 70 and 85 then 1 else 0 end) as [85 - 70]
,sum(case when 成績(jī) between 60 and 70 then 1 else 0 end) as [70 - 60]
,sum(case when 成績(jī) < 60 then 1 else 0 end) as [60 -]
from t
group by 課程id, 課程名稱
11.列印學(xué)生平均成績(jī)及其名次
select count(distinct b.f),a.學(xué)生id,max(a.學(xué)生姓名),max(a.f)
from (select distinct t.學(xué)生id,t.學(xué)生姓名,(select avg(成績(jī))
from t t1
where t1.學(xué)生id = t.學(xué)生id) as f
from t
) as a,
(select distinct t.學(xué)生id,t.學(xué)生姓名,(select avg(成績(jī))
from t t1
where t1.學(xué)生id = t.學(xué)生id) as f
from t
) as b
where a.f <= b.f
group by a.學(xué)生id
order by count(b.f)
select 1+(select count(distinct [平均成績(jī)])
from (select [學(xué)生id],max([學(xué)生姓名]) as 學(xué)生姓名 ,avg([成績(jī)]) as [平均成績(jī)]
from t
group by [學(xué)生id]
) as t1
where [平均成績(jī)] > t2.[平均成績(jī)]) ,
[學(xué)生id],[學(xué)生姓名],[平均成績(jī)]
from
(
select [學(xué)生id],max([學(xué)生姓名]) as 學(xué)生姓名,avg([成績(jī)]) as [平均成績(jī)]
from t
group by [學(xué)生id]
) as t2
order by t2.[平均成績(jī)] desc
