sql - 分区上的 Row_number 并找到最大 rn 值

Question

我从下表开始:

+---+-----+---------+----------+----------+-------------+
| 1 | ID  | OrderNO | PartCode | Quantity | DateEntered |
| 2 | 417 | 2144    | 44917    | 100      | 40773       |
| 3 | 418 | 7235    | 11762    | 5        | 40773       |
| 4 | 419 | 9999    | 60657    | 100      | 40773       |
| 5 | 420 | 9999    | 60657    | 90       | 40774       |
+---+-----+---------+----------+----------+-------------+

为此:

+---+---------+----------+----------+-------------+----+
| 1 | OrderNO | PartCode | Quantity | DateEntered | rn |
| 2 | 2144    | 44917    | 100      | 40773       | 1  |
| 3 | 7235    | 11762    | 5        | 40773       | 1  |
| 4 | 9999    | 60657    | 100      | 40773       | 1  |
| 5 | 9999    | 60657    | 90       | 40774       | 2  |
+---+---------+----------+----------+-------------+----+

使用此 answer 的查询.

select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable) as T

现在，我尝试添加一个字段“rn_max”，它是每个 OrderNO 的最大“rn”并得到以下结果:

+---+---------+----------+----------+-------------+----+--------+
| 1 | OrderNO | PartCode | Quantity | DateEntered | rn | rn_max |
| 2 | 2144    | 44917    | 100      | 40773       | 1  | 1      |
| 3 | 7235    | 11762    | 5        | 40773       | 1  | 1      |
| 4 | 9999    | 60657    | 100      | 40773       | 1  | 2      |
| 5 | 9999    | 60657    | 90       | 40774       | 2  | 2      |
+---+---------+----------+----------+-------------+----+--------+

我可以通过查询计算每个 OrderNO 的最大值得到这个结果

SELECT OrderNO,MAX(rn) AS 'rn_max'
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T
Group by OrderNO

然后使用 YourTable 的左连接。有没有最短的方法来计算具有相同 OderNo 的行？我尝试在查询中添加 row_number() over(partition by OrderNO) as rn_max 但我需要编写一个 order by 子句。

Answer 1

使用max 窗口函数。

SELECT T.*,MAX(rn) OVER(PARTITION BY OrderNo) AS rn_max
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T

编辑:一个更简单的选择是按照@Jason A. Long 在评论中的建议使用count。

select OrderNO
       ,PartCode
       ,Quantity
       ,row_number() over(partition by OrderNO order by DateEntered desc) as rn
       ,count(*) over(partition by OrderNO) as maxrn
from YourTable