查找子組的最小值，過濾掉同一父組中之前的最小值

如何獲取組（Acc、TranType）的最小值，但過濾掉 Acc 組前面行中使用的任何最小值。前面的行將被定義為 Acc asc、TranType asc。

PosCancelID 每個 Acc 組應該只出現一次。但相同的 PosCancelID 可能出現在數據集中的另一個 Acc 組中。

所以對於給定的數據集：

Acc  | TranType | PosCancelID
100     1               2
808     1               5
808     1               4
808     2               5
808     2               4<--To be filtered from min calc as it min for (808,1)
813     2               3
813     4               3<--To be filtered from min calc as it min for (813,2)
809     1               3
809     1               4
809     2               3<--To be filtered from min calc as (809,1) uses it
809     2               4 
809     3               4<--To be filtered from min calc as (809,2) uses it

我應該得到：

Acc  | TranType | PosCancelID
100     1               2
808     1               4
808     2               5
813     2               3
809     1               3
809     2               4

SELECT ACC, TranType, min(maxPreceeding) as ActualCancelID 
FROM 
(
  SELECT ACC, TranType,  
         MAX(m.posCancelID) OVER (PARTITION BY m.ACC 
                                 ORDER BY m.TranType, m.posCancelID 
                                 ROWS UNBOUNDED PRECEDING) as maxPreceeding
   FROM MCancel as m
) AS x
GROUP BY ACC, TranType

上面的查詢幾乎給了我我想要的東西，但沒有過濾 acc = 813。所以我知道必須有更好的（實際上應用過濾器來刪除以前的最小值）方式。

相當難的問題。這是一個遞歸解決方案：

WITH 
 rcte AS
 ( SELECT TOP (1)
       Acc, TranType, posCancelID,
       CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs 
   FROM
       MCancel
   ORDER BY
       Acc, TranType, posCancelID

   UNION ALL

   SELECT
       Acc, TranType, posCancelID,
       CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
   FROM 
     ( SELECT
           m.*, 
           r.IDs, 
           ROW_NUMBER() OVER (ORDER BY m.Acc, m. TranType, m.PosCancelID) AS rn
       FROM
           rcte AS r 
           JOIN MCancel AS m
               ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
               OR  (m.Acc > r.Acc)
       WHERE
           r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
     ) AS mc
   WHERE
       rn = 1
 )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

該解決方案假定 aposCancelID不應在結果集中出現兩次。如果要求它們不能 在同一個Acc組中出現兩次，那麼解決方案需要稍微調整一下：

---

WITH rcte AS
 ( SELECT 
     Acc, TranType, posCancelID,
     CAST('=' + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX)) AS IDs 
   FROM
     ( SELECT 
         Acc, TranType, posCancelID,
         ROW_NUMBER() OVER (PARTITION BY Acc ORDER BY TranType, PosCancelID) AS rnk
       FROM MCancel
     ) AS f
   WHERE rnk = 1

   UNION ALL

   SELECT
     Acc, TranType, posCancelID,
     CAST(IDs + CAST(posCancelID AS VARCHAR(20)) + '=' AS VARCHAR(MAX))
   FROM
     ( SELECT
         m.*, r.IDs, 
         ROW_NUMBER() OVER (PARTITION BY m.Acc
                            ORDER BY m.TranType, m.PosCancelID) AS rn
       FROM
         rcte AS r 
         JOIN MCancel AS m
             ON  (m.Acc = r.Acc AND m.TranType > r.TranType)
       WHERE
         r.IDs NOT LIKE ('%=' + CAST(m.posCancelID AS VARCHAR(20)) + '=%')
     ) AS mc
   WHERE rn = 1
 )
SELECT Acc, TranType, posCancelID
FROM rcte
ORDER BY Acc, TranType ;

兩者都在**dbfiddle.uk進行了測試**

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