少写了一个ANDSELECT
dbo.HW_CLASS.CLASS_NAME AS 班级名称,
dbo.HW_COURSE.COURSE_NAME AS 课程名,
dbo.HW_TEACHER.TEACHER_NAME AS 任课教师,
AVG(dbo.HW_STUDENTGRADE.GRADE_APPRAISE) AS 平均成绩,
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 85
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE <= 100 THEN 1 ELSE 0 END) AS [100 - 85],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 70
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 85 THEN 1 ELSE 0 END) AS [85 - 70],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 60
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 70 THEN 1 ELSE 0 END) AS [70 - 60],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 60 THEN 1 ELSE 0 END) AS [60 -]
dbo.HW_CLASS.CLASS_NAME AS 班级名称,
dbo.HW_COURSE.COURSE_NAME AS 课程名,
dbo.HW_TEACHER.TEACHER_NAME AS 任课教师,
AVG(dbo.HW_STUDENTGRADE.GRADE_APPRAISE) AS 平均成绩,
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 85
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE <= 100 THEN 1 ELSE 0 END) AS [100 - 85],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 70
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 85 THEN 1 ELSE 0 END) AS [85 - 70],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 60
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 70 THEN 1 ELSE 0 END) AS [70 - 60],
COUNT(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 60 THEN 1 ELSE 0 END) AS [60 -]
From tablename,..
Where ??--看sql帮助了解基本语法结构
dbo.HW_COURSE.COURSE_NAME AS 课程名,
dbo.HW_TEACHER.TEACHER_NAME AS 任课教师,
AVG(dbo.HW_STUDENTGRADE.GRADE_APPRAISE) AS 平均成绩,
SUM(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 85
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE <= 100
THEN 1
ELSE 0
END
) AS [100 - 85],
SUM(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 70
AND dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 85
THEN 1
ELSE 0
END
) AS [85 - 70],
SUM(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE >= 60
and dbo.HW_STUDENTGRADE.GRADE_APPRAISE< 70
THEN 1
ELSE 0
END
) AS [70 - 60],
SUM(CASE WHEN dbo.HW_STUDENTGRADE.GRADE_APPRAISE < 60
THEN 1
ELSE 0
END
)AS [60 -]from 表.....
group by ........