JW/EMIS.Web/Config/Report/StudentManage/StudentStatisticsData.sql

SELECT 
class.ClassNum AS classNo ,class.Name AS className, grade.GradeID AS year,
Standards.Name AS StandardsName,college.Name AS collegeName,
school.Code AS schoolCode,
unicersity.Name AS unicersityName,
COUNT(1) as StudentCount  FROM dbo.CF_Student student
INNER JOIN dbo.CF_InSchoolSetting InSchoolSetting ON InSchoolSetting.InSchoolStatusID = student.InSchoolStatusID --AND InSchoolSetting.IsSelected=1
INNER JOIN dbo.CF_Classmajor class ON class.ClassmajorID = student.ClassmajorID
INNER JOIN dbo.CF_Grademajor grade ON grade.GrademajorID = class.GrademajorID
INNER JOIN dbo.CF_Facultymajor faculty ON faculty.FacultymajorID = grade.FacultymajorID
INNER JOIN dbo.Sys_DictionaryItem Standards ON Standards.Value = faculty.StandardID AND Standards.DictionaryCode = 'CF_Standard'
INNER JOIN dbo.CF_College college ON college.CollegeID = faculty.CollegeID
INNER JOIN dbo.CF_Campus campus ON campus.CampusID = college.CampusID
INNER JOIN dbo.CF_University unicersity ON unicersity.UniversityID = campus.UniversityID
INNER JOIN dbo.CF_Schoolyear Schoolyear ON Schoolyear.SchoolyearID = grade.GraduateSchoolyearID 
INNER JOIN dbo.CF_Schoolyear school ON school.IsCurrent=1 AND school.Value<=Schoolyear.Value
WHERE (@INSchoolStatus is null or InSchoolSetting.IsSelected=@INSchoolStatus)
GROUP BY class.ClassNum,class.ClassmajorID,class.Name,
grade.GrademajorID,grade.GradeID,
college.Name,Standards.Name,
Schoolyear.Code,school.Code,
unicersity.Name
ORDER BY class.ClassNum, class.Name