有两个表，一个保存课程列表，一个保存选课志愿，简单来说就是
T_Course: 每一门课一行记录
-----------------------
CourseId
CourseName
T_Enlist: 每个学生报名一行记录
-----------------------
EnlistId
StudentId
CourseId
ChoiceOrder
-----------------------
ChoiseOrder 意思是第几志愿，值是1、2、3中一个
现在要列出所有课程，并按照它的第一二三志愿值来统计每一门课的报名总人数，
形成结果集：
--------------------------
CourseName,
Summ1,
Summ2,
Summ3
求助各位大侠！
------解决方案--------------------
select courseid,
count(case when ChoiceOrder when 1 then 1 else null end) '第一志愿',
count(case when ChoiceOrder when 2 then 1 else null end) '第二志愿',
count(case when ChoiceOrder when 3 then 1 else null end) '第三志愿'
from T_Enlist
group by courseid
这样？
------解决方案--------------------
with cte as
(
select CourseId, ChoiceOrder, count(0) as cnt from T_Enlist group by CourseId, ChoiceOrder
)
select a.CourseName, isnull(cte1.cnt, 0) as Summ1, isnull(cte2.cnt, 0) as Summ2, isnull(cte3.cnt, 0) as Summ3
from T_Course a left join cte cte1 on a.CourseId = cte1.CourseId
left join cte cte2 on a.CourseId = cte2.CourseId
left join cte cte3 on a.CourseId = cte3.CourseId
where cte1.ChoiceOrder = 1
and cte2.ChoiceOrder = 2
and cte3.ChoiceOrder = 3