8.统计列印各科成绩,各分数段人数:
课程ID,课程名称,[100-85],[85-70],[70-60],[<60]
尽管表面看上去不那么容易,其实用 CASE 可以很容易地实现:
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, 课程名称
注意这里的 BETWEEN,虽然字段名都是从高至低,可 BETWEEN 中还是要从低到高,这里
如果不小心,会犯一个很难发现的逻辑错误: 在数学上,当a > b 时,[a, b]是一个空集。
9.列印学生平均成绩及其名次
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)
这里有很多值得一提的地方,先利用两个完全相同的自相关子查询生成两个派生表作
为基本表用于作小于或等于的连接,这样就可以通过表中小于或等于每个值的其他值
的 COUNT(distinct) 的计数聚集函数来体现名次了。
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.[平均成绩] desc
方法二也使用了两个完全相同的自相关子查询生成两个派生表作为基本表,再利用它
们之间作大于的相关子查询取 COUNT(distinct) + 1 的计数聚集函数同样实现了名
次的显示。
这道题从应用角度来看,查询结果是相当合理的,并列情况的名次也都一样。但如果想
实现类似自动序列的行号,该解决方案的局限性突显,不能处理并列相等的情况了,所
以有必要强调:一定要选择不重复的连接条件,可以根据实际情况利用字段组合的不等
连接 (T1.f1 + ... + T1.fn <= T2.f1 + ... + T2.fn)。继续引申还可以通过判断
COUNT(distinct) % 2 是否为 0 的 HAVING 或 WHERE 子句实现只显示偶数或奇数行:
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 =1
再简单说一下 HAVING 和 WHERE 在含有 GROUP BY 分组的查询中的区别,HAVING 是
在数据分组后才筛选记录的,WHERE 是先进行筛选在分组的,而且 HAVING 一般应与聚
集函数合用才有真正含义。
两种方法再次体现了子查询与连接可以殊途同归之妙,第二种子查询方法值得推荐,因
为比较利于程序构造,便于为没有该功能的原有查询添加此项功能。本题仅仅是为了示
范一种比较新颖的解题思路,回避了效率的问题。
10.列印各科成绩前三名的记录:(不考虑成绩并列情况)
学生ID,学生姓名,课程ID,课程名称,成绩,教师ID,教师姓名
如果仅从成绩考虑前三名的人,利用相关子查询的知识:
SELECT *
FROM 成绩表 t1
WHERE 成绩 IN (SELECT TOP 3 成绩
FROM 成绩表
WHERE t1.课程id = 课程id
ORDER BY 成绩 DESC
)
ORDER BY t1.课程id
这样查询的结果各科成绩前三名的记录数应该大于等于三,因为可能有并列情况,
如果小于三自然是该门课还没有那么多人考试!
如果不考虑并列情况,严格控制各科只列印三条记录,则使用"学生id"构造相关
子查询条件亦可:
SELECT *
FROM 成绩表 t1
WHERE 学生id IN (SELECT TOP 2 学生id
FROM 成绩表
WHERE t1.课程id = 课程id
ORDER BY 成绩 DESC
)
ORDER BY t1.课程id
如果利用第 10 题的思路也可实现该应用。
11.规范化
规范化的问题可以说是仁者见仁,智者见智。而且不做肯定不好,但过犹不及,搞到太
规范也不一定是好事。首先分析信息的对应关系,这个表中有四种信息。学生、课程、教师、成绩。其中前三个可以独立存在,最
后一个可以看做是基于前三个存在的。然后,我们按这四种分类,建立四个表:
关于学生的信息,有以下两个:学生ID,姓名;
教师则会有教师ID,姓名,课程ID 这也就是为什么我要把学生和教师会为两个表的原因;
课程则有课程ID,课程名称两种;
而最后一个成绩信息,就成为了联接它们的一个部分,在这里,它要有学生ID,教师ID,课程ID,成绩四项,相
对与其它表应属应用级别,除了成绩字段,其它都引用的另外的表。
这样一来,几个表的脚本大概是这个样子:
CREATE TABLE "学生信息"
(
"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 "成绩信息"
(
"学生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")
)
这样建表很明显是为了尽可能的细化信息的分类。它的好处在于各种信息分划明确,不
过问题也很明显,比如,一个教师不能同时带两门不同的课(当然,这可能正是业务规则所
要求的),而且,这样做分类过于细腻了。
如果不需要对教师进行人事管理,那么,完全可以把教师信息和课程信息合为一表。也就是说,不同教师带的同
一名称课程,视做不同课程。这样做当然也有其应用背景,很多教师,特别是高等教育和名师,往往有他们自
己的风格,完全可以视做两种课程,相信同样教授 C++ , Lippman 和 Stroustrup 教出的学生总会有所不同。
要说问题,那就是,如果想要限制学生不能重复修某一门课,就得用触发器了,没有太好的办法,不过这个问题,
前面的第一种设计同样解决不了,就算针对教师和课程的关系单建一个表也不一定就可以,还把问题复杂化了。
现在把第二种设计的脚本列出来:
CREATE TABLE "学生信息"
(
"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 "成绩信息"
(
"学生ID" CHAR(4),
"课程ID" CHAR(4),
成绩 NUMERIC(5, 2),
PRIMARY KEY("学生ID", "课程ID"),
FOREIGN KEY("学生ID") REFERENCES "学生信息"("ID"),
FOREIGN KEY("课程ID") REFERENCES "课程信息"("ID")-
)
这样是不是能清爽一点?这样一来,如果不存在一个教师教不同的课程的情况,并且我
们希望简化管理,甚至都可以不用"课程分类"和"教师ID"字段。当然,视业务需要而定,
如果希望在限制学生学习的课程分类的同时,不想带来额外的性能开销,使用第一种设
计,或将课程分类字段也列入成绩信息表,是一个更好的办法。
关于数据库的设计和管理,有几条经验,拿出来在这里和大家交流一下:
对数据进行规范化时,最好要符合它的应用背景。这样易于理解和管理;
数据的规范化不一定是越细化越好,粒度适当地大一点,后面的编程一般会容易一点;
虽说不是越细越好,不过要是不做规范化,却几乎是一定要出问题;
很重要的一点: 千万不要滥用自动标识列! 特别是,不要滥用自动标识列来做为一个表中唯一的约束条件,通常,
那和没有约束没什么不同!
关于这些试题,我们的看法就到这里,希望朋友们可以拿出更多更好的意见,我们一起讨论。
原题含答案:
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
ＳＱＬ综合应用学习<二> （转贴）
80酷酷网 80kuku.com
