如何在使用max(date)時從表中獲取最新狀態

這是一個名為.. COMPLAINTS 的表名：-

COMNO       CDATE           MESSAGE                             STATUS
----------------------------------------------------------------------
12345       05-JAN-22       CUSTOMER ISSUE REPORTED             OPEN        
12345       07-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS
12345       09-JAN-22       ISSUE RESOLVED                      CLOSED
56789       14-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
56789       23-JAN-22       ISSUE RESOLVED                      CLOSED
85642       03-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       30-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       31-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS

這是我的甲骨文查詢 -

SELECT COMNO,
       MIN(CDATE) AS COMPLAINT_CREATION_DATE,
       MAX(CDATE) AS COMPLAINT_CLOSURE_DATE
  FROM COMPLAINT 
 WHERE CDATE BETWEEN TO_DATE('01/Jan/2022 00:00:00','dd/Mon/yyyy hh24:mi:ss')     AND TO_DATE('31/Jan/2022 23:59:59','dd/Mon/yyyy hh24:mi:ss') 
   AND REFNO='12345'
 GROUP BY COMNO 

這給了我 REFNO='12345' 的結果

COMNO       COMPLAINT_CREATION_DATE   COMPLAINT_CLOSURE_DATE
--------------------------------------------------------
12345       05-JAN-22                 09-JAN-22

在此查詢中，我想添加所有投訴的最后狀態和訊息

COMNO   COMPLAINT_CREATION_DATE   COMPLAINT_CLOSURE_DATE   MESSAGE   STATUS
-------------------------------------------------------------------
12345       05-JAN-22       09-JAN-22       ISSUE RESOLVED                      CLOSED
56789       14-JAN-22       23-JAN-22       ISSUE RESOLVED                      CLOSED
85642       03-JAN-22       03-JAN-22       CUSTOMER ISSUE REPORTED             OPEN
78632       30-JAN-22       31-JAN-22       WAITING FOR THE CUSTOMER RESPONCE   IN PROGRESS

任何人都可以請幫助我。

uj5u.com熱心網友回復：

不要使用自聯接，使用KEEP (DENSE_RANK LAST ORDER BY CDATE)：

SELECT COMNO,
       MIN(CDATE) AS COMPLAINT_CREATION_DATE,
       MAX(CDATE) AS COMPLAINT_CLOSURE_DATE,
       MAX(MESSAGE) KEEP (DENSE_RANK LAST ORDER BY CDATE) AS message,
       MAX(STATUS)  KEEP (DENSE_RANK LAST ORDER BY CDATE) AS status
  FROM COMPLAINT 
 WHERE CDATE >= DATE '2022-01-01'
   AND CDATE <  DATE '2022-02-01' 
   AND REFNO='12345'
 GROUP BY COMNO 

或決議函式：

SELECT comno,
       COMPLAINT_CREATION_DATE,
       cdate AS COMPLAINT_CLOSURE_DATE,
       message,
       status
FROM   (
  SELECT c.*,
         MIN(CDATE) OVER (PARTITION BY comno) AS COMPLAINT_CREATION_DATE,
         ROW_NUMBER() OVER (PARTITION BY comno ORDER BY cdate DESC) AS rn
    FROM COMPLAINT c
   WHERE CDATE >= DATE '2022-01-01'
     AND CDATE <  DATE '2022-02-01' 
     AND REFNO='12345'
)
WHERE  rn = 1

其中，對于樣本資料：

CREATE TABLE complaint (REFNO, COMNO, CDATE, MESSAGE, STATUS) AS
SELECT '12345', 12345, DATE '2022-01-05', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 12345, DATE '2022-01-07', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL UNION ALL
SELECT '12345', 12345, DATE '2022-01-09', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT '12345', 56789, DATE '2022-01-14', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 56789, DATE '2022-01-23', 'ISSUE RESOLVED', 'CLOSED' FROM DUAL UNION ALL
SELECT '12345', 85642, DATE '2022-01-03', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 78632, DATE '2022-01-30', 'CUSTOMER ISSUE REPORTED', 'OPEN' FROM DUAL UNION ALL
SELECT '12345', 78632, DATE '2022-01-31', 'WAITING FOR THE CUSTOMER RESPONCE', 'IN PROGRESS' FROM DUAL

兩個輸出：

康諾	COMPLAINT_CREATION_DATE	COMPLAINT_CLOSURE_DATE	資訊	狀態
12345	2022-01-05 00:00:00	2022-01-09 00:00:00	問題解決了	關閉
56789	2022-01-14 00:00:00	2022-01-23 00:00:00	問題解決了	關閉
78632	2022-01-30 00:00:00	2022-01-31 00:00:00	等待客戶回復	進行中
85642	2022-01-03 00:00:00	2022-01-03 00:00:00	客戶問題報告	打開

db<>在這里擺弄

uj5u.com熱心網友回復：

您可以從查詢中洗掉過濾條件并將其再次加入表中 -

SELECT C.COMNO,
       CD.COMPLAINT_CREATION_DATE,
       CD.COMPLAINT_CLOSURE_DATE,
       C.MESSAGE,
       C.STATUS
  FROM (SELECT COMNO,
               MIN(CDATE) AS COMPLAINT_CREATION_DATE,
               MAX(CDATE) AS COMPLAINT_CLOSURE_DATE
          FROM COMPLAINT 
         WHERE CDATE BETWEEN TO_DATE('01/Jan/2022 00:00:00','dd/Mon/yyyy hh24:mi:ss')
                         AND TO_DATE('31/Jan/2022 23:59:59','dd/Mon/yyyy hh24:mi:ss') 
         GROUP BY COMNO) CD
  JOIN COMPLAINTS C ON CD.COMNO = C.COMNO
                   AND CD.COMPLAINT_CLOSURE_DATE = C.CDATE;

演示。

標籤：sql 甲骨文

上一篇：是否可以根據回傳多行的選擇在表中插入多行

下一篇：我無法創建和使用包