以下试题来自：
单项选择题当前日录下有“学生”表和“成绩”表两个文件，要求查找同时选修了“课程名称”为“计算机”和“英语”的学生姓名，下列SQL语句的空白处应填入的语句为(
SELECT 姓名 FROM 学生，成绩；
WHlERE 学生.学号=成绩.学号；
AND 课程名称="计算机"；
AND 姓名______；
(SELECT 姓名 FROM 学生,成绩；
WHERE 学生.学号=成绩.学号；
AND 课程名称="英语")A．ANYB．EXISTSC．IND．ALL
为您推荐的考试题库
你可能感兴趣的试题
12A．软件测试是证明软件没有错误B．主要目的是发现程序中的错误C．主要目的是确定程序中错误的位置D．测试最好由程序员自己来检查自己的程序3A．单击表单的标题栏，不引发表单的Click事件B．运行表单时，Init事件在Load事件之前引发C．释放表单时，Unload事件在Destory事件之前引发D．上面的说法都不对4A．矩形B．菱形C．椭圆形D．正方形5A．89B．93C．99D．100
热门相关试卷
最新相关试卷当前位置： &
ＳＱＬ综合应用学习
1:25:53　来源: 本站整理
看完测试完下面这些试题，你的ＳＱＬ水平一定会有新的提高。下面我们先看一下题设:二维表 T(F1,F2,F3,F4,F5,F6,F7) 表示如下关系:┌─────┬────┬─────┬─────┬─────┬─────┬─────┐│& 学生ID& │学生姓名│& 课程ID& │ 课程名称 │&& 成绩&& │& 教师ID& │ 教师姓名 │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S3&&& │&& 王五 │&&& K4&&& │&& 政治&& │&&& 53&&& │&&& T4&&& │& 赵老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S1&&& │&& 张三 │&&& K1&&& │&& 数学&& │&&& 61&&& │&&& T1&&& │& 张老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S2&&& │&& 李四 │&&& K3&&& │&& 英语&& │&&& 88&&& │&&& T3&&& │& 李老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S1&&& │&& 张三 │&&& K4&&& │&& 政治&& │&&& 77&&& │&&& T4&&& │& 赵老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S2&&& │&& 李四 │&&& K4&&& │&& 政治&& │&&& 67&&& │&&& T5&&& │& 周老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S3&&& │&& 王五 │&&& K2&&& │&& 语文&& │&&& 90&&& │&&& T2&&& │& 王老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S3&&& │&& 王五 │&&& K1&&& │&& 数学&& │&&& 55&&& │&&& T1&&& │& 张老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S1&&& │&& 张三 │&&& K2&&& │&& 语文&& │&&& 81&&& │&&& T2&&& │& 王老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S4&&& │&& 赵六 │&&& K2&&& │&& 语文&& │&&& 59&&& │&&& T1&&& │& 王老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S1&&& │&& 张三 │&&& K3&&& │&& 英语&& │&&& 37&&& │&&& T3&&& │& 李老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&&& S2&&& │&& 李四 │&&& K1&&& │&& 数学&& │&&& 81&&& │&&& T1&&& │& 张老师& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&& ....&& │&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │├─────┼────┼─────┼─────┼─────┼─────┼─────┤│&& ....&& │&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │&&&&&&&&& │└─────┴────┴─────┴─────┴─────┴─────┴─────┘为便于大家更好的理解,我们将 T 表起名为"成绩表"1.如果 T 表还有一字段 F 数据类型为自动增量整型(唯一,不会重复),& 而且 T 表中含有除 F 字段外,请删除其它字段完全相同的重复多余的脏记录数据:& 本问题就是一个清理"逻辑重复"记录的问题,当然,这种情况完全可以利用主键约束来& 杜绝!然而,现实情况经常是原始数据在"洗涤"后,方可安全使用,而且逻辑主键过早的& 约束,将会给采集原始数据带来不便,例如:从刷卡机上读取考勤记录。到了应用数据& 的时候,脏数据就该被扫地出门了! 之所以题中要保留一个自动标识列,是因为它的确& 是下面答案所必须的前提:& DELETE L&&& FROM "成绩表" L&&&&&&&& JOIN "成绩表" R&&&&&&&&&&&&&&&&&&&&&& ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F & R.F& 这是思路最精巧且最直接有效的方法之一。用不等自联接,正好可以将同一组重复数& 据中 F 字段值最小的那一条留下,并选出其它的删掉,如果只有一条,自然也不会被选& 中了。这里还要强调一下,大家一定要分清楚被操作的基本表也就是 DELETE 关键字& 后的表和过滤条件所使用的由基本表连接而成的二维表数据集,也就是 FROM 子句的& 全部。在自连接的 FROM 子句至少要取一个别名来引用基本表。别名的使用在编写大& 量类似结构的 SQL 时非常方便,而且利于统一程序构造动态 SQL。如有必要加强条件,& 还可继续使用 WHERE 子句。如果上面的例子还不够直观,下面模仿一个不等自联接,& 有一组数 (1,2,3),作一个不等自联接,令左子集大于右子集,是:& 2 1& 3 1& 3 2& 如果现在选出左子集,就是 2 和 3 了。1 在右边没有比它更小的数据可以与之匹配,& 因此被过滤了。如果数据大量重复,效率会差强人意,幸亏不是 SELECT ,而是 DELETE& 无需返回结果集,影响自然小多了。& DELETE T& FROM 成绩表 T& WHERE F NOT IN (SELECT MIN(F)&&&&&&&&&&&&&&&&&&& FROM 成绩表 I &&&&&&&&&&&&&&& GROUP BY I.学生ID,I.课程ID&&&&&&&&&&&&&&&&& HAVING COUNT(*)&1&&&&&&&&&&&&&&&& )&&&&&&& AND F NOT IN (SELECT MIN(F)&&&&&&&&&&&&&&&&&&&&&&& FROM 成绩表 I &&&&&&&&&&&&&&&&&&& GROUP BY I.学生ID, I.课程ID&&&&&&&&&&&&&&&&&&&&& HAVING COUNT(*)=1&&&&&&&&&&&&&&&&&&&& )& 这种方法思路很简单,就像翻译自然语言,很精确地描述了符合条件记录的特性,甚至& 第二个条件的确多余。至少应该用一个 &= 号合并这两个条件或只保留任意一个条件,& 提高效率。& DELETE T&&& FROM 成绩表 T&& WHERE F & (SELECT MIN(F)&&&&&&&&&&&&&&& FROM 成绩表 AS I&&&&&&&&&&&&&& WHERE I.学生ID = T.学生ID &&&&&&&&&&&&&&&&&&&& AND I.课程ID = T.课程ID&&&&&&&&&&& GROUP BY I.学生ID, I.课程ID&&&&&&&&&&&& )& 这种方法,基本上是方法一的相关子查询版本,了解笛卡尔积的读者能会好理解些,而& 且用到了统计函数,因此效率不是太高。细心的读者会发现子查询里的 GROUP BY 子& 句没有必要,去掉它应该会提高一些效率的。& 关于 DELETE 语句的调试,有经验的程序员都会先用无害的 SELECT 暂时代替危险的& DELETE。例如:& SELECT L.*& --DELECT L 暂时注释掉&&& FROM "成绩表" L&&&&&&&& JOIN "成绩表" R&&&&&&&&&&& ON L."学生ID" = R."学生ID" AND L."课程ID" = R."课程ID" AND L.F&R.F& 这样,极大地减小了在线数据被无意破坏的可能性,当然数据提前备份也很重要。同理& UPDATE 和 INSERT 写操作也应照此行事。从数据库原理的关系运算的角度来看 INSERT、& UPDATE 和 DELETE 这些写操作都属于典型的"选择(Selection)"运算,UPDATE 和 INSERT& 而且还是"投影(Projection)"运算,它们都是这些关系运算的"写"应用的表现形式。& 其实,查询的目的也本来无非就是浏览、删除、更& 新或插入。通常写操作也比读操作消耗更大,如果索引过多,只会降低效率。& 选择"子查询"还是"连接"在效率是有差别的,但最关键的差别还是表现在查询的结果& 集的读写性上,开发人员在写一个"只读"应用的查询记录集时,"子查询"和"连接"各自& 的效率就是应该首先考虑的问题,但是如果要实现"可写"应用的查询结果集,则无论是& 相关还是非相关子查询都是在复杂应用中难以避免的。& 以上解决方案中,应该说第一种方法,简洁有效,很有创意,是值得推荐的方法。当然,& 最简单的写法应该是:& DELETE T&&& FROM T,T T1&& WHERE T.学生ID=T1.学生ID and T.课程ID=T.课程ID and T.F & T1.F& 其实这就是方法一的"标准"(但确实实不是《ANSI/ISO SQL》标准)连接写法,以下各& 题答案为了便于读者理解,一般不采用这种写法,这也是《ANSI/ISO SQL》标准所鼓& 励的,JOIN 确实更容易地表达表之间的关系,有兴趣的读者可自行改写。如果使用& "*="实现两表以上的外连接时,要注意此时 WHERE 子句的 AND 条件是有顺序的,尽& 管《ANSI/ISO SQL》标准不允许 WHERE 条件的顺序影响查询结果,但是 FROM 子句& 的各表连接的顺序可以影响查询结果。2.列印各科成绩最高和最低的相关记录: (就是各门课程的最高、最低分的学生和老师)& 课程ID,课程名称, 最高分,学生ID,学生姓名,教师ID,教师姓名,& 最低分,学生ID,学生姓名,教师ID,教师姓名& 如果这道题要是仅仅求出各科成绩最高分或最低分,则是一道非常简单的题了:&SELECT L.课程ID, MAX(L.课程名称), MAX(L.成绩) AS 最高分, MIN(L.成绩) AS 最低分&&& FROM 成绩表 LGROUP BY L.课程ID& & 但是,刁钻的题目却是要列出各科最高和最低成绩的相关记录,这也往往才是真正需求。& 既然已经选出各科最高和最低分,那么,剩下的就是把学生和教师的信息并入这个结果& 集。如果照这样写下去,非常麻烦,因为要添加的字段太多了,很快就使代码变得难于& 管理。还是换个思路吧:& SELECT L.课程ID,L.课程名称,L.[成绩] AS 最高分,L.[学生ID],L.[学生姓名],L.[教师ID],L.[教师姓名]&&&&&&&&&&&&&&&&&&&&&&&&&&& ,R.[成绩] AS 最低分,R.[学生ID],R.[学生姓名],R.[教师ID],R.[教师姓名]&&& FROM 成绩表 L &&&&&&&& JOIN 成绩表 AS R ON L.[课程ID] = R.[课程ID] && WHERE L.[成绩] = (SELECT MAX(IL.[成绩]) &&&&&&&&&&&&&&&&&&&&&& FROM 成绩表 AS [IL] &&&&&&&&&&&&&&&&&&&&& WHERE L.[课程ID] = IL.[课程ID]&&&&&&&&&&&&&&&&&& GROUP BY IL.[课程ID]&&&&&&&&&&&&&&&&&&&& )&&&&&&&& AND&&&&&&&& R.[成绩] = (SELECT MIN(IR.[成绩]) &&&&&&&&&&&&&&&&&&&&&& FROM 成绩表 AS [IR] &&&&&&&&&&&&&&&&&&&&& WHERE R.[课程ID] = IR.[课程ID]&&&&&&&&&&&&&&&&&& GROUP BY IR.[课程ID]&&&&&&&&&&&&&&&&&&&& )& 乍一看答案,好像很复杂,其实如果掌握了构造交叉透视表的基本方法和相关子查询的& 知识,问题迎刃而解。由于最低和最高分都是针对课程信息的,该答案巧妙地把课程信& 息合并到了最高分的数据集中,当然也可以合并到最低分中。代码中规中矩,风格很好,& 可读性也是不错的。3.按平均成绩从高到低顺序,列印所有学生的四门(数学,语文,英语,政治)课程成绩: (就是每个学生的四门课程的成绩单)& 学生ID,学生姓名,数学,语文,英语,政治,有效课程数,有效平均分& (注: 有效课程即在 T 表中有该学生的成绩记录,如不明白可不列印"有效课程数"和"有效平均分")& 需要说明的是: 题目之所以明确提出"四门(数学,语文,英语,政治)课程"是有道理的,& 因为实现时,的确无法避免使原基本表中的行上的数据的值影响列,这又是一个典型的& "行变列"的相关子查询:SELECT 学生ID,MAX(学生姓名) AS 学生姓名,&(SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K1') AS 数学 ,&&&&&&& (SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K2') AS 语文 ,&&&&&&& (SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K3') AS 英语 ,&&&&&&& (SELECT 成绩 FROM 成绩表 WHERE 学生ID=T.学生ID AND 课程ID='K4') AS 政治 ,&&&&&&& COUNT(*) AS 有效课程数, AVG(T.成绩) AS 平均成绩&&& FROM 成绩表 AS TGROUP BY 学生IDORDER BY 平均成绩& 这可以说也是一个很规矩的解法,在这种应用场合,子查询要比联接代码可读性强得多。& 如果数据库引擎认为把它解析成联接更好,那就由它去吧,其实本来相关子查询也肯定含有连接。& 这里再补充一下,在实际应用中如果再加一张表 Ranks(Rank,MinValue,MaxValue):& ┌─────┬─────┬─────┐& │&& Rank&& │ MinValue │ MaxValue │& ├─────┼─────┼─────┤& │&&& A&&&& │&&& 90&&& │&& 100&&& │& ├─────┼─────┼─────┤& │&&& B&&&& │&&& 80&&& │&&& 89&&& │& ├─────┼─────┼─────┤& │&&& C&&&& │&&& 70&&& │&&& 79&&& │& ├─────┼─────┼─────┤& │&&& D&&&& │&&& 60&&& │&&& 69&&& │& ├─────┼─────┼─────┤& │&&& E&&&& │&&&& 0&&& │&&& 59&&& │& └─────┴─────┴─────┘& 就可以实现一个非常有实用价值的应用: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 成绩表 t0group by 学生ID& 这里表面上使用了不等连接,再仔细想想,Ranks 表中每条记录的区间是没有交集的,& 其实也可以认为是等值连接,这样的表设计无疑存在着良好的扩展性,如果题目只要求& 列印(学生ID,学生姓名,有效课程数,有效平均分,平均分级别):& select 学生ID,MAX(学生姓名) as 学生姓名,count(*),avg(t0.成绩)&&&&&&&& ,(SELECT max(Rank) &&&&&&&&&&&& from Ranks&&&&&&&&&&& where AVG(T0.成绩) &= Ranks.MinValue&&&&&&&&&&&&&&&&& and AVG(T0.成绩) &= Ranks.MaxValue&&&&&&&&&& ) AS 平均级别from T as T0group 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 TGROUP BY 学生IDORDER 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 Master4.按各科不平均成绩从低到高和及格率的百分数从高到低顺序,统计并列印各科平均成绩和不及格率的百分数(用"N行"表示): (就是分析哪门课程难)& 课程ID,课程名称,平均成绩,及格百分比& SELECT 课程ID,MAX(课程名称) AS 课程名称,AVG(成绩) AS 平均成绩&&&&&&&& ,str(100 * SUM(CASE WHEN 成绩 &=60 THEN 1 ELSE 0 END)/COUNT(*))+'%' AS 及格百分比&&& FROM 成绩表 TGROUP BY 课程IDORDER 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 成绩表 TGROUP BY 课程ID,教师IDORDER BY AVG(成绩) DESC& 这道题的确没啥好说的,就算闭着眼,不动手,答案也应脱口而出!& 如果平均分按去掉一个最高分和一个最低分后取得,则也不难写出:& SELECT 教师ID,MAX(教师姓名),课程ID,MAX(课程名称) AS 课程名称 --,AVG(成绩) AS 平均成绩&&&&&&&& ,(SUM(成绩)&&&&&&&&&& -(SELECT MAX(成绩) &&&&&&&&&&&&&& FROM 成绩表 &&&&&&&&&&&&& WHERE 课程ID= T1.课程ID AND 教师ID = T1.教师ID)&&&&&&&&&& -(SELECT MIN(成绩) &&&&&&&&&&&&&& FROM 成绩表 &&&&&&&&&&&&& WHERE 课程ID= T1.课程ID and 教师ID = T1.教师ID))&&&&&&&&& / CAST((SELECT COUNT(*) -2&&&&&&&&&&&&&&&&&&& FROM 成绩表 &&&&&&&&&&&&&&&&&& WHERE 课程ID= T1.课程ID AND 教师ID = T1.教师ID) AS FLOAT) AS 平均分FROM 成绩表 AS T1WHERE (SELECT COUNT(*) -2&&&&&&&& FROM 成绩表 &&&&&&& WHERE 课程ID = T1.课程ID AND 教师ID = T1.教师ID) &0GROUP BY 课程ID,教师IDORDER BY 平均分 DESC********************************************************************************************7.列印数学成绩第 10 名到第 15 名的学生成绩单& 或列印平均成绩第 10 名到第 15 名的学生成绩单& [学生ID],[学生姓名],数学,语文,英语,政治,平均成绩& 如果只考虑一门课程,如:数学成绩,非常简单:& select Top 5 *&& from T& where 课程id ='K1' &&&&&&& and 成绩 not in(select top 15 成绩&&&&&&&&&&&&&&&&&&&&&&&&& from T&&&&&&&&&&&&&&&&&&&&& order by 成绩 desc&&&&&&&&&&&&&&&&&&&&&& )order by 成绩 descunion& select *&&& from T&& where 课程id ='K1' &&&&&&&& and 成绩 not in(select top 10 成绩&&&&&&&&&&&&&&&&&&&&&&&&&& from T&&&&&&&&&&&&&&&&&&&&&& order by 成绩 desc&&&&&&&&&&&&&&&&&&&&&&&& )&&&&&&&& and 成绩 in(select top 15 成绩&&&&&&&&&&&&&&&&&&&&&& from T&&&&&&&&&&&&&&&&&& order by 成绩 desc&&&&&&&&&&&&&&&&&&& )order by 成绩 desc& 从逻辑上说,第 10 名到第 15 名就是从原前 15 名,"再"挑出前 5 名不要,保留剩下& 的 5 名。第二种写法是从前 15 名里挑出不属于原前 10 名的记录,把两个数据集做& 一个差,因此要多用一个& 子查询,效率相对较低,它,如果要有《ANSI/ISO SQL》的 EXCEPT& 关键字就是最理想的了。& 这种技巧在数据"分页"的应用中经常利用,只要遵循如下原则即可:&& SELECT Top @PageSize *&&&& FROM T&&& WHERE SortField NOT IN (SELECT TOP @PageSize * @Pagei SortField&&&&&&&&&&&&&&&&&&&&&&&&&&&&& FROM T&&&&&&&&&&&&&&&&&&&&&&&&& ORDER BY SortField&&&&&&&&&&&&&&&&&&&&&&&&&&& )&ORDER BY SortField& 至此,该题考察的主要目的已经达到。至于列印明晰成绩单:& [学生ID],[学生姓名],数学,语文,英语,政治,平均成绩 前面也有类似的题目,做起来& 确实麻烦,因此下面仅提供参考答案,就不赘述了:& SELECT& DISTINCT top 5&&&&&& [成绩表].[学生ID],&&&&&& [成绩表].[学生姓名] AS 学生姓名,&&&&&& [T1].[成绩] AS 数学,&&&&&& [T2].[成绩] AS 语文,&&&&&& [T3].[成绩] AS 英语,&&&&&& [T4].[成绩] AS 政治,&&&&&& ISNULL([T1].[成绩],0) + ISNULL([T2].[成绩],0) + ISNULL([T3].[成绩],0) + ISNULL([T4].[成绩],0) as 总分&& FROM [成绩表] &&&&&&&&&&&& LEFT JOIN [成绩表] AS [T1] &&&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T1].[学生ID] AND [T1].[课程ID] = 'k1' &&&&&&&&&&&& LEFT JOIN [成绩表] AS [T2] &&&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T2].[学生ID] AND [T2].[课程ID] = 'k2' &&&&&&&&&&&& LEFT JOIN [成绩表] AS [T3] &&&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T3].[学生ID] AND [T3].[课程ID] = 'k3' &&&&&&&&&&&& LEFT JOIN [成绩表] AS [T4] &&&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T4].[学生ID] AND [T4].[课程ID] = 'k4' WHERE ISNULL([T1].[成绩],0) + ISNULL([T2].[成绩],0) + ISNULL([T3].[成绩],0) + ISNULL([T4].[成绩],0)&&&&& NOT IN &&&&& (SELECT &&&&&&&&&&&& DISTINCT&&&&&&&&&&&& TOP 15 WITH TIES&&&&&&&&&&&& ISNULL([T1].[成绩],0) + ISNULL([T2].[成绩],0) + ISNULL([T3].[成绩],0) + ISNULL([T4].[成绩],0)&&&&&& FROM [成绩表] &&&&&&&&&&& LEFT JOIN [成绩表] AS [T1] &&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T1].[学生ID] AND [T1].[课程ID] = 'k1' &&&&&&&&&&& LEFT JOIN [成绩表] AS [T2] &&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T2].[学生ID] AND [T2].[课程ID] = 'k2' &&&&&&&&&&& LEFT JOIN [成绩表] AS [T3] &&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T3].[学生ID] AND [T3].[课程ID] = 'k3' &&&&&&&&&&& LEFT JOIN [成绩表] AS [T4] &&&&&&&&&&&&&&&&&&&&& ON [成绩表].[学生ID] = [T4].[学生ID] AND [T4].[课程ID] = 'k4' &&&&&& ORDER BY ISNULL([T1].[成绩],0) + ISNULL([T2].[成绩],0) + ISNULL([T3].[成绩],0) + ISNULL([T4].[成绩],0) DESC)& 最后还要多说一句: 一般 TOP 关键字与 ORDER BY 子句合用才有真正意义。
相关 ＳＱＬ综合应用学习 的文章：
您还没登陆呢，点这里共有 & 位网友发表了看法&&}