對每個子組使用 max 似乎不起作用。

我在 Oracle 11g 中有一個如下所示的表：

+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA      | PERS       | RUN_DATE   |
+----------------------------------------------------------+
| 123      | ALT     | 2         |            | 21-JAN-13  |
| 123      | ALT     | 2         |            | 22-FEB-13  |
| 123      | ALT     |           | 3          | 23-FEB-13  |
| 124      | PRI     | 4         |            | 24-JAN-13  |
| 124      | PRI     | 4         |            | 25-FEB-13  |
+----------------------------------------------------------+

我需要根據 Orga 和 Pers 列獲取最新的 RUN_DATE。使表格看起來像這樣：

+----------------------------------------------------------+
| ACCT_NBR | MAIL_TY | ORGA      | PERS       | RUN_DATE   |
+----------------------------------------------------------+
| 123      | ALT     | 2         |            | 22-FEB-13  |
| 123      | ALT     |           | 3          | 23-FEB-13  |
| 124      | PRI     | 4         |            | 25-FEB-13  |
+----------------------------------------------------------+

我嘗試使用此查詢，但它似乎不起作用：

Select *  
from wh_acct   
where a.rundate = (select max(a2.rundate)  
from wh_acct a2)  
WHERE a2.ORGA = a.ORGA)
UNION
Select *  
from wh_acct   
where a.rundate = (select max(a2.rundate)  
from wh_acct a2)  
WHERE a2.PERS = a.PERS)

誰能指出我正確的方向？

您的右括號比左括號多兩個，並且a缺少別名：

Select *  
from wh_acct a                                 -- alias added
where a.rundate = 
     (select max(a2.rundate)  
      from wh_acct a2                         -- parenthesis removed
      WHERE a2.ORGA = a.ORGA)
UNION
Select *  
from wh_acct a                                 -- alias added 
where a.rundate = 
     (select max(a2.rundate)  
      from wh_acct a2                         -- parenthesis removed
      WHERE a2.PERS = a.PERS) ;

OR您還可以使用而不是編寫查詢UNION：

Select *  
From wh_acct a
Where a.rundate = 
     (Select max(a2.rundate)  
      From wh_acct a2   
      Where a2.ORGA = a.ORGA)
  Or a.rundate = 
     (Select max(a2.rundate)  
      From wh_acct a2   
      Where a2.PERS = a.PERS) ;

或使用視窗函式：

WITH cte AS
   ( SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE, 
            MAX(rundate) OVER (PARTITION BY ORGA) AS max_rundate_orga,
            MAX(rundate) OVER (PARTITION BY PERS) AS max_rundate_pers 
     FROM wh_acct
   ) 
SELECT ACCT_NBR, MAIL_TY, ORGA, PERS, RUN_DATE
FROM cte
WHERE rundate = max_rundate_orga
  OR rundate = max_rundate_pers ;

---

如果如您的範例所示，所有行都NULL在orga其中有值，pers反之亦然，您可以將 更改UNION為UNION ALL以提高效率，因為不需要檢查子結果的重複項（這就是這樣UNION做的）。window-cte 版本也可以重寫為ALL2 個子查詢的並集 ( )。

索引(orga, run_date)並且(pers, run_date)也會有所幫助。

您可以為此使用分析函式： Function(arg1,…, argn) OVER (

$$ PARTITION BY <…> $$ $$ ORDER BY <….> $$ $$ $$) 例子：

http://www.orafaq.com/node/55

引用自：https://dba.stackexchange.com/questions/48453