免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

打開APP
userphoto
未登錄

開通VIP,暢享免費(fèi)電子書等14項(xiàng)超值服

開通VIP
SQL綜合應(yīng)用學(xué)習(xí)

SQL綜合應(yīng)用學(xué)習(xí)

[日期:2005-07-08]來源:CSDN  作者:[字體: ]

看完測(cè)試完下面這些試題,你的SQL水平一定會(huì)有新的提高。

下面我們先看一下題設(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    │   英語   │    88    │    T3    │  李老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   張三 │    K4    │   政治   │    77    │    T4    │  趙老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S2    │   李四 │    K4    │   政治   │    67    │    T5    │  周老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S3    │   王五 │    K2    │   語文   │    90    │    T2    │  王老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S3    │   王五 │    K1    │   數(shù)學(xué)   │    55    │    T1    │  張老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   張三 │    K2    │   語文   │    81    │    T2    │  王老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S4    │   趙六 │    K2    │   語文   │    59    │    T1    │  王老師  │
├─────┼────┼─────┼─────┼─────┼─────┼─────┤
│    S1    │   張三 │    K3    │   英語   │    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)然,這種情況完全可以利用主鍵約束來
  杜絕!然而,現(xiàn)實(shí)情況經(jīng)常是原始數(shù)據(jù)在"洗滌"后,方可安全使用,而且邏輯主鍵過早的
  約束,將會(huì)給采集原始數(shù)據(jù)帶來不便,例如:從刷卡機(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è)別名來引用基本表。別名的使用在編寫大
  量類似結(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
  無需返回結(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)單,就像翻譯自然語言,很精確地描述了符合條件記錄的特性,甚至
  第二個(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 語句的調(diào)試,有經(jīng)驗(yàn)的程序員都會(huì)先用無害的 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ù)被無意破壞的可能性,當(dāng)然數(shù)據(jù)提前備份也很重要。同理
  UPDATE 和 INSERT 寫操作也應(yīng)照此行事。從數(shù)據(jù)庫原理的關(guān)系運(yùn)算的角度來看 INSERT、
  UPDATE 和 DELETE 這些寫操作都屬于典型的"選擇(Selection)"運(yùn)算,UPDATE 和 INSERT
  而且還是"投影(Projection)"運(yùn)算,它們都是這些關(guān)系運(yùn)算的"寫"應(yīng)用的表現(xiàn)形式。
  其實(shí),查詢的目的也本來無非就是瀏覽、刪除、更
  新或插入。通常寫操作也比讀操作消耗更大,如果索引過多,只會(huì)降低效率。

  選擇"子查詢"還是"連接"在效率是有差別的,但最關(guān)鍵的差別還是表現(xiàn)在查詢的結(jié)果
  集的讀寫性上,開發(fā)人員在寫一個(gè)"只讀"應(yīng)用的查詢記錄集時(shí),"子查詢"和"連接"各自
  的效率就是應(yīng)該首先考慮的問題,但是如果要實(shí)現(xiàn)"可寫"應(yīng)用的查詢結(jié)果集,則無論是
  相關(guān)還是非相關(guān)子查詢都是在復(fù)雜應(yīng)用中難以避免的。

  以上解決方案中,應(yīng)該說第一種方法,簡(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é),語文,英語,政治)課程成績(jī): (就是每個(gè)學(xué)生的四門課程的成績(jī)單)
  學(xué)生ID,學(xué)生姓名,數(shù)學(xué),語文,英語,政治,有效課程數(shù),有效平均分
  (注: 有效課程即在 T 表中有該學(xué)生的成績(jī)記錄,如不明白可不列印"有效課程數(shù)"和"有效平均分")

  需要說明的是: 題目之所以明確提出"四門(數(shù)學(xué),語文,英語,政治)課程"是有道理的,
  因?yàn)閷?shí)現(xiàn)時(shí),的確無法避免使原基本表中的行上的數(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 語文 ,
        (SELECT 成績(jī) FROM 成績(jī)表 WHERE 學(xué)生ID=T.學(xué)生ID AND 課程ID=‘K3‘) AS 英語 ,
        (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ī)

  這可以說也是一個(gè)很規(guī)矩的解法,在這種應(yīng)用場(chǎng)合,子查詢要比聯(lián)接代碼可讀性強(qiáng)得多。
  如果數(shù)據(jù)庫引擎認(rèn)為把它解析成聯(lián)接更好,那就由它去吧,其實(shí)本來相關(guān)子查詢也肯定含有連接。
  這里再補(bǔ)充一下,在實(shí)際應(yīng)用中如果再加一張表 Ranks(Rank,MinValue,MaxValue):

  ┌─────┬─────┬─────┐
  │   Rank   │ MinValue │ MaxValue │
  ├─────┼─────┼─────┤
  │    A     │    90    │   100    │
  ├─────┼─────┼─────┤
  │    B     │    80    │    89    │
  ├─────┼─────┼─────┤
  │    C     │    70    │    79    │
  ├─────┼─────┼─────┤
  │    D     │    60    │    69    │
  ├─────┼─────┼─────┤
  │    E     │     0    │    59    │
  └─────┴─────┴─────┘

  就可以實(shí)現(xiàn)一個(gè)非常有實(shí)用價(jià)值的應(yīng)用:

select 學(xué)生ID,MAX(學(xué)生姓名) as 學(xué)生姓名
       ,(select 成績(jī) from 成績(jī)表 t where 學(xué)生ID=T0.學(xué)生ID and 課程ID=‘K1‘) as 數(shù)學(xué)
       ,(SELECT max(Rank) from Ranks ,成績(jī)表 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 成績(jī)表 t where 學(xué)生ID=T0.學(xué)生ID and 課程ID=‘K2‘) as 語文
       ,(SELECT min(Rank)
           from Ranks ,成績(jī)表 t
           where t.成績(jī) >= Ranks.MinValue
                 and t.成績(jī) <= Ranks.MaxValue
                 and t.學(xué)生ID=T0.學(xué)生ID and t.課程ID=‘K2‘ 
           ) as 語文級(jí)別
       ,(select 成績(jī) from 成績(jī)表 t where 學(xué)生ID=T0.學(xué)生ID and 課程ID=‘K3‘) as 英語
       ,(SELECT max(Rank)
           from Ranks ,成績(jī)表 t
           where t.成績(jī) >= Ranks.MinValue
                 and t.成績(jī) <= Ranks.MaxValue
                 and t.學(xué)生ID=T0.學(xué)生ID and t.課程ID=‘K3‘ 
           ) as 英語級(jí)別
       ,(select 成績(jī) from 成績(jī)表 t where 學(xué)生ID=T0.學(xué)生ID and 課程ID=‘K4‘) as 政治
       ,(SELECT min(Rank)
           from Ranks ,成績(jī)表 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 成績(jī)表 t0
group by 學(xué)生ID

  這里表面上使用了不等連接,再仔細(xì)想想,Ranks 表中每條記錄的區(qū)間是沒有交集的,
  其實(shí)也可以認(rèn)為是等值連接,這樣的表設(shè)計(jì)無疑存在著良好的擴(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 語文,
         SUM(CASE 課程ID WHEN ‘K3‘ THEN 成績(jī) ELSE 0 END) AS 英語,
         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 語句進(jìn)行數(shù)據(jù)分類的作用: CASE 將成績(jī)按課程分
  成四類,SUM 用來消去多余的 0。

  SELECT [T].[學(xué)生ID],MAX([T].[學(xué)生姓名]) AS 學(xué)生姓名,
         MAX([T1].[成績(jī)]) AS 數(shù)學(xué),
  MAX([T2].[成績(jī)]) AS 語文,
  MAX([T3].[成績(jī)]) AS 英語,
  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)接解法,盡管寫起來麻煩了些,但還是不難理解的。再從實(shí)用
  角度考慮一下,真實(shí)需求往往不是象本題明確提出"列印四門 (數(shù)學(xué),語文,英語,政治)
  課程"這樣的相對(duì)靜態(tài)的需求,該是動(dòng)態(tài) SQL 大顯身手的時(shí)候了,很明顯方法一的寫法
  無疑是利用程序構(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ī)
         ,str(100 * SUM(CASE WHEN 成績(jī) >=60 THEN 1 ELSE 0 END)/COUNT(*))+‘%‘ AS 及格百分比
    FROM 成績(jī)表 T
GROUP BY 課程ID
ORDER BY 及格百分比 DESC

  這道題應(yīng)該說是算簡(jiǎn)單的了,就是用"行"來提供表現(xiàn)形式的。只要想明白要對(duì)數(shù)據(jù)如
  何分組,取統(tǒng)計(jì)聚集函數(shù),就萬事大吉了。

5.列印四門課程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行4列"表示): (就是分析哪門課程難)
  數(shù)學(xué)平均分,數(shù)學(xué)及格百分?jǐn)?shù),語文平均分,語文及格百分?jǐn)?shù),英語平均分,英語及格百分?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 語文平均分
         ,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 語文及格百分?jǐn)?shù)
         ,SUM(CASE WHEN 課程ID = ‘K3‘ THEN 成績(jī) ELSE 0 END)/SUM(CASE 課程ID WHEN ‘K3‘ THEN 1 ELSE 0 END) AS 英語平均分
         ,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 英語及格百分?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

  這一句看起來很長(zhǎng),但實(shí)際上是最經(jīng)典的 CASE 運(yùn)用,很實(shí)用的數(shù)據(jù)分析技術(shù)。先將原
  表中的成績(jī)一列連續(xù)投影 8 次備用于四門不同課程,充分利用 CASE 和數(shù)據(jù)的值域
  [‘k1‘,‘k2‘,‘k3‘,‘k4‘]來劃分?jǐn)?shù)據(jù),再利用 SUM() [1 + ...+ 1] 實(shí)現(xiàn)了看似本來應(yīng)
  該用 COUNT(*) 的計(jì)數(shù)器的功能,這里面不要說聯(lián)接和子查詢,甚至連 Group by 分組
  的痕跡都找不到!如果讀起來吃力,完全可以先只保留一個(gè)字段,相對(duì)好理解些,看懂后
  逐一補(bǔ)全。本題也可以算一個(gè)"行變列"的交叉透視表示例吧! 另外,"行"相對(duì)于"列"
  是動(dòng)態(tài)的,"行"是相對(duì)無限的,"列"是相對(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

  這道題的確沒啥好說的,就算閉著眼,不動(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é),語文,英語,政治,平均成績(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
union
  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

  從邏輯上說,第 10 名到第 15 名就是從原前 15 名,"再"挑出前 5 名不要,保留剩下
  的 5 名。第二種寫法是從前 15 名里挑出不屬于原前 10 名的記錄,把兩個(gè)數(shù)據(jù)集做
  一個(gè)差,因此要多用一個(gè)
  子查詢,效率相對(duì)較低,它,如果要有《ANSI/ISO SQL》的 EXCEPT
  關(guān)鍵字就是最理想的了。

  這種技巧在數(shù)據(jù)"分頁"的應(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é),語文,英語,政治,平均成績(jī) 前面也有類似的題目,做起來
  確實(shí)麻煩,因此下面僅提供參考答案,就不贅述了:

  SELECT  DISTINCT top 5
       [成績(jī)表].[學(xué)生ID],
       [成績(jī)表].[學(xué)生姓名] AS 學(xué)生姓名,
       [T1].[成績(jī)] AS 數(shù)學(xué),
       [T2].[成績(jī)] AS 語文,
       [T3].[成績(jī)] AS 英語,
       [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)

  最后還要多說一句: 一般 TOP 關(guān)鍵字與 ORDER BY 子句合用才有真正意義。

本站僅提供存儲(chǔ)服務(wù),所有內(nèi)容均由用戶發(fā)布,如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請(qǐng)點(diǎn)擊舉報(bào)
打開APP,閱讀全文并永久保存 查看更多類似文章
猜你喜歡
類似文章
巨難的問題.....相信大家都可能會(huì)遇到, 將打豎的數(shù)據(jù)變成打橫的數(shù)據(jù)的問題
關(guān)于面試總結(jié)1-SQL篇
Hive SQL50道練習(xí)題
sql 打橫
記錄一下無聊的數(shù)據(jù)庫作業(yè)
中綴算術(shù)轉(zhuǎn)后綴算術(shù)表達(dá)式并計(jì)算的函數(shù)
更多類似文章 >>
生活服務(wù)
分享 收藏 導(dǎo)長(zhǎng)圖 關(guān)注 下載文章
綁定賬號(hào)成功
后續(xù)可登錄賬號(hào)暢享VIP特權(quán)!
如果VIP功能使用有故障,
可點(diǎn)擊這里聯(lián)系客服!

聯(lián)系客服