我已經創建了上面的查詢，它為我提供了截至 sysdate 時標記給該人的 Bonus。
select
person_number ,
peef.effective_start_Date,
peef.value Amount
from
per_all_people_f papf,
pay_element_entries_f peef
where
papf.person_id = peef.person_id
and PEEF.element_name in ('Bonus')
and sysdate between peef.effective_start_Date and peef.effective_end_Date
我想調整上述查詢以獲取截至 31/3 的過去三年的金額，而不是截至 31/3/2021、31/03/2020、31/03/2019 的 sysdate
輸出如 -
Person_NUMBER effective_start_Date current_Amount 2021_AMOUNT 2020_AMOUNT 2019_AMOUNT
如何調整相同的查詢并更改 sysdate 條件以查找過去三年的資料以及 2021_amount、2020_amount 和 2019_amount 列
uj5u.com熱心網友回復：
您可以將最后一行移動到 SELECT 串列作為條件，例如
SELECT person_number, peef.effective_start_Date,
peef.value AS current_amount,
CASE WHEN date'2021-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END AS 2021_amount,
CASE WHEN date'2020-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END AS 2020_amount,
CASE WHEN date'2019-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END AS 2019_amount
FROM per_all_people_f papf
JOIN pay_element_entries_f peef
ON peef.person_id = papf.person_id
WHERE peef.element_name = 'Bonus'
為了獲得當前顯示的結果，但在這種情況下，金額列將有多行具有 NULL 值。我懷疑您需要獲取匯總結果才能顯示匯總金額，然后考慮添加 SUM() 聚合，例如
SELECT person_number, peef.effective_start_Date,
SUM( peef.value ) AS current_amount,
SUM( CASE WHEN date'2021-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END ) AS 2021_amount,
SUM( CASE WHEN date'2020-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END ) AS 2020_amount,
SUM( CASE WHEN date'2019-03-31' BETWEEN peef.effective_start_Date AND peef.effective_end_Date
THEN peef.value
END ) AS 2019_amount
FROM per_all_people_f papf
JOIN pay_element_entries_f peef
ON peef.person_id = papf.person_id
WHERE peef.element_name = 'Bonus'
GROUP BY person_number, peef.effective_start_Date
uj5u.com熱心網友回復：
您可以在同一查詢中插入多個選擇并根據需要命名結果。這是我的意思的一個例子
SELECT a.distributor_id,
(SELECT COUNT(*) FROM myTable WHERE level='personal' and distributor_id = a.distributor_id) as PersonalCount,
(SELECT COUNT(*) FROM myTable WHERE level='exec' and distributor_id = a.distributor_id) as ExecCount,
(SELECT COUNT(*) FROM myTable WHERE distributor_id = a.distributor_id) as TotalCount
FROM (SELECT DISTINCTdistributor_id FROM myTable) a ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/415969.html
標籤：
上一篇：ORA-01861:文字與char變數上的格式字串不匹配錯誤
下一篇：排除記錄組——如果數量上升