我需要分区上的 Row_number 才能在 SQL Server 中实现此目的

WITH cte AS (选择pid,处理日期，应用号，ROW_NUMBER() OVER(PARTITION BY pid, CAST(ProcessedDate AS DATE) ORDER BY ProcessedDate) rn从 mytable)更新 cte SET AppNo = rnDB Fiddle 演示原始数据:PID |应用号 |处理日期--: |----: |:--------------11 |空 |09/30/2019 18:213 |空 |09/25/2019 08:373 |空 |09/25/2019 08:3711 |空 |09/25/2019 08:3911 |空 |09/25/2019 08:407 |空 |09/26/2019 14:197 |空 |09/26/2019 14:207 |空 |09/26/2019 14:222 |空 |09/26/2019 14:2311 |空 |09/26/2019 14:2311 |空 |09/26/2019 14:2411 |空 |09/26/2019 14:243 |空 |09/26/2019 14:24运行查询后:PID |应用号 |处理日期--: |----: |:--------------11 |1 |09/30/2019 18:213 |1 |09/25/2019 08:373 |2 |09/25/2019 08:3711 |1 |09/25/2019 08:3911 |2 |09/25/2019 08:407 |1 |09/26/2019 14:197 |2 |09/26/2019 14:207 |3 |09/26/2019 14:222 |1 |09/26/2019 14:2311 |1 |09/26/2019 14:2311 |2 |09/26/2019 14:2411 |3 |09/26/2019 14:243 |1 |09/26/2019 14:24I have three columnsPID, AppNo and ProcessedDateI need a query to update the AppNo in the format belowPID AppNo ProcessedDate11 1 09/30/2019 18:213 1 09/25/2019 08:373 2 09/25/2019 08:3711 1 09/25/2019 08:3911 2 09/25/2019 08:407 1 09/26/2019 14:197 2 09/26/2019 14:207 3 09/26/2019 14:222 1 09/26/2019 14:2311 1 09/26/2019 14:2311 2 09/26/2019 14:2411 3 09/26/2019 14:243 1 09/26/2019 14:24For now the AppNo column is null.This is the sql that is not workingSELECT AppNo, ProcessedDate,pid,Row_Number() OVER(PARTITION BY pid, ProcessedDate ORDER BY ProcessedDate) AS rnselect * FROM table 解决方案 You seem to be looking to update your original table. You can use ROW_NUMBER() in a CTE to rank records with groups having the same date (without time) and pid, ordered by date (with time) and then do the update on the fly:WITH cte AS ( SELECT pid, ProcessedDate, AppNo, ROW_NUMBER() OVER(PARTITION BY pid, CAST(ProcessedDate AS DATE) ORDER BY ProcessedDate) rn FROM mytable)UPDATE cte SET AppNo = rnDemo on DB FiddleOriginal data:PID | AppNo | ProcessedDate --: | ----: | :--------------- 11 | null | 09/30/2019 18:21 3 | null | 09/25/2019 08:37 3 | null | 09/25/2019 08:37 11 | null | 09/25/2019 08:39 11 | null | 09/25/2019 08:40 7 | null | 09/26/2019 14:19 7 | null | 09/26/2019 14:20 7 | null | 09/26/2019 14:22 2 | null | 09/26/2019 14:23 11 | null | 09/26/2019 14:23 11 | null | 09/26/2019 14:24 11 | null | 09/26/2019 14:24 3 | null | 09/26/2019 14:24After running the query:PID | AppNo | ProcessedDate --: | ----: | :--------------- 11 | 1 | 09/30/2019 18:21 3 | 1 | 09/25/2019 08:37 3 | 2 | 09/25/2019 08:37 11 | 1 | 09/25/2019 08:39 11 | 2 | 09/25/2019 08:40 7 | 1 | 09/26/2019 14:19 7 | 2 | 09/26/2019 14:20 7 | 3 | 09/26/2019 14:22 2 | 1 | 09/26/2019 14:23 11 | 1 | 09/26/2019 14:23 11 | 2 | 09/26/2019 14:24 11 | 3 | 09/26/2019 14:24 3 | 1 | 09/26/2019 14:24 这篇关于我需要分区上的 Row_number 才能在 SQL Server 中实现此目的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！