如何明智地按一组增加列 [英] How to increment column by one group wise
本文介绍了如何明智地按一组增加列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个名为productLocation的表，它的数据结构如下，SQLFiddle
+--------------+-------------+-----------+|FkLocationId |FkProductId |排序值 |+--------------+-------------+------------+|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 |排序值 |+--------------+-------------+------------+|1 |100 |0 |+--------------+-------------+------------+|1 |101 |0 |+--------------+-------------+------------+|1 |102 |0 |+--------------+-------------+------------+|1 |103 |2 |+--------------+-------------+------------+|1 |104 |1 |+--------------+-------------+------------+|1 |105 |3 |+--------------+-------------+------------+在上面的数据表中，可以看到，FkProductId = 100,101,102有0作为SortValue.我需要按 FkProductId 降序更新它的 sortValue .我需要更新它像
+--------------+-------------+-----------+|FkLocationId |FkProductId |排序值 |+--------------+-------------+------------+|1 |100 |3 |+--------------+-------------+------------+|1 |101 |2 |+--------------+-------------+------------+|1 |102 |1 |+--------------+-------------+------------+并且也一一更新之前的SortValue.
那么完整的输出应该是，
+--------------+-------------+-----------+|FkLocationId |FkProductId |排序值 |+--------------+-------------+------------+|1 |100 |3 |+--------------+-------------+------------+|1 |101 |2 |+--------------+-------------+------------+|1 |102 |1 |+--------------+-------------+------------+|1 |103 |5 |+--------------+-------------+------------+|1 |104 |4 |+--------------+-------------+------------+|1 |105 |6 |+--------------+-------------+------------+这可以吗?我真的很困惑，请帮我解决这个问题.谢谢
更新:
假设我有另一张桌子作为产品.其数据结构如下，
+-----------+-----------+|产品编号 |排序值 |+-----------+-----------+|100 |0 |+-----------+-----------+|101 |0 |+-----------+-----------+|107 |0 |+-----------+-----------+|108 |1 |+-----------+-----------+|109 |2 |+-----------+-----------+|110 |6 |+-----------+-----------+|111 |5 |+-----------+-----------+|112 |4 |+-----------+-----------+|113 |3 |+-----------+-----------+我也需要为这张桌子做同样的事情，我该怎么做
预期输出:
+-----------+-----------+|产品编号 |排序值 |+-----------+-----------+|100 |3 |+-----------+-----------+|101 |2 |+-----------+-----------+|107 |1 |+-----------+-----------+|108 |4 |+-----------+-----------+|109 |5 |+-----------+-----------+|110 |9 |+-----------+-----------+|111 |8 |+-----------+-----------+|112 |7 |+-----------+-----------+|113 |6 |+-----------+-----------+ 解决方案
以下是一个示例查询，您可以使用 CTE- 来检查和使用相同的逻辑进行更新过程
重要说明:更新是一个有风险的过程，您应该先尝试使用您的测试数据.
带 CTE作为(选择 *，(选择 COUNT(*)来自#Temp BWHERE B.FkLocationId = A.FkLocationIdAND SortValue = 0) 零计数，-- 上面的零计数是简单的计数为 0 的行-- 对于特定的 FkLocationId.这是为了添加到其他-- SortValue 列中已有值的行.逻辑-- 很简单，SortValue列中有0的行数，-- 应添加到现有值中，其中值不等于 0.ROW_NUMBER() OVER (PARTITION BY FkLocationId ORDER BY SortValue ASC, FkProductId DESC) RN-- ROW_NUMBER 只是创建您应该替换 0 的数字.-- 当您正在寻找 1 以 0 为 MAX FkProductId 开始时，-- 我在该列上应用了 DESC 顺序来自#Temp A)更新ASET A.SortValue =案件当 A.SortValue = 0 然后 B.RN-- RN就是这样创建的，这样你就可以直接-- 如果 SortValue = 0，则将 SortValue 列中的值替换为 RNELSE A.SortValue + B.Zero_Count-- 如果 SortValue 已经包含值 >0,-- 你只需要用 Number 增加值-- 行的值为 0.我已经计算了该值-- 通过 CTE 中的子查询，您可以检查.结束来自#Temp AA.FkLocationId 上的内部连接 CTE B = B.FkLocationIdAND A.FkProductId = B.FkProductIdSELECT * FROM #Temp<块引用>
在我使用过#temp 的地方使用你的table_name"
I have a table called productLocation, and its data structure as follows, 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 |
+--------------+-------------+-----------+
For each location have its own sorting order for products. But some of the products have 0 as the SortValue
Now I need to write a query to update SortValue as,
If I consider one location, FkLocationId = 1
+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1 | 100 | 0 |
+--------------+-------------+-----------+
| 1 | 101 | 0 |
+--------------+-------------+-----------+
| 1 | 102 | 0 |
+--------------+-------------+-----------+
| 1 | 103 | 2 |
+--------------+-------------+-----------+
| 1 | 104 | 1 |
+--------------+-------------+-----------+
| 1 | 105 | 3 |
+--------------+-------------+-----------+
In the above data table, you can see, FkProductId = 100,101,102 have 0 as the SortValue. I need to update its sortValue as order by FkProductId descending order. I need to update its like
+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1 | 100 | 3 |
+--------------+-------------+-----------+
| 1 | 101 | 2 |
+--------------+-------------+-----------+
| 1 | 102 | 1 |
+--------------+-------------+-----------+
And also update the previous SortValue by one by one.
Then the complete output should be,
+--------------+-------------+-----------+
| FkLocationId | FkProductId | SortValue |
+--------------+-------------+-----------+
| 1 | 100 | 3 |
+--------------+-------------+-----------+
| 1 | 101 | 2 |
+--------------+-------------+-----------+
| 1 | 102 | 1 |
+--------------+-------------+-----------+
| 1 | 103 | 5 |
+--------------+-------------+-----------+
| 1 | 104 | 4 |
+--------------+-------------+-----------+
| 1 | 105 | 6 |
+--------------+-------------+-----------+
Is this possible to do? I'm really confused about this, please help me to solve this. Thank you
Updated:
Suppose I have another table as product. And its data structure as follows,
+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100 | 0 |
+-----------+-----------+
| 101 | 0 |
+-----------+-----------+
| 107 | 0 |
+-----------+-----------+
| 108 | 1 |
+-----------+-----------+
| 109 | 2 |
+-----------+-----------+
| 110 | 6 |
+-----------+-----------+
| 111 | 5 |
+-----------+-----------+
| 112 | 4 |
+-----------+-----------+
| 113 | 3 |
+-----------+-----------+
I also need to do same thing for this table as well, How can I do it
Expected Output:
+-----------+-----------+
| ProdcutId | SortValue |
+-----------+-----------+
| 100 | 3 |
+-----------+-----------+
| 101 | 2 |
+-----------+-----------+
| 107 | 1 |
+-----------+-----------+
| 108 | 4 |
+-----------+-----------+
| 109 | 5 |
+-----------+-----------+
| 110 | 9 |
+-----------+-----------+
| 111 | 8 |
+-----------+-----------+
| 112 | 7 |
+-----------+-----------+
| 113 | 6 |
+-----------+-----------+
解决方案
Following is a sample query for you to check and use the same logic for your update process using CTE-
Important Note: Update is a risky process and you should try with your test data first.
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
Just use your "table_name" where I have used #temp
这篇关于如何明智地按一组增加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
