一道褒贬不一的SQL测验题[MSSQL防范]

《一道褒贬不一的 SQL 测验题》
playyuer 命题,playyuer、ccat 撰稿www.iTbulo.comw4l5r

相信有不少网友近来在网上见过一则名为《一道褒贬不一的 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 表起名为"成就表"www.iTbulo.comw4l5r

1.假如 T 表还有一字段 F 数据范例为自动增量整型(唯一,不会反复),
  并且 T 表中含有除 F 字段外,请删除别的字段完好相同的反复多余的脏记录数据:www.iTbulo.comw4l5r

  本问题就是一个清理"逻辑反复"记录的问题,当然,这种情形完好可以操纵主键约束来
  根绝!但是,实际情形常常是原始数据在"洗涤"后,方可安全利用,并且逻辑主键过早的
  约束,将会给采集原始数据带来不便,比方:从刷卡机上读取考勤记录.到了利用数据
  的时刻,脏数据就该被扫地出门了! 之所以题中要保存一个自动标识列,是因为它的确
  是下面答案所必须的前提:www.iTbulo.comw4l5r

  DELETE L
    FROM "成就表" L
         JOIN "成就表" R
                       ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F > R.Fwww.iTbulo.comw4l5r

  这是思绪最精良且最直接有效的办法之一.用不等自联接,恰好可以将同一组反复数
  据中 F 字段值最小的那一条留下,并选出别的的删掉,假如只有一条,自然也不会被选
  中了.这里还要夸大一下,大家一定要分清楚被操作的基本表也就是 DELETE 关键字
  后的表和过滤条件所利用的由基本表衔接而成的二维表数据集,也就是 FROM 子句的
  全部.在自衔接的 FROM 子句至少要取一个体名来引用基本表.别名的利用在编写大
  量近似构造的 SQL 时非常便利,并且利于统一程序构造动态 SQL.若有必要加强条件,
  还可持续利用 WHERE 子句.假如上面的例子还不够直观,下面模拟一个不等自联接,
  有一组数 (1,2,3),作一个不等自联接,令左子集大于右子集,是:
  2 1
  3 1
  3 2
  假如目前选出左子集,就是 2 和 3 了.1 在右边没有比它更小的数据可以与之匹配,
  因此被过滤了.假如数据大量反复,效率会差强人意,好在不是 SELECT ,而是 DELETE
  无需返回后果集,影响自然小多了.www.iTbulo.comw4l5r

  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
                     )www.iTbulo.comw4l5r

  这种办法思绪很简单,就像翻译自然语言,很切确地描写了符合条件记录的特点,乃至
  第二个条件的确多余.至少应当用一个 >= 号归并这两个条件或只保存肆意一个条件,
  提高效率.www.iTbulo.comw4l5r

  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
             )www.iTbulo.comw4l5r

  这种办法,基本上是办法一的相关子查询版本,理解笛卡尔积的读者能会好理解些,而
  且用到了统计函数,因此效率不是太高.细心的读者会发现子查询里的 GROUP BY 子
  句没有必要,去掉它应当会提高一些效率的.www.iTbulo.comw4l5r

  关于 DELETE 语句的调试,有经验的程序员城市先用无害的 SELECT 暂时替换危险的
  DELETE.比方:www.iTbulo.comw4l5r

  SELECT L.*
  --DELECT L 暂时注释掉
    FROM "成就表" L
         JOIN "成就表" R
            ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F>R.Fwww.iTbulo.comw4l5r

  这样,极大地减小了在线数据被无意破坏的大概性,当然数据提早备份也很重要.同理
  UPDATE 和 INSERT 写操作也应照此行事.从数据库原理的关系运算的角度来看 INSERT、
  UPDATE 和 DELETE 这些写操作都属于典型的"挑选(Selection)"运算,UPDATE 和 INSERT
  并且还是"投影(Projection)"运算,它们都是这些关系运算的"写"利用的表现情势.
  其实,查询的目的也本来无非就是浏览、删除、更
  新或插入.普通写操作也比读操作损耗更大,假如索引过量,只会降低效率.www.iTbulo.comw4l5r

  挑选"子查询"还是"衔接"在效率是有差别的,但最关键的差别还是表目前查询的后果
  集的读写性上,开辟人员在写一个"只读"利用的查询记录集时,"子查询"和"衔接"各自
  的效率就是应当首先考虑的问题,但是假如要实现"可写"利用的查询后果集,则无论是
  相关还是非相关子查询都是在复杂利用中难以避免的.www.iTbulo.comw4l5r

  以上办理筹划中,应当说第一种办法,简便有效,很有创意,是值得举荐的办法.当然,
  最简单的写法应当是:www.iTbulo.comw4l5r

  DELETE T
    FROM T,T T1
   WHERE T.学生ID=T1.学生ID and T.课程ID=T.课程ID and T.F < T1.Fwww.iTbulo.comw4l5r

  其实这就是办法一的"尺度"(但确切实不是《ANSI/ISO SQL》尺度)衔接写法,以下各
  题答案为了便于读者理解,普通不采取这种写法,这也是《ANSI/ISO SQL》尺度所鼓
  励的,JOIN 确切更简单地表达表之间的关系,有爱好的读者可自行改写.假如利用
  "*="实现两表以上的外衔接时,要注意此时 WHERE 子句的 AND 条件是有次序的,尽
  管《ANSI/ISO SQL》尺度不答应 WHERE 条件的次序影响查询后果,但是 FROM 子句
  的各表衔接的次序可以影响查询后果.www.iTbulo.comw4l5r

2.列印各科成就最高和最低的相关记录: (就是各门课程的最高、最低分的学生和老师)
  课程ID,课程名称,最高分,学生ID,学生姓名,教师ID,教师姓名,最低分,学生ID,学生姓名,教师ID,教师姓名www.iTbulo.comw4l5r

  假如这道题如果仅仅求出各科成就最高分或最低分,则是一道非常简单的题了:www.iTbulo.comw4l5r

  SELECT L.课程ID, MAX(L.课程名称), MAX(L.成就) AS 最高分, MIN(L.成就) AS 最低分
    FROM 成就表 L
GROUP BY L.课程ID
 
  但是,刁钻的标题倒是要列出各科最高和最低成就的相关记录,这也常常才是真正需求.
  既然已经选出各科最高和最低分,那么,剩下的就是把学生和教师的信息并入这个后果
  集.假如照这样写下去,非常麻烦,因为要增添的字段太多了,很快就使代码变得难于
  管理.还是换个思绪吧:www.iTbulo.comw4l5r

  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]
                     )www.iTbulo.comw4l5r

  乍一看答案,仿佛很复杂,其实假如掌握了构造穿插透视表的基本办法和相关子查询的
  知识,问题迎刃而解.由于最低和最高分都是针对课程信息的,该答案巧妙地把课程信
  息归并到了最高分的数据集合,当然也可以归并到最低分中.代码中规中矩,气势很好,
  可读性也是不错的.www.iTbulo.comw4l5r

3.按平均成就从高到低次序,列印全部学生的四门(数学,语文,英语,政治)课程成就: (就是每个学生的四门课程的成就单)
  学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分
  (注: 有效课程即在 T 表中有该学生的成就记录,如不懂得可不列印"有效课程数"和"有效平均分")www.iTbulo.comw4l5r

  需求阐明的是: 标题之所以明确提出"四门(数学,语文,英语,政治)课程"是有原理的,
  因为实现时,的确无法避免使原基本表中的行上的数据的值影响列,这又是一个典型的
  "行变列"的相关子查询:www.iTbulo.comw4l5r

  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 平均成就www.iTbulo.comw4l5r

  这可以说也是一个很法则的解法,在这种利用场所,子查询要比联接代码可读性强得多.
  假如数据库引擎认
  为把它解析成联接更好,那就由它去吧,其实本来相关子查询也必定含有衔接.这里再增补一下,在实际利用
  中假如再加一张表 Ranks(Rank,MinValue,MaxValue):
  ┌──────────┬──────────┬──────────┐
  │   Rank   │ MinValue │ MaxValue │
  ├──────────┼──────────┼──────────┤
  │    A     │    90    │   100    │
  ├──────────┼──────────┼──────────┤
  │    B     │    89    │    80    │
  ├──────────┼──────────┼──────────┤
  │    C     │    79    │    70    │
  ├──────────┼──────────┼──────────┤
  │    D     │    69    │    60    │
  ├──────────┼──────────┼──────────┤
  │    E     │    60    │    0     │
  └──────────┴──────────┴──────────┘www.iTbulo.comw4l5r

  便可以实现一个非常有实用代价的利用:www.iTbulo.comw4l5r

  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 学生IDwww.iTbulo.comw4l5r

  这里表面上利用了不等衔接,再细心想想,Ranks 表中每条记录的区间是没有交集的,
  其实也可以认为是等值衔接,这样的表计划无疑存在着杰出的扩大性,假如标题只要求www.iTbulo.comw4l5r

  列印(学生ID,学生姓名,有效课程数,有效平均分,平均分级别):www.iTbulo.comw4l5r

  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 学生IDwww.iTbulo.comw4l5r

  则这样的办理筹划就对比全面了.www.iTbulo.comw4l5r

  回到原题,再介绍一个对比取巧的办法,仅需一个简单分组查询便可办理问题,有经验的读者大概已经想到了
  ,那就是 CASE:www.iTbulo.comw4l5r

  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 平均成就 DESCwww.iTbulo.comw4l5r

  固然大概初看答案感受有点怪,其实很好理解,可读性并不低,效率也很高.但它不能
  像前一个答案那样,在成就中辨别出某一门课这个学生毕竟是缺考 (NULL),还是真得
  零分.这个解法充分操纵了 CASE 语句举行数据分类的作用: CASE 将成就按课程分
  成四类,SUM 用来消去多余的 0.www.iTbulo.comw4l5r

  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 有效平均分 DESCwww.iTbulo.comw4l5r

  这个办法是相当正统的联接解法,固然写起来麻烦了些,但还是不难理解的.再从实用
  角度考虑一下,真实需求常常不是象本题明确提出"列印四门 (数学,语文,英语,政治)
  课程"这样的相对静态的需求,该是动态 SQL 大显神通的时刻了,很明显办法一的写法
  无疑是操纵程序构造动态 SQL 的最好挑选,当然另两个 SQL 规律还是挺明显的,一样
  不难构造.以 CASE 版答案为例: 先用一个游标遍历,取出全部课程凑成:
  SUM(CASE '课程ID' WHEN '课程名称' THEN 成就 ELSE 0 END) AS 课程名称 情势,
  再补上 SELECT 和 FROM、WHERE 等必要条件,一个生成动态成就单的 SQL 就诞生了,
  只要再由相关程序调用履行便可,这样便可以算一个更完善的办理筹划了.www.iTbulo.comw4l5r

  其实,近来似的典型利用是在主、细关系中的主表投影中实现细表的汇总统计行,
  比方两张表:
   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 Masterwww.iTbulo.comw4l5r

4.按各科不平均成就从低到高和合格率的百分数从高到低次序,统计并列印各科平均成就和不合格率的百分数(用"N行"表示): (就是解析哪门课程难)
  课程ID,课程名称,平均成就,合格百分比www.iTbulo.comw4l5r

  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 合格百分比 DESCwww.iTbulo.comw4l5r

  这道题应当说是算简单的了,就是用"行"来供应表现情势的.只要想懂得要对数据如
  何分组,取统计堆积函数,就万事大吉了.www.iTbulo.comw4l5r

5.列印四门课程平均成就和合格率的百分数(用"1行4列"表示): (就是解析哪门课程难)
  数学平均分,数学合格百分数,语文平均分,语文合格百分数,英语平均分,英语合格百分数,政治平均分,政治合格百分数www.iTbulo.comw4l5r

  这道题其实就是上一题的"列"表现情势版本,相关于上一题,本题是静态的,因为本题
  同第三题一样操纵行上的数据构造了列,要实现扩大必须再操纵别的的程序构造动态
  SQL:www.iTbulo.comw4l5r

  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 成就表 Twww.iTbulo.comw4l5r

  这一句看起来很长,但实际上是最经典的 CASE 应用,很实用的数据解析技术.先将原
  表中的成就一列持续投影 8 次备用于四门差别课程,充分操纵 CASE 和数据的值域
  ['k1','k2','k3','k4']来划分数据,再操纵 SUM() [1 + ...+ 1] 实现了看似本来应
  该用 COUNT(*) 的计数器的功效,这里面不要说联接和子查询,乃至连 Group by 分组
  的痕迹都找不到!假如读起来吃力,完好可以先只保存一个字段,相对好理解些,看懂后
  一一补全.本题也可以算一个"行变列"的穿插透视表示例吧! 别的,"行"相关于"列"
  是动态的,"行"是相对无限的,"列"是相对有限的,"行"的增删是利用级的,可"随便"增
  删,"列"的增删是管理级的,不要简单变更!www.iTbulo.comw4l5r

6.按差别老师所教差别课程平均分从高到低列印: (就是解析哪个老师的哪个课程水平高)
  教师ID,教师姓名,课程ID,课程名称,平均分www.iTbulo.comw4l5r

  SELECT 教师ID,MAX(教师姓名) AS 教师姓名,课程ID,MAX(课程名称) AS 课程名称,AVG(成就) AS 平均成就
    FROM 成就表 T
GROUP BY 课程ID,教师ID
ORDER BY AVG(成就) DESCwww.iTbulo.comw4l5r

  这道题的确没啥好说的,就算闭着眼,不着手,答案也应脱口而出!
  假如平均分按去掉一个最高分和一个最低分后获得,则也不难写出:www.iTbulo.comw4l5r

  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 平均分 DESCwww.iTbulo.comw4l5r

7.列印数学成就第 10 名到第 15 名的学生成就单
  或列印平均成就第 10 名到第 15 名的学生成就单
  [学生ID],[学生姓名],数学,语文,英语,政治,平均成就www.iTbulo.comw4l5r

  假如只考虑一门课程,如:数学成就,非常简单:
  select Top 5 *
   from T
  where 课程id ='K1'
        and 成就 not in(select top 15 成就
                          from T
                      order by 成就 desc
                       )
order by 成就 descwww.iTbulo.comw4l5r

  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 成就 descwww.iTbulo.comw4l5r

  从逻辑上说,第 10 名到第 15 名就是从原前 15 名,"再"挑出前 5 名不要,保存剩下
  的 5 名.第二种写法是早年 15 名里挑出不属于原前 10 名的记录,把两个数据集做
  一个差,因此要多用一个
  子查询,效率相对较低,它,假如要有《ANSI/ISO SQL》的 EXCEPT
  关键字就是最抱负的了.www.iTbulo.comw4l5r

  这种本领在数据"分页"的利用中常常操纵,只要遵守以下原则便可:www.iTbulo.comw4l5r

   SELECT Top @PageSize *
     FROM T
    WHERE SortField NOT IN (SELECT TOP @PageSize * @Pagei SortField
                              FROM T
                          ORDER BY SortField
                            )
 ORDER BY SortFieldwww.iTbulo.comw4l5r

  至此,该题观察的主要目的已经到达.至于列印明晰成就单:
  [学生ID],[学生姓名],数学,语文,英语,政治,平均成就 前面也有近似的标题,做起来
  确切麻烦,因此下面仅供应参考答案,就不赘述了:www.iTbulo.comw4l5r

  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)www.iTbulo.comw4l5r

  最后还要多说一句: 普通 TOP 关键字与 ORDER BY 子句实用才有真正意义.www.iTbulo.comw4l5r

8.统计列印各科成就,各分数段人数:
  课程ID,课程名称,[100-85],[85-70],[70-60],[<60]www.iTbulo.comw4l5r

  固然表面看上去不那么简单,其实用 CASE 可以很简单地实现:www.iTbulo.comw4l5r

  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, 课程名称www.iTbulo.comw4l5r

  注意这里的 BETWEEN,固然字段名都是从高至低,可 BETWEEN 中还是要从低到高,这里
  假如不当心,会犯一个很难发现的逻辑错误: 在数学上,当a > b 时,[a, b]是一个空集.www.iTbulo.comw4l5r

9.列印学平生均成就及其名次www.iTbulo.comw4l5r

  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)www.iTbulo.comw4l5r

  这里有很多值得一提的地方,先操纵两个完好相同的自相关子查询生成两个派生表作
  为基本表用于作小于或等于的衔接,这样便可以通过表中小于或等于每个值的其他值
  的 COUNT(distinct) 的计数堆积函数来表现名次了.www.iTbulo.comw4l5r

  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.[平均成就] descwww.iTbulo.comw4l5r

  办法二也利用了两个完好相同的自相关子查询生成两个派生表作为基本表,再操纵它
  们之间作大于的相关子查询取 COUNT(distinct) + 1 的计数堆积函数一样实现了名
  次的显示.www.iTbulo.comw4l5r

  这道题从利用角度来看,查询后果是相当公道的,并列情形的名次也都一样.但假如想
  实现近似自动序列的行号,该办理筹划的范围性突显,不能处理并列相等的情形了,所
  以有必要夸大:一定要挑选不反复的衔接条件,可以按照实际情形操纵字段组合的不等
  衔接 (T1.f1 + ... + T1.fn <= T2.f1 + ... + T2.fn).持续引伸还可以通过判断
  COUNT(distinct) % 2 能否为 0 的 HAVING 或 WHERE 子句实现只显示偶数或奇数行:www.iTbulo.comw4l5r

  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 =1www.iTbulo.comw4l5r

  再简单说一下 HAVING 和 WHERE 在含有 GROUP BY 分组的查询中的辨别,HAVING 是
  在数据分组后才挑选记录的,WHERE 是先举行挑选在分组的,并且 HAVING 普通应与聚
  集函数实用才有真正含义.www.iTbulo.comw4l5r

  两种办法再次表现了子查询与衔接可以殊途同归之妙,第二种子查询办法值得举荐,因
  为对比利于程序构造,便于为没有该功效的原有查询增添此项功效.本题仅仅是为了示
  范一种对比新颖的解题思绪,躲避了效率的问题.www.iTbulo.comw4l5r

10.列印各科成就前三名的记录:(不考虑成就并列情形)
  学生ID,学生姓名,课程ID,课程名称,成就,教师ID,教师姓名www.iTbulo.comw4l5r

  假如仅从成就考虑前三名的人,操纵相关子查询的知识:www.iTbulo.comw4l5r

 SELECT *
   FROM 成就表 t1
  WHERE 成就 IN (SELECT TOP 3 成就
               FROM 成就表
               WHERE t1.课程id = 课程id
            ORDER BY 成就 DESC
              )
ORDER BY t1.课程idwww.iTbulo.comw4l5r

  这样查询的后果各科成就前三名的记录数应当大于等于三,因为大概有并列情形,
  假如小于三自然是该门课还没有那么多人测验!
  假如不考虑并列情形,严峻掌握各科只列印三条记录,则利用"学生id"构造相关
  子查询条件亦可:www.iTbulo.comw4l5r

  SELECT *
    FROM 成就表 t1
   WHERE 学生id IN (SELECT TOP 2 学生id
                      FROM 成就表
                     WHERE t1.课程id = 课程id
                  ORDER BY 成就 DESC
                    )
ORDER BY t1.课程idwww.iTbulo.comw4l5r

  假如操纵第 10 题的思绪也可实现该利用.www.iTbulo.comw4l5r

11.标准化
   标准化的问题可以说是仁者见仁,智者见智.并且不做必定不好,但过犹不及,搞到太
   标准也不一定是功德.首先解析信息的对应关系,这个表中有四种信息.学生、课程、教师、成就.此中前三个可以独立存在,最
   后一个可以看做是基于前三个存在的.然后,我们按这四种分类,成立四个表:
   关于学生的信息,有以下两个:学生ID,姓名;
   教师则会有教师ID,姓名,课程ID 这也就是为什么我要把学生和教师会为两个表的缘由;
   课程则有课程ID,课程名称两种;
   而最后一个成就信息,就成为了联接它们的一个部份,在这里,它要有学生ID,教师ID,课程ID,成就四项,相
   对与别的表应属利用级别,除了成就字段,别的都引用的别的的表.
   这样一来,几个表的脚本大约是这个模样:
   CREATE TABLE "学生信息"
                (
                 "ID" CHAR(4),
                 "姓名" CHAR(16),
                 PRIMARY KEY ("ID")
                )www.iTbulo.comw4l5r

   CREATE TABLE "课程信息"
                (
                 "ID" CHAR(4),
                 "名称" CHAR(16),
                 PRIMARY KEY ("ID"),
                )www.iTbulo.comw4l5r

  CREATE TABLE "教师信息"
               (
                "ID" CHAR(4),
                "姓名" CHAR(16),
                "课程ID" CHAR(4),
                PRIMARY KEY ("ID"),
                FOREIGN KEY("课程ID") REFERENCES "课程信息"("ID")
               )www.iTbulo.comw4l5r

  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")
               )www.iTbulo.comw4l5r

这样建表很明显是为了尽大概的细化信息的分类.它的好处在于各种信息分划明确,不
干涉题也很明显,比方,一个教师不能同时带两门差别的课(当然,这大概恰是业务法则所
要求的),并且,这样做分类过于精致了.www.iTbulo.comw4l5r

假如不需求对教师举行人事管理,那么,完好可以把教师信息和课程信息合为一表.也就是说,差别教师带的同
一名称课程,视做差别课程.这样做当然也有其利用后台,很多教师,分外是高档教诲和名师,常常有他们自
己的气势,完好可以视做两种课程,相信一样传授 C++ , Lippman 和 Stroustrup 教出的学生总会有所差别.
要说问题,那就是,假如想要限制学生不能反复修某一门课,就得用触发器了,没有太好的办法,不过这个问题,
前面的第一种计划一样办理不了,就算针对教师和课程的关系单建一个表也不一定便可以,还把问题复杂化了.
目前把第二种计划的脚本列出来:www.iTbulo.comw4l5r

  CREATE TABLE "学生信息"
               (
                "ID" CHAR(4),
                "姓名" CHAR(16),
                PRIMARY KEY ("ID")
               )www.iTbulo.comw4l5r

  CREATE TABLE "课程信息"
               (
                "ID" CHAR(4),
                "课程分类" CHAR(4),
                "名称 "CHAR(16),
                "教师ID" CHAR(4),
                "教师姓名" CHAR(16),
                PRIMARY KEY ("ID")
               )www.iTbulo.comw4l5r

  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")-
               )www.iTbulo.comw4l5r

  这样是不是能清爽一点?这样一来,假如不存在一个教师教差别的课程的情形,并且我
  们但愿简化管理,乃至都可以不用"课程分类"和"教师ID"字段.当然,视业务需求而定,
  假如但愿在限制学生学习的课程分类的同时,不想带来额外的性能开销,利用第一种设
  计,或将课程分类字段也列入成就信息表,是一个更好的办法.www.iTbulo.comw4l5r

  关于数据库的计划和管理,有几条经验,拿出来在这里和大家交流一下:
  对数据举行标准化时,最好要符合它的利用后台.这样易于理解和管理;
  数据的标准化不一定是越细化越好,粒度适本地大一点,背面的编程普通会简单一点;
  虽说不是越细越好,不过如果不做标准化,却几近是一定要出问题;
  很重要的一点: 千万不要滥用自动标识列! 分外是,不要滥用自动标识列来做为一个表中唯一的约束条件,普通,
  那和没有约束没什么差别!www.iTbulo.comw4l5r

  关于这些试题,我们的见解就到这里,但愿朋友们可以拿出更多更好的看法,我们一同谈论.www.iTbulo.comw4l5r

原题含答案:
CREATE TABLE [T] (
[ID] [int] IDENTITY (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 sql server) 或 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 www.iTbulo.comw4l5r