我有一個輸出如下的查詢：
select c.case_id caseId, c.memberId memberId, c.last_name lastName, c.first_name firstName, lkp_alt.descr altShowCauseAuthority,
'Active' as caseType,
listagg(lkp_cs.descr,',') within group (order by lkp_cs.id) as caseStatus
from cases c join lkP_alt_show_cause_authority lkp_alt
on c.ATL_SHOW_CAUSE_AUTHORITY = lkp_alt.id
and c.case_type = 'P'
JOIN (SELECT case_id,
case_status_id,
COUNT(DECODE(case_status_id, 2, 1, NULL)) OVER (PARTITION BY case_id) AS IS_CLOSED
FROM case_status) cs ON cs.case_id = c.case_id AND cs.is_closed = 0
join lkp_case_status lkp_cs
on lkp_cs.id = cs.case_status_id
where (c.created_by = 1 and c.assigned_to is null)
or (c.assigned_to = 1) and c.delete_date is null
group by c.case_id, c.memberId, c.last_name, c.first_name, lkp_alt.descr, c.case_type;
輸出：
caseId memberId lastName firstName altShow caseType caseStatus
101 1365385501 WWW test MARFORK Active Active ,Closed ,EO
102 1501534761 AAA test MARFORK Active IGMC,JPAS
110 1572158078 CCC test I MEF Active EO,EEO
Case_status 如下：
case_id case_status_id case_status_date
101 1 17-OCT-21
101 2//Closed 17-NOV-19
101 3 17-OCT-21
102 4 17-OCT-21
102 5 17-OCT-21
110 8 17-OCT-21
110 9 17-OCT-21
現在，我希望我的查詢顯示除已結案件以外的所有案件，案件狀態日期距當前日期少于 365 天，這意味著我不應該顯示案件 101，因為它的案件狀態已關閉，日期是過去 2 年。請提出任何建議。
uj5u.com熱心網友回復：
如果你不想顯示所有記錄，case_id = 101那么
SELECT * FROM CASES
WHERE
CASE_ID NOT IN
(
SELECT CASE_ID FROM CASES
WHERE
CASE_STATUS_ID = 2 AND
MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12
)
-- both queries get same results, but this query more performancing
SELECT C1.* FROM CASES C1
LEFT JOIN (
SELECT CASE_ID FROM CASES
WHERE
CASE_STATUS_ID = 2 AND
MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12
) C2 ON C1.CASE_ID = C2.CASE_ID
WHERE C2.CASE_ID IS NULL
如果你不想只顯示這條記錄，case_id = 101 and case_status_id=2那么
SELECT * FROM CASES
WHERE
NOT (CASE_STATUS_ID = 2 AND
MONTHS_BETWEEN(SYSDATE, CASE_STATUS_DATE)>12)
如果你想過濾幾天你可以使用這個條件： CAST((SYSDATE - CASE_STATUS_DATE) AS INTEGER) > 365
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/361914.html