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
上一页 [1] [2] [3] [4] [5] [6] |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 