其他 檢視每班人數多少人

各位同工:

請問在WebSAMS那處可以一次過檢視每班人數多少人, 每班有否懷疑退學學生, 如果系統沒有地方可以一次過檢視, 請問SQL如何編寫.

謝謝指教

有心人

 

please try

select
a.classlevel,
a.classname,
b.male 'Male by Class',
b.female 'Female by Class',
(b.male+b.female) 'No. of student in class',
b.dropout 'No. of Depart or Suspected Dropout in Class'

from tb_sch_schclass a

join (select suid, schyear, schlvl, schsess, classlvl, classcode, sum(case when sex='M' then 1 else 0 end) 'male', sum(case when sex='F' then 1 else 0 end) 'female', sum(case when suspdropout='1' then 1 else 0 end) 'dropout'
from vw_stu_lateststudent
where schyear=? and (status is null or status not in (4,5,6))
group by suid, schyear, schlvl, schsess, classlvl, classcode) b
on a.suid=b.suid and a.schyear=b.schyear and a.schlevel=b.schlvl and a.schsession=b.schsess and a.classlevel=b.classlvl and a.classcode=b.classcode


order by a.classlevel, a.classcode

 

this line
b.dropout 'No. of Depart or Suspected Dropout in Class'

should be changed to
b.dropout 'No. of Suspected Dropout in Class'