求高人。
精简也好。换个思路也好。只要能解决问题。
先谢谢了。
表结构如下:
成绩表:
RID 成绩编号
SID 学生编号
TID 考试编号
Class 班级编号
Subject 科目编号
Score 分数 班级表:
CID 班级编号
Name 班级名称
Grade 年级名称 年级就是高一和高二SELECT [Class],[Subject],应考人数,实考人数,A类,B类,C类,D类,最高分,最低分,班平均,级平均,B值,DENSE_RANK() OVER(PARTITION BY B值 ORDER BY 年级,[Subject] ASC) as B序
FROM (
SELECT [Class],[Subject],(SELECT [Grade] FROM [Classes] WHERE [Name] = '高二一班') AS 年级,
(SELECT COUNT(ID) FROM [Student] AS A WHERE A.[Class] = [Class]) AS 应考人数,
(SELECT COUNT(ID) FROM [Student] AS A WHERE A.[Class] = [Class] AND [Score] > 0 ) AS 实考人数,
(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 30 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS A类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 30 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 60 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS B类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 60 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 90 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS C类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 90 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS D类,(SELECT MAX([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 最高分,
(SELECT MIN([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 最低分,
(SELECT AVG([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 班平均,
(SELECT AVG([Score]) FROM [Results] WHERE [Class] IN (SELECT [Name] FROM [Classes] WHERE [Grade] = '高二' AND [Subject] = '语文' )) AS 级平均,
CONVERT(DECIMAL(4,2),(SELECT AVG([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文')*1.00
/(SELECT AVG([Score]) FROM [Results] WHERE [Class] IN (SELECT [Name] FROM [Classes] WHERE [Grade] = '高二' AND [Subject] = '语文'))*1.00) AS B值
FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
) T
精简也好。换个思路也好。只要能解决问题。
先谢谢了。
表结构如下:
成绩表:
RID 成绩编号
SID 学生编号
TID 考试编号
Class 班级编号
Subject 科目编号
Score 分数 班级表:
CID 班级编号
Name 班级名称
Grade 年级名称 年级就是高一和高二SELECT [Class],[Subject],应考人数,实考人数,A类,B类,C类,D类,最高分,最低分,班平均,级平均,B值,DENSE_RANK() OVER(PARTITION BY B值 ORDER BY 年级,[Subject] ASC) as B序
FROM (
SELECT [Class],[Subject],(SELECT [Grade] FROM [Classes] WHERE [Name] = '高二一班') AS 年级,
(SELECT COUNT(ID) FROM [Student] AS A WHERE A.[Class] = [Class]) AS 应考人数,
(SELECT COUNT(ID) FROM [Student] AS A WHERE A.[Class] = [Class] AND [Score] > 0 ) AS 实考人数,
(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 30 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS A类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 30 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 60 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS B类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 60 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)
AND [Score] >= (SELECT AVG([Score]) FROM (SELECT TOP 90 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS C类,(SELECT COUNT(DISTINCT [SID]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
AND [Score] < (SELECT AVG([Score]) FROM (SELECT TOP 90 PERCENT [Score] FROM [Results] ORDER BY [Score] DESC) T)) AS D类,(SELECT MAX([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 最高分,
(SELECT MIN([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 最低分,
(SELECT AVG([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文') AS 班平均,
(SELECT AVG([Score]) FROM [Results] WHERE [Class] IN (SELECT [Name] FROM [Classes] WHERE [Grade] = '高二' AND [Subject] = '语文' )) AS 级平均,
CONVERT(DECIMAL(4,2),(SELECT AVG([Score]) FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文')*1.00
/(SELECT AVG([Score]) FROM [Results] WHERE [Class] IN (SELECT [Name] FROM [Classes] WHERE [Grade] = '高二' AND [Subject] = '语文'))*1.00) AS B值
FROM [Results] WHERE [Class] = '高二一班' AND [Subject] = '语文'
) T
你好,你认为哪里该改下?@SQL77
你好,不太懂 我数据库很烂的。。@qiqi860819
呵呵,谢谢。
哥,我看好你。。
不符合三范式~
拆分表考试表
Subject 科目编号
TID 考试编号成绩表:
RID 成绩编号
SID 学生编号
Subject 科目编号
Score 分数班级表
CID 班级编号
Name 班级名称
Grade 年级名称
你好,不好意思。我的有考试表。
没贴出来。呵呵。不好意思。我自己试着精简了一下。 还是好长。 唉