我必須創建一個查詢來顯示員工是否按時休了什么樣的假。
select papf.person_number,
atrb.attribute_category element,
atrb.measure measure_hours,
rec.start_Date,
rec.end_Date
from per_all_people_F papf,
hwm_tm_rec rec,
fusion.hwm_tm_rep_atrbs atrb,
fusion.hwm_tm_rep_atrb_usages ausage,
hwm_tm_statuses status
where 1=1
AND atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
AND ausage.usages_source_id = rec.tm_rec_id
AND ausage.usages_source_version = rec.tm_rec_version
AND status.tm_bldg_blk_id = REC.tm_rec_id
AND status.tm_bldg_blk_version = REC.tm_rec_version
AND REC.tm_rec_type IN ( 'RANGE', 'MEASURE' )
and papf.person_number = '101928'
AND Trunc (status.date_to) = To_date ('31/12/4712', 'DD/MM/YYYY')
and atrb.attribute_category in( 'Overtime','Regular Pay', 'Double_Time')
and Trunc (sh21.start_time) between trunc(:P_From_Date) and trunc(:P_To_Date)
這給了我 p_from_date- 01-Jan-2021 和 p_to_date- 31-Jul-2021 之間的輸出 -
Person_Number Element measure_hours Start_Date end_date
101928 Overtime 10 10-Jan-2021 10-Jan-2021
101928 Overtime 8 09-Jul-2021 09-Jul-2021
101928 Regular Pay 10.9 23-Jan-2021 24-jan-2021
101928 Regular Pay 4.1 01-Jun-2021 01-Jun-2021
101928 Double_Time 34 02-Feb-2021 04-Feb-2021
現在想要調整查詢以提供如下輸出 -
Person_Number Overtime_measure_hours Regular_Measure_hours Others_code Others_measure
101928 18 15 Double_Time 34
即 Overtime_measure_hours、Regular_Measure_hours 和 Others_measure 應該是這些值的總和。
有沒有辦法在不使用子查詢的情況下調整我的查詢？或者我怎樣才能以最有效的方式做到這一點？
uj5u.com熱心網友回復：
也許像這樣，使用條件聚合來旋轉。
但我不確定那個交叉連接。
SELECT papf.person_number
, SUM(CASE WHEN atrb.attribute_category = 'Overtime' THEN atrb.measure measure_hours END) AS Overtime_measure_hours
, SUM(CASE WHEN atrb.attribute_category LIKE 'Regular P%' THEN atrb.measure measure_hours END) AS Regular_Measure_hours
, MAX(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.attribute_category END) AS Others_code
, SUM(CASE WHEN atrb.attribute_category NOT IN ('Overtime','Regular Pay') THEN atrb.measure measure_hours END) AS Others_measure
FROM hwm_tm_rec AS rec
CROSS JOIN per_all_people_F AS papf
JOIN fusion.hwm_tm_rep_atrb_usages AS ausage
ON ?ausage.usages_source_id = rec.tm_rec_id
? AND ausage.usages_source_version = rec.tm_rec_version
JOIN fusion.hwm_tm_rep_atrbs AS atrb
?ON atrb.tm_rep_atrb_id = ausage.tm_rep_atrb_id
JOIN hwm_tm_statuses AS status
ON status.tm_bldg_blk_id = rec.tm_rec_id
? AND status.tm_bldg_blk_version = rec.tm_rec_version
WHERE 1=1
AND papf.person_number = '101928'
AND rec.tm_rec_type IN ('RANGE', 'MEASURE')
AND TRUNC(status.date_to) = TO_DATE('31/12/4712', 'DD/MM/YYYY')
AND atrb.attribute_category IN ('Overtime','Regular Pay', 'Double_Time')
AND TRUNC(sh21.start_time) BETWEEN TRUNC(:P_From_Date) AND TRUNC(:P_To_Date)
GROUP BY papf.person_number
ORDER BY papf.person_number
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/368565.html