EXPLAIN ANALYZE SELECT
pay.personid,
pay.TIMECARDDATE AS 日期,
‘迟到’ AS 考勤类型,
‘F01’ AS 考勤类型编码,
CASE
WHEN ifnull( H01, 0 ) > 0 THEN
0
WHEN ifnull( F01, 0 ) > 0 THEN
F01 ELSE F02
END AS 数量,
endProcessTime
FROM
(
SELECT
P.personid,
TIMECARDDATE,
IF
( ( lb.classcode = ‘H01’ ), sum( DISTINCT P.PAYHOURS ), 0 ) AS ‘H01’,
IF
( ( lb.classcode = ‘F01’ ), sum( DISTINCT P.PAYHOURS ), 0 ) AS ‘F01’,
IF
( ( lb.classcode = ‘F02’ ), sum( DISTINCT P.PAYHOURS ), 0 ) AS ‘F02’,
MAX( pro.endProcessTime ) endProcessTime
FROM
edf_process_task pro STRAIGHT_JOIN attendance_submit_form sub ON sub.ProcessInstanceId = pro.processInstanceId STRAIGHT_JOIN greentown.atdpersonpaycode P ON sub.personid = P.personid
AND ifnull( P.timecarddate, ‘’ ) BETWEEN DATE_FORMAT( sub.startdate, ‘%Y-%m-%d’ )
AND DATE_FORMAT( sub.enddate, ‘%Y-%m-%d’ )
LEFT JOIN greentown.atd_attendance_class lb ON P.PAYCODE = lb.ID
WHERE
lb.classcode IN (
‘J01’,
‘J02’,
‘I06’,
‘I04’,
‘A03’,
‘A04’,
‘I07’,
‘A06’,
‘A07’,
‘A08’,
‘I08’,
‘A10’,
‘I05’,
‘A11’,
‘A12’,
‘D06’,
‘D07’,
‘D08’,
‘D09’,
‘D10’,
‘I01’,
‘I02’,
‘I09’,
‘I10’,
‘I11’,
‘I12’,
‘I13’,
‘I14’,
‘I15’,
‘I16’,
‘L01’,
‘I03’,
‘H01’
)
AND pro.status = ‘COMPLETED’
AND pro.prsid = ‘ProcessAttendanceSubmitForm’
AND ifnull( pro.endProcessTime, ‘’ ) >= ‘2022-12-01 17:00:27.507’
GROUP BY
personid,
TIMECARDDATE,
lb.classcode
) pay
这两个SQL 就 lb.classcode 的值不一样