select DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) as month,sname,t.time,t.checktime,sum,t.idowner,t.count from (select
case when DATEDIFF(MINUTE,'08:00:00', CONVERT(varchar(100), checktime, 108))<0 then 0
when DATEDIFF(MINUTE,'08:00:00', CONVERT(varchar(100), checktime, 108))>0 and checktime between (select jbqssj From hga_jbtjb_v where xwsp='2c9cd8196ddc4923016dfb4148b41192'
and djzt='2c9cd87d6ca784ca016cb2ecdb000371' and
DATENAME(year, jbqssj) +'-' + DATENAME(MONTH, jbqssj) +'-'+
RIGHT('00'+CAST(day(jbqssj) AS VARCHAR(2)),2) =
DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) and idowner=(select id from hga_employee_v where sname=t.sname
)) and (select jbjzsj From hga_jbtjb_v where xwsp='2c9cd8196ddc4923016dfb4148b41192'
and djzt='2c9cd87d6ca784ca016cb2ecdb000371' and
DATENAME(year, jbjzsj) +'-' + DATENAME(MONTH, jbjzsj) +'-'+
RIGHT('00'+CAST(day(jbjzsj) AS VARCHAR(2)),2) =
DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) and idowner=(select id from hga_employee_v where sname=t.sname)
) then 0
else DATEDIFF(MINUTE,'08:00:00', CONVERT(varchar(100), checktime, 108)) end sum,
case when DATEDIFF(MINUTE,'08:00:00', CONVERT(varchar(100), checktime, 108))<0 then 0
when DATEDIFF(MINUTE,'08:00:00', CONVERT(varchar(100), checktime, 108))>0 and checktime between (select jbqssj From hga_jbtjb_v where xwsp='2c9cd8196ddc4923016dfb4148b41192'
and djzt='2c9cd87d6ca784ca016cb2ecdb000371' and
DATENAME(year, jbqssj) +'-' + DATENAME(MONTH, jbqssj) +'-'+
RIGHT('00'+CAST(day(jbqssj) AS VARCHAR(2)),2) =
DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) and idowner=(select id from hga_employee_v where sname=t.sname
)) and (select jbjzsj From hga_jbtjb_v where xwsp='2c9cd8196ddc4923016dfb4148b41192'
and djzt='2c9cd87d6ca784ca016cb2ecdb000371' and
DATENAME(year, jbjzsj) +'-' + DATENAME(MONTH, jbjzsj) +'-'+
RIGHT('00'+CAST(day(jbjzsj) AS VARCHAR(2)),2) =
DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2)and idowner=(select id from hga_employee_v where sname=t.sname)
) then 0
else 1 end count
,sname,checktime,id,idowner,DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) as time From hga_zkkq t where DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) in (
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%' )) t
where id=(select top (1) id from hga_zkkq where sname=t.sname and DATENAME(year, checktime) +'-' + DATENAME(MONTH, checktime) +'-'+
RIGHT('00'+CAST(day(checktime) AS VARCHAR(2)),2) =t.time order by checktime )
and t.idowner !='null'
uj5u.com熱心網友回復：
建議將子查詢修改為臨時表。uj5u.com熱心網友回復：
你這樣還不如把幾個表貼出來，說一下要求，可能會有人幫你寫出簡練一點的sql轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/82377.html
標籤：疑難問題
下一篇：eclipse