贴一个sql

select HRM_KQDAY.PersonID,hrm_personnel.userid,hrm_personnel.deptid,hrm_personnel.positionid as posid,
       Sum(CDate) as CDate,
       Sum(Case CType when '01' then (ZCHOURS-QJHOURS-CDHOURS-ZTHOURS)*60/480 else 0 end) as ZCHOURS,
       Sum(Case CType when '02' then (ZCHOURS-QJHOURS-CDHOURS-ZTHOURS)*60/480 else 0 end) as SXHOURS,
       Sum(Case CType when '03' then (ZCHOURS-QJHOURS-CDHOURS-ZTHOURS)*60/480 else 0 end) as JRHOURS,
       Sum(CDHOURS*60/480) as CDHOURS,
       Sum(ZTHOURS*60/480) as ZTHOURS,
       Sum(YBNUMS) as YBNUMS,
       Sum(Case QTYPE when '01' then QJHOURS*60/480 else 0 end) as BJ1HOURS,
       Sum(Case QTYPE when '02' then QJHOURS*60/480 else 0 end) as BJ2HOURS,
       Sum(Case QTYPE when '03' then QJHOURS*60/480 else 0 end) as BJ3HOURS,
       Sum(Case QTYPE when '04' then QJHOURS*60/480 else 0 end) as BJ4HOURS,
       Sum(Case QTYPE when '05' then QJHOURS*60/480 else 0 end) as BJ5HOURS,
       Sum(Case QTYPE when '06' then QJHOURS*60/480 else 0 end) as BJ6HOURS,
       Sum(Case QTYPE when '07' then QJHOURS*60/480 else 0 end) as BJ7HOURS,
       Sum(Case JBTYPE when '1' then JBHOURS*60/480 else 0 end) as JBPHOURS,
       Sum(Case JBTYPE when '2' then JBHOURS*60/480 else 0 end) as JBSHOURS,
       Sum(Case JBTYPE when '3' then JBHOURS*60/480 else 0 end) as JBJHOURS,
       Sum(Case JBTYPE when '4' then JBHOURS*60/480 else 0 end) as JBDHOURS
from HRM_KQDAY
left join hrm_personnel on hrm_personnel.personid=HRM_KQDAY.personid

where KSTATUS= 'T'
      and Convert(Char(10),KDate,20)>='2007-05-01'
      and Convert(Char(10),KDate,20)<='2007-05-31'
      and HRM_KQDAY.PersonID in (select HRM_KQDAY.PersonID from HRM_KQDay where Kdate=(select max(kdate) from hrm_kqday where year(kdate)='2007' and month(kdate)='5' and kqid='GD000602') and KSTATUS= 'T' and KQID='GD000602')
      and HRM_KQDAY.PersonID not in (select HRM_KQDAY.PersonID from HRM_KQMONTH where KYEAR=2007 and KMONTH=5 and HRM_KQDAY.PersonID in (select KQEDID from HRM_KQRIGHT where KQID='GD000602'))
group by HRM_KQDAY.PersonID,hrm_personnel.userid,hrm_personnel.deptid,hrm_personnel.positionid