N
nylx
Unregistered / Unconfirmed
GUEST, unregistred user!
MS SQL语句:<br>select distinct d.dept,yd.ydrs,sd.sdrs,zh.zjrs,jj.jjrs<br>from department d<br>--按部门统计应到人数<br>left join<br> (select d.dept,count(p.emno) as ydrs from person p<br> left join department d on d.dpno=p.dpno<br> group by d.dept) yd on d.dept=yd.dept<br>--安部门统计实到人数<br>left join<br> (select d.dept,count(distinct p.emno) as sdrs from person p<br> left join department d on d.dpno=p.dpno<br> left join attendance a on a.emno=p.emno<br> where a.adate!='' and a.atime!=''<br> group by d.dept) sd on d.dept=sd.dept<br>--按部门统计直接人员数<br>left join<br> (select d.dept,count(p.zhj) as zjrs from person p<br> left join department d on d.dpno=p.dpno<br> where p.zhj='直接'<br> group by d.dept) zh on d.dept=zh.dept<br>--按部门统计间接人员数<br>left join <br> (select d.dept,count(p.zhj) as jjrs from person p<br> left join department d on d.dpno=p.dpno<br> where p.zhj='间接'<br> group by d.dept) jj on d.dept=jj.dept