我有一個下表結構
當員工的協議型別是基本和附錄（例如第 1,2 和 5,6 行）時，需要將這兩行一起考慮，并且狀態將處于活動狀態。下面應該是預期的結果
如何在 oracle 10g 中實作這一點。謝謝
uj5u.com熱心網友回復：
這可以使用CASE陳述句和LEAD決議函式來實作，以查看下一個 ID 是否為附錄。
詢問
--This is to set up the sample data
WITH
emp_agreements (id, emp_id, agreement_type)
AS
(SELECT 1, 1023, 'Basic' FROM DUAL
UNION ALL
SELECT 2, 1023, 'Appendix' FROM DUAL
UNION ALL
SELECT 3, 1023, 'Basic' FROM DUAL
UNION ALL
SELECT 4, 1023, 'Basic' FROM DUAL
UNION ALL
SELECT 5, 1023, 'Basic' FROM DUAL
UNION ALL
SELECT 6, 1023, 'Appendix' FROM DUAL)
--Real query begins here. You will need to put in your real table name
SELECT emp_id, status
FROM (SELECT id,
emp_id,
agreement_type,
CASE LEAD (agreement_type) OVER (PARTITION BY emp_id ORDER BY id)
WHEN 'Appendix' THEN 'Active'
ELSE 'Pending'
END AS status
FROM emp_agreements)
WHERE agreement_type = 'Basic'
ORDER BY id;
結果
EMP_ID STATUS
_________ __________
1023 Active
1023 Pending
1023 Pending
1023 Active
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/455870.html
上一篇：有條件地回傳多行的程序