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