这里表面上使用了不等连接,再仔细想想,Ranks 表中每条记录的区间是没有交集的,
  其实也可以认为是等值连接,这样的表设计无疑存在着良好的扩展性,如果题目只要求

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

  select 学生ID,MAX(学生姓名) as 学生姓名,count(*),avg(t0.成绩)
         ,(SELECT max(Rank)
             from Ranks
            where AVG(T0.成绩) >= Ranks.MinValue
                  and AVG(T0.成绩) <= Ranks.MaxValue
           ) AS 平均级别
from T as T0
group by 学生ID

  则这样的解决方案就比较全面了。

  回到原题,再介绍一个比较取巧的办法,仅需一个简单分组查询就可解决问题,有经验的读者可能已经想到了
  ,那就是 CASE:

  SELECT 学生ID, MIN(学生姓名),
         SUM(CASE 课程ID WHEN ''''K1'''' THEN 成绩 ELSE 0 END) AS 数学,
         SUM(CASE 课程ID WHEN ''''K2'''' THEN 成绩 ELSE 0 END) AS 语文,
         SUM(CASE 课程ID WHEN ''''K3'''' THEN 成绩 ELSE 0 END) AS 英语,
         SUM(CASE 课程ID WHEN ''''K4'''' THEN 成绩 ELSE 0 END) AS 政治,
         COUNT(*) AS 有效课程数, AVG(T.成绩) AS 平均成绩
    FROM 成绩表 AS T
GROUP BY 学生ID
ORDER BY 平均成绩 DESC

  虽然可能初看答案感觉有点怪,其实很好理解,可读性并不低,效率也很高。但它不能
  像前一个答案那样,在成绩中区分出某一门课这个学生究竟是缺考 (NULL),还是真得
  零分。这个解法充分利用了 CASE 语句进行数据分类的作用: CASE 将成绩按课程分
  成四类,SUM 用来消去多余的 0。

  SELECT [T].[学生ID],MAX([T].[学生姓名]) AS 学生姓名,
         MAX([T1].[成绩]) AS 数学,
  MAX([T2].[成绩]) AS 语文,
  MAX([T3].[成绩]) AS 英语,
  MAX([T4].[成绩]) AS 政治,
  COUNT([T].[课程ID]) AS 有效课程数,
         (ISNULL(MAX([T1].[成绩]),0) +
   ISNULL(MAX([T2].[成绩]),0) +
   ISNULL(MAX([T3].[成绩]),0) +
   ISNULL(MAX([T4].[成绩]),0)) / COUNT([T].[课程ID]) AS 有效平均分
    FROM 成绩表 T
         LEFT JOIN 成绩表 AS [T1]  ON [T].[学生ID] = [T1].[学生ID] AND [T1].[课程ID] = ''''K1''''
         LEFT JOIN 成绩表 AS [T2]  ON [T].[学生ID] = [T2].[学生ID] AND [T2].[课程ID] = ''''K2''''
         LEFT JOIN 成绩表 AS [T3]  ON [T].[学生ID] = [T3].[学生ID] AND [T3].[课程ID] = ''''K3''''
         LEFT JOIN 成绩表 AS [T4]  ON [T].[学生ID] = [T4].[学生ID] AND [T4].[课程ID] = ''''K4''''
GROUP BY [T].[学生ID]
ORDER BY 有效平均分 DESC

  这个方法是相当正统的联接解法,尽管写起来麻烦了些,但还是不难理解的。再从实用
  角度考虑一下,真实需求往往不是象本题明确提出"列印四门 (数学,语文,英语,政治)
  课程"这样的相对静态的需求,该是动态 SQL 大显身手的时候了,很明显方法一的写法
  无疑是利用程序构造动态 SQL 的最好选择,当然另两个 SQL 规律还是挺明显的,同样
  不难构造。以 CASE 版答案为例: 先用一个游标遍历,取出所有课程凑成:
  SUM(CASE ''''课程ID'''' WHEN ''''课程名称'''' THEN 成绩 ELSE 0 END) AS 课程名称 形式,
  再补上 SELECT 和 FROM、WHERE 等必要条件,一个生成动态成绩单的 SQL 就诞生了,
  只要再由相关程序调用执行即可,这样就可以算一个更完善的解决方案了。

  其实,最类似的典型应用是在主、细关系中的主表投影中实现细表的汇总统计行,
  例如两张表:
   Master(F,f1,f2 ...) 一对多 Details(F,f3,f4 ...) 
  SELECT *
         ,( SELECT COUNT(*)
              FROM Details
             WHERE Master.F = Details.F
          )
         ,( SELECT SUM(F3)
              FROM Details
             WHERE Master.F = Details.F
          )
    FROM Master

4.按各科不平均成绩从低到高和及格率的百分数从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示):
(就是分析哪门课程难)
  课程ID,课程名称,平均成绩,及格百分比
  SELECT 课程ID,MAX(课程名称) AS 课程名称,AVG(成绩) AS 平均成绩
         ,str(100 * SUM(CASE WHEN 成绩 >=60 THEN 1 ELSE 0 END)/COUNT(*))+''''%'''' AS 及格百分比
    FROM 成绩表 T
GROUP BY 课程ID
ORDER BY 及格百分比 DESC

  这道题应该说是算简单的了,就是用"行"来提供表现形式的。只要想明白要对数据如
  何分组,取统计聚集函数,就万事大吉了。

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

  这道题其实就是上一题的"列"表现形式版本,相对于上一题,本题是静态的,因为本题
  同第三题一样利用行上的数据构造了列,要实现扩展必须再利用另外的程序构造动态
  SQL:

  SELECT SUM(CASE WHEN 课程ID = ''''K1'''' THEN 成绩 ELSE 0 END)/SUM(CASE 课程ID WHEN ''''K1'''' THEN 1 ELSE 0 END) AS 数学平均分
         ,100 * SUM(CASE WHEN 课程ID = ''''K1'''' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = ''''K1'''' THEN 1 ELSE 0 END) AS 数学及格百分数
         ,SUM(CASE WHEN 课程ID = ''''K2'''' THEN 成绩 ELSE 0 END)/SUM(CASE 课程ID WHEN ''''K2'''' THEN 1 ELSE 0 END) AS 语文平均分
         ,100 * SUM(CASE WHEN 课程ID = ''''K2'''' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = ''''K2'''' THEN 1 ELSE 0 END) AS 语文及格百分数
         ,SUM(CASE WHEN 课程ID = ''''K3'''' THEN 成绩 ELSE 0 END)/SUM(CASE 课程ID WHEN ''''K3'''' THEN 1 ELSE 0 END) AS 英语平均分
         ,100 * SUM(CASE WHEN 课程ID = ''''K3'''' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = ''''K3'''' THEN 1 ELSE 0 END) AS 英语及格百分数
         ,SUM(CASE WHEN 课程ID = ''''K4'''' THEN 成绩 ELSE 0 END)/SUM(CASE 课程ID WHEN ''''K4'''' THEN 1 ELSE 0 END) AS 政治平均分
         ,100 * SUM(CASE WHEN 课程ID = ''''K4'''' AND 成绩 >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN 课程ID = ''''K4'''' THEN 1 ELSE 0 END) AS 政治及格百分数
   FROM 成绩表 T

  这一句看起来很长,但实际上是最经典的 CASE 运用,很实用的数据分析技术。先将原
  表中的成绩一列连续投影 8 次备用于四门不同课程,充分利用 CASE 和数据的值域
  [''''k1'''',''''k2'''',''''k3'''',''''k4'''']来划分数据,再利用 SUM() [1 + ...+ 1] 实现了看似本来应
  该用 COUNT(*) 的计数器的功能,这里面不要说联接和子查询,甚至连 Group by 分组
  的痕迹都找不到!如果读起来吃力,完全可以先只保留一个字段,相对好理解些,看懂后
  逐一补全。本题也可以算一个"行变列"的交叉透视表示例吧! 另外,"行"相对于"列"
  是动态的,"行"是相对无限的,"列"是相对有限的,"行"的增删是应用级的,可"随意"增
  删,"列"的增删是管理级的,不要轻易变动!

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

  SELECT 教师ID,MAX(教师姓名) AS 教师姓名,课程ID,MAX(课程名称) AS 课程名称,AVG(成绩) AS 平均成绩
    FROM 成绩表 T
GROUP BY 课程ID,教师ID
ORDER BY AVG(成绩) DESC

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

  SELECT 教师ID,MAX(教师姓名),课程ID,MAX(课程名称) AS 课程名称 --,AVG(成绩) AS 平均成绩
         ,(SUM(成绩)
           -(SELECT MAX

上一页  [1] [2] [3] [4]  下一页