统计某班级，某课程的考试情况，要求显示出不同分值段（10分为一个分值段）的学生人数，如（0~10-2人；11~20-7人…）

Question

CREATE PROCEDURE Stu_score
@class nchar(4),
@cname nvarchar(4)
AS 
SELECT 
Score=
CASE
WHEN Score>=0 AND Score<10 THEN '0~9'
WHEN Score>=10 AND Score<20 THEN '10~19'
WHEN Score>=20 AND Score<30 THEN '20~29'
WHEN Score>=30 AND Score<40 THEN '30~39'
WHEN Score>=40 AND Score<50 THEN '40~49'
WHEN Score>=50 AND Score<60 THEN '50~59'
WHEN Score>=60 AND Score<70 THEN '60~69'
WHEN Score>=70 AND Score<80 THEN '70~79'
WHEN Score>=80 AND Score<90 THEN '80~89'
WHEN Score>=90 AND Score<=100 THEN '90~100'
END,
COUNT(*) Number
FROM Reports LEFT JOIN Courses ON
Courses.Cno=Reports.Cno 
WHERE @class=Tclass AND @cname=Cname
GROUP BY Score
这样写了只是会输出有成绩的那个分段。比如reports表中只有33，60，90.那就只有30~39，60~69，90~100。怎样才可以让其他的分段也显示出来，人数显示为0

Answer 1

做一个小表(with as 定义就可以)，再left join 你这个结果集。

Answer 2

是这样的结果吗？;WITH Reports(Cno,Score) AS (
SELECT 'A',20
),Courses(Cno) AS (
SELECT 'A'
)SELECT 
Score=
CASE
WHEN sv.number>=0 AND sv.number<10 THEN '0~9'
WHEN sv.number>=10 AND sv.number<20 THEN '10~19'
WHEN sv.number>=20 AND sv.number<30 THEN '20~29'
WHEN sv.number>=30 AND sv.number<40 THEN '30~39'
WHEN sv.number>=40 AND sv.number<50 THEN '40~49'
WHEN sv.number>=50 AND sv.number<60 THEN '50~59'
WHEN sv.number>=60 AND sv.number<70 THEN '60~69'
WHEN sv.number>=70 AND sv.number<80 THEN '70~79'
WHEN sv.number>=80 AND sv.number<90 THEN '80~89'
WHEN sv.number>=90 AND sv.number<=100 THEN '90~100'
END,
COUNT(Reports.Cno) Number
FROM Courses INNER JOIN [master].dbo.spt_values AS sv ON sv.[type]='P' AND sv.number BETWEEN 1 AND 100 
LEFT JOIN Reports ON Courses.Cno=Reports.Cno AND Reports.Score=sv.number
--WHERE @class=Tclass AND @cname=Cname
GROUP BY Courses.Cno,CASE
WHEN sv.number>=0 AND sv.number<10 THEN '0~9'
WHEN sv.number>=10 AND sv.number<20 THEN '10~19'
WHEN sv.number>=20 AND sv.number<30 THEN '20~29'
WHEN sv.number>=30 AND sv.number<40 THEN '30~39'
WHEN sv.number>=40 AND sv.number<50 THEN '40~49'
WHEN sv.number>=50 AND sv.number<60 THEN '50~59'
WHEN sv.number>=60 AND sv.number<70 THEN '60~69'
WHEN sv.number>=70 AND sv.number<80 THEN '70~79'
WHEN sv.number>=80 AND sv.number<90 THEN '80~89'
WHEN sv.number>=90 AND sv.number<=100 THEN '90~100'
END
/*
Score Number
0~9 0
10~19 0
20~29 1
30~39 0
40~49 0
50~59 0
60~69 0
70~79 0
80~89 0
90~100 0
*/

Answer 3

;with Score as 
(
select 0 as MinS,10 as MaxS, convert(varchar(50),'0~9') as DisplayName Union all
select MinS+10 as MinS,MaxS+9 as MaxS, convert(varchar(50),rtrim(MinS+10) + '~' + rtrim(MaxS+ case MaxS when 90 then 10 else 9 end)) as DisplayName 
from Score
where MinS < 90
),StudentScore as (
select 50 as score union all
select 70 as score union all
select 90 as score union all
select 100 as score 
)
select a.DisplayName,count(b.score)
from Score a
left join StudentScore b on b.score between a.MinS and a.MaxS
group by a.DisplayName

Answer 4

如果要按照班级统计，需要将sno也加入到group by中，如下面的语句。
如果班级没有成绩也要列出来，那么最好有个班级的子表
否则现在只能列出班级有成绩的数据，如下面的1011将只返回一行
;WITH Reports(Cno,Score,sno) AS (
    SELECT 'A',20,'1011' UNION ALL
    SELECT 'A',20,'1012' UNION ALL
    SELECT 'A',20,'1013'
),Courses(Cno,cname) AS (
    SELECT 'A',N'语文' UNION ALL
    SELECT 'B',N'数学' UNION ALL
    SELECT 'C',N'物理' UNION ALL
    SELECT 'D',N'化学'
    )
 
SELECT  Courses.Cno,Courses.cname,Reports.sno,
Score=
CASE
WHEN sv.number>=0 AND sv.number<10 THEN '0~9'
WHEN sv.number>=10 AND sv.number<20 THEN '10~19'
WHEN sv.number>=20 AND sv.number<30 THEN '20~29'
WHEN sv.number>=30 AND sv.number<40 THEN '30~39'
WHEN sv.number>=40 AND sv.number<50 THEN '40~49'
WHEN sv.number>=50 AND sv.number<60 THEN '50~59'
WHEN sv.number>=60 AND sv.number<70 THEN '60~69'
WHEN sv.number>=70 AND sv.number<80 THEN '70~79'
WHEN sv.number>=80 AND sv.number<90 THEN '80~89'
WHEN sv.number>=90 AND sv.number<=100 THEN '90~100'
END,
COUNT(Reports.Cno) Number
FROM Courses INNER JOIN [master].dbo.spt_values AS sv ON sv.[type]='P' AND sv.number BETWEEN 1 AND 100 
LEFT JOIN Reports ON Courses.Cno=Reports.Cno AND Reports.Score=sv.number
GROUP BY Courses.Cno,Courses.cname,Reports.sno,CASE
WHEN sv.number>=0 AND sv.number<10 THEN '0~9'
WHEN sv.number>=10 AND sv.number<20 THEN '10~19'
WHEN sv.number>=20 AND sv.number<30 THEN '20~29'
WHEN sv.number>=30 AND sv.number<40 THEN '30~39'
WHEN sv.number>=40 AND sv.number<50 THEN '40~49'
WHEN sv.number>=50 AND sv.number<60 THEN '50~59'
WHEN sv.number>=60 AND sv.number<70 THEN '60~69'
WHEN sv.number>=70 AND sv.number<80 THEN '70~79'
WHEN sv.number>=80 AND sv.number<90 THEN '80~89'
WHEN sv.number>=90 AND sv.number<=100 THEN '90~100'
                                               END
HAVING sno='1011'