sql—如何按组递增列

emeijp43 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(333)

我有一张table叫 productLocation ，其数据结构如下，sqlfiddle

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1            | 100         | 0         |
+--------------+-------------+-----------+
| 1            | 101         | 0         |
+--------------+-------------+-----------+
| 1            | 102         | 0         |
+--------------+-------------+-----------+
| 1            | 103         | 2         |
+--------------+-------------+-----------+
| 1            | 104         | 1         |
+--------------+-------------+-----------+
| 1            | 105         | 3         |
+--------------+-------------+-----------+
| 2            | 100         | 0         |
+--------------+-------------+-----------+
| 2            | 101         | 0         |
+--------------+-------------+-----------+
| 2            | 102         | 0         |
+--------------+-------------+-----------+
| 2            | 103         | 1         |
+--------------+-------------+-----------+
| 2            | 104         | 3         |
+--------------+-------------+-----------+
| 2            | 105         | 2         |
+--------------+-------------+-----------+
| 3            | 100         | 0         |
+--------------+-------------+-----------+
| 3            | 101         | 0         |
+--------------+-------------+-----------+
| 3            | 102         | 0         |
+--------------+-------------+-----------+
| 3            | 103         | 1         |
+--------------+-------------+-----------+
| 3            | 104         | 2         |
+--------------+-------------+-----------+

每个地点都有自己的产品分类顺序。但有些产品 0 作为 SortValue 现在我需要写一个查询来更新 SortValue 作为，
如果我考虑一个地点， FkLocationId = 1 ```
+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1 | 100 | 0 |
+--------------+-------------+-----------+
| 1 | 101 | 0 |
+--------------+-------------+-----------+
| 1 | 102 | 0 |
+--------------+-------------+-----------+
| 1 | 103 | 2 |
+--------------+-------------+-----------+
| 1 | 104 | 1 |
+--------------+-------------+-----------+
| 1 | 105 | 3 |
+--------------+-------------+-----------+

在上面的数据表中，你可以看到， `FkProductId = 100,101,102` 有 `0` 作为 `SortValue` . 我需要更新它 `sortValue` 按订单 `FkProductId` 降序排列。我需要更新一下

并逐个更新以前的sortvalue。
那么完整的输出应该是，

+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1 | 100 | 3 |
+--------------+-------------+-----------+
| 1 | 101 | 2 |
+--------------+-------------+-----------+
| 1 | 102 | 1 |
+--------------+-------------+-----------+
| 1 | 103 | 5 |
+--------------+-------------+-----------+
| 1 | 104 | 4 |
+--------------+-------------+-----------+
| 1 | 105 | 6 |
+--------------+-------------+-----------+

这有可能吗？我真的很困惑，请帮我解决这个问题。谢谢您
更新时间：
假设我有另一张table作为产品。其数据结构如下：，

+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100 | 0 |
+-----------+-----------+
| 101 | 0 |
+-----------+-----------+
| 107 | 0 |
+-----------+-----------+
| 108 | 1 |
+-----------+-----------+
| 109 | 2 |
+-----------+-----------+
| 110 | 6 |
+-----------+-----------+
| 111 | 5 |
+-----------+-----------+
| 112 | 4 |
+-----------+-----------+
| 113 | 3 |
+-----------+-----------+

我也需要为这张table做同样的事情，我怎么做呢
预期产量：

+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100 | 3 |
+-----------+-----------+
| 101 | 2 |
+-----------+-----------+
| 107 | 1 |
+-----------+-----------+
| 108 | 4 |
+-----------+-----------+
| 109 | 5 |
+-----------+-----------+
| 110 | 9 |
+-----------+-----------+
| 111 | 8 |
+-----------+-----------+
| 112 | 7 |
+-----------+-----------+
| 113 | 6 |
+-----------+-----------+

sql sql-server sql-update sql-order-by

来源：https://stackoverflow.com/questions/62166993/how-to-increment-column-by-one-group-wise

2条答案

按热度按时间

w46czmvw1#

下面是一个示例查询，您可以使用cte检查并使用相同的逻辑进行更新-
此处演示
重要提示：更新是一个危险的过程，您应该首先尝试测试数据。

WITH CTE
AS
(
    SELECT *,
    (
        SELECT COUNT(*) 
        FROM #Temp B 
        WHERE B.FkLocationId = A.FkLocationId
        AND SortValue = 0
    ) Zero_Count,
    -- The above Zero count is a simple count of rows with 0
    -- for a specific FkLocationId. This is for adding to other 
    -- rows where there are already value in column SortValue. The logic
    -- is simple, the number of row with 0 in column SortValue, 
    -- should be add to existing value where there is value not equal 0.
    ROW_NUMBER() OVER (PARTITION BY FkLocationId ORDER BY SortValue ASC, FkProductId DESC) RN
    -- ROW_NUMBER is simply creating the Number you should replace 0 by.
    -- As you are looking for 1 to start for the MAX FkProductId with 0,
    -- I applied DESC order on that column
    FROM #Temp A
) 

UPDATE A
SET A.SortValue = 
CASE 
    WHEN A.SortValue = 0 THEN B.RN 
    -- RN is created in such way, so that you can directly 
    -- Replace your value in column SortValue by RN if SortValue = 0 
    ELSE A.SortValue + B.Zero_Count
    -- IF SortValue contains already value > 0, 
    -- You need to just increment the value with Number 
    -- of rows is there with value 0. I have calculated that value
    -- by a sub query in the CTE you can check.
END
FROM  #Temp A
INNER JOIN CTE B ON A.FkLocationId = B.FkLocationId
AND A.FkProductId = B.FkProductId

SELECT * FROM #Temp

只要用你的“表名”就行了

赞(0）回复(0）举报 2021-07-26

jucafojl2#

with cte as
(select FkLocationId, FkProductId, SortValue,
ROW_NUMBER() over(partition by FkLocationId order by sortvalue,FkProductId desc) new_sort_value
from productLocation where FkLocationId =1  --and SortValue =0
)

update pl SET
pl.SortValue = c.new_sort_value
from productLocation pl inner join cte c
on pl.FkLocationId = c.FkLocationId and pl.FkProductId = c.FkProductId

Select * from productLocation where FkLocationId =1 order by FkProductId

我所采用的方法的核心是基于行数窗口函数，它将数据按排序值和fkproductid降序地划分到fkproductid上。
有两个查询涉及，虽然可以做的是单一的，但为了简单起见，我使用了公共表表达式（cte）
从查询1派生的cte在第二个更新查询中用作联接表。
我留下了许多自我探索，记住它的必要性，认为在条款的设置。

赞(0）回复(0）举报 2021-07-26

我来回答

sql—如何按组递增列

2条答案

相关问题

热门标签

最新问答