ROW_NUMBER在具有重复Order By值的PARTITION上生成

Question

我正在访问SQL Server中的一个视图，该视图通过应用程序存储如下

SELECT *
FROM
(
VALUES 
(1, 'Open', { ts '2020-01-10 23:56:12' }), 
(1, 'Started', { ts '2020-01-10 23:56:12' }), 
(1, 'Hold', { ts '2020-01-10 23:56:12' }), 
(1, 'Sent', { ts '2020-01-11 15:33:22' }), 
(1, 'Complete', { ts '2020-01-11 15:36:22' }), 
(1, 'Closed', { ts '2020-01-13 16:43:33' }), 
(2, 'Open', { ts '2020-02-22 06:43:33' }), 
(2, 'Started', { ts '2020-02-22 06:43:33' }), 
(2, 'Clarify', { ts '2020-03-23 08:33:53' }), 
(2, 'Closed', { ts '2020-03-24 08:33:53' })
) t (WO, WO_Status, WO_Status_Date)

这将生成以下

| WO | WO_Status | WO_Status_Date          |
+----+-----------+-------------------------+
| 1  | Open      | 2020-01-10 23:56:12.000 |
| 1  | Started   | 2020-01-10 23:56:12.000 |
| 1  | Hold      | 2020-01-10 23:56:12.000 |
| 1  | Sent      | 2020-01-11 15:33:22.000 |
| 1  | Complete  | 2020-01-11 15:36:22.000 |
| 1  | Closed    | 2020-01-13 16:43:33.000 |
| 2  | Open      | 2020-02-22 06:43:33.000 |
| 2  | Started   | 2020-02-22 06:43:33.000 |
| 2  | Clarify   | 2020-03-23 08:33:53.000 |
| 2  | Closed    | 2020-03-24 08:33:53.000 |

我只想按照行的固有顺序创建一个行号/WO。

所以我想要的输出是

| WO | WO_Status | WO_Status_Date          | Order |
+----+-----------+-------------------------+-------+
| 1  | Open      | 2020-01-10 23:56:12.000 | 1     |
| 1  | Started   | 2020-01-10 23:56:12.000 | 2     |
| 1  | Hold      | 2020-01-10 23:56:12.000 | 3     |
| 1  | Sent      | 2020-01-11 15:33:22.000 | 4     |
| 1  | Complete  | 2020-01-11 15:36:22.000 | 5     |
| 1  | Closed    | 2020-01-13 16:43:33.000 | 6     |
| 2  | Open      | 2020-02-22 06:43:33.000 | 1     |
| 2  | Started   | 2020-02-22 06:43:33.000 | 2     |
| 2  | Clarify   | 2020-03-23 08:33:53.000 | 3     |
| 2  | Closed    | 2020-03-24 08:33:53.000 | 4     |

为了做到这一点，我尝试了以下

SELECT
a.*,
ROW_NUMBER() OVER (PARTITION BY (a.[WO]) ORDER BY a.[WO_Status_Date] ASC) AS [Natural Order]
FROM
(SELECT *
FROM
(VALUES 
(1, 'Open', { ts '2020-01-10 23:56:12' }), 
(1, 'Started', { ts '2020-01-10 23:56:12' }), 
(1, 'Hold', { ts '2020-01-10 23:56:12' }), 
(1, 'Sent', { ts '2020-01-11 15:33:22' }), 
(1, 'Complete', { ts '2020-01-11 15:36:22' }), 
(1, 'Closed', { ts '2020-01-13 16:43:33' }), 
(2, 'Open', { ts '2020-02-22 06:43:33' }), 
(2, 'Started', { ts '2020-02-22 06:43:33' }), 
(2, 'Clarify', { ts '2020-03-23 08:33:53' }), 
(2, 'Closed', { ts '2020-03-24 08:33:53' })
) t (WO, WO_Status, WO_Status_Date)
) a

生成以下结果：

| WO | WO_Status | WO_Status_Date          | Natural Order |
+----+-----------+-------------------------+---------------+
| 1  | Started   | 2020-01-10 23:56:12.000 | 1             |
| 1  | Hold      | 2020-01-10 23:56:12.000 | 2             |
| 1  | Open      | 2020-01-10 23:56:12.000 | 3             |
| 1  | Sent      | 2020-01-11 15:33:22.000 | 4             |
| 1  | Complete  | 2020-01-11 15:36:22.000 | 5             |
| 1  | Closed    | 2020-01-13 16:43:33.000 | 6             |
| 2  | Started   | 2020-02-22 06:43:33.000 | 1             |
| 2  | Open      | 2020-02-22 06:43:33.000 | 2             |
| 2  | Clarify   | 2020-03-23 08:33:53.000 | 3             |
| 2  | Closed    | 2020-03-24 08:33:53.000 | 4             |

ORDER BY正在覆盖具有重复值的行的固有顺序。有没有一种方法可以通过这个获得想要的结果。

Answer 1

首先，您应该知道，当涉及到关系数据库时，没有"自然顺序"这回事。这是一种误解
事实上，这是一个常见的误解，我曾写过一篇关于它的博客文章，因为尽管它被许多网站上的许多专业人士多次驳斥，但仍然有太多的开发人员成为这种误解的受害者。

话虽如此，我假设不同的状态应该按照业务逻辑顺序规则进行排序，这可以使用包含列的状态表进行排序，也可以使用order by子句中的case表达式进行排序。

这个SQL语句会给你想要的结果(假设我正确地得到了不同状态的顺序(：

SELECT  WO, 
WO_Status, 
WO_Status_Date,
ROW_NUMBER() OVER(PARTITION BY WO ORDER BY 
WO_Status_Date, 
CASE WO_Status 
WHEN 'Open' Then 1
WHEN 'Started' Then 2
WHEN 'Hold' Then 3
WHEN 'Clarify' Then 4
WHEN 'Sent' Then 5
WHEN 'Complete' Then 6
WHEN 'Closed' Then 7
END) As [Natural Order]
FROM
(
VALUES 
(1, 'Open', { ts '2020-01-10 23:56:12' }), 
(1, 'Started', { ts '2020-01-10 23:56:12' }), 
(1, 'Hold', { ts '2020-01-10 23:56:12' }), 
(1, 'Sent', { ts '2020-01-11 15:33:22' }), 
(1, 'Complete', { ts '2020-01-11 15:36:22' }), 
(1, 'Closed', { ts '2020-01-13 16:43:33' }), 
(2, 'Open', { ts '2020-02-22 06:43:33' }), 
(2, 'Started', { ts '2020-02-22 06:43:33' }), 
(2, 'Clarify', { ts '2020-03-23 08:33:53' }), 
(2, 'Closed', { ts '2020-03-24 08:33:53' })
) t (WO, WO_Status, WO_Status_Date)