sql - 如何根据每个项目组的排序项目数使用 SQL 插入或更新列

Question

我有两个表'Product'和'product_Desc'

+-----------+-------------+
| ProductID | ProductName |
+-----------+-------------+
|         1 | A           |
|         2 | B           |
+-----------+-------------+
    


      +----+-----------+-------------+-----------+
| Id | ProductID | ProductDec  | SortOrder |
+----+-----------+-------------+-----------+
|  1 |         1 | Aero-pink   |           |
|  2 |         1 | Aero-white  |           |
|  3 |         1 | Aero-green  |           |
|  4 |         1 | Aero-Orange |           |
|  5 |         2 | Baloon-1    |           |
|  6 |         2 | Baloon-2    |           |
|  7 |         2 | Baloon-3    |           |
+----+-----------+-------------+-----------+
        

现在，可以为每组 ProductID 顺序更新 'sortOrder' 列的 Sql 代码是什么，如下所示：

    +----+-----------+-------------+-----------+
    | Id | ProductID | ProductDec  | SortOrder |
    +----+-----------+-------------+-----------+
    |  1 |         1 | Aero-pink   |         1 |
    |  2 |         1 | Aero-white  |         2 |
    |  3 |         1 | Aero-green  |         3 |
    |  4 |         1 | Aero-Orange |         4 |
    |  5 |         2 | Baloon-1    |         1 |
    |  6 |         2 | Baloon-2    |         2 |
    |  7 |         2 | Baloon-3    |         3 |
    +----+-----------+-------------+-----------+

请注意，这些是示例表，实际表有数千条记录。非常感谢您对此的帮助。谢谢

标签： sqlsql-server

Answer 1

with cte
as
(
select SortOrder, row_number() over(partition by ProductID order by Id) as newPerProductOrder 
from product_Desc
)
update cte
set SortOrder = newPerProductOrder
where (SortOrder <> newPerProductOrder or SortOrder is null)