使用 group by 和 row_number 根據另一列的順序獲取唯一 ID

這是SQLfiddle的連結。出於分頁目的，我首先執行一個查詢來執行 if 和 order by 並返回一個 id 列表，我使用它來查詢所有包含這些 id 列表的數據。我在第一個查詢中遇到 order by 問題，它不允許我在嵌套語句（視圖）中使用 order by。簡而言之，提供了一個小提琴。使用查詢

SELECT req.reqId, req.RN from (
SELECT 
   (ROW_NUMBER() OVER (ORDER BY A.requisitionId asc)) as RN,
   A.reqId as reqId                            
   FROM dbo.RequisitionToPatient B
   INNER JOIN dbo.Requisition A ON A.reqId = B.reqId
   INNER JOIN dbo.Orders C on C.reqId = A.reqId
) req
GROUP BY req.reqId, req.RN

我正進入（狀態

reqId   RN
----------
12      1
12      2
14      3
11      4
11      5
10      6
10      7
9       8
9       9
9       10
9       11

我想要的是

reqId
-----
12      
14      
11          
10       
9        

試試這個：

select reqID FROM(
select req.reqId, req.RN from (
select 
       (ROW_NUMBER() OVER (ORDER BY A.received desc)) as RN,
       A.reqId as reqId                            
       FROM RequisitionToPatient B
       INNER JOIN Requisition A ON A.reqId = B.reqId
       INNER JOIN Orders C on C.reqId = A.reqId
) req
group by req.reqId, req.RN
order by req.RN) AS a
 GROUP BY reqID

輸出：

reqID
------
12
14
11
10
9

你想要的是不可能的。想像一下下表：

reqID    RN
   1     1
   2     2
   1     3

你說你想通過 RN 選擇不同的 reqID 和 order，看到問題了嗎？RN 對於 reqID=1 是不明確的，它可以是 1 和 3。您需要以某種方式選擇要使用的 RN。在這個例子中，我為每個 reqID 選擇最小的 RN：

select reqID 
from (
   select req.reqId, min(req.RN) as RN 
   from (
       select ROW_NUMBER() OVER (ORDER BY A.received desc) as RN
            , A.reqId                            
       from RequisitionToPatient B
       join Requisition A 
           on A.reqId = B.reqId
       join Orders C 
           on C.reqId = A.reqId
   ) req
   group by req.reqId
) AS a
order by RN;

這給我們留下了桌子：

reqID    RN
   1     1
   2     2

可以按照RN排序。

我認為您的查詢可以簡化為：

select reqID 
FROM (
   select MAX(A.received) as RN,
          A.reqId as reqId                            
   FROM RequisitionToPatient B
   INNER JOIN Requisition A ON A.reqId = B.reqId
   INNER JOIN Orders C on C.reqId = A.reqId
   group by A.reqId
) req
order by RN;

即不需要用row_number函式列舉，可以直接用received下單。

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