27,579
社区成员
发帖
与我相关
我的任务
分享
select c.collegeNo, c.CollegeName 系别,
case grouping(SpeGrade) when 1 then N'合计' else SpeGrade end 年级,count(distinct ClassNo) 班数,
count(1)人数,count(case when sex='1' then 1 end ) 男,count(case when sex='0' then 1 end ) 女,
count(case when Nation='MZDM002' then 1 end) 满族,
count(case when Nation='MZDM004' then 1 end) 回族,
count(case when Nation='MZDM009' then 1 end) 蒙族,
count(d.StudentId) 困难人数,
count(case when Nation not in ('MZDM009','MZDM002','MZDM004','MZDM001') then 1 end) 其他族
from StudentInfo s inner join CollegeInfo c on s.CollegeNo=c.CollegeNo left join [dbo].[Need_NeedData] d on s.studentId=d.StudentId
group by c.collegeNo,s.CollegeNo,c.CollegeName,SpeGrade with rollup
having grouping(c.CollegeName)=0