(成绩)
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
********************************************************************************************
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 成绩 desc
union
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 子句合用才有真正意义。
上一页 [1] [2] [3] [4] |
