时间戳

首页 > 解决方案 > 更新组中两列之间相关记录的唯一编号

sql - 更新组中两列之间相关记录的唯一编号

问题描述

我需要在 Req_Result 列下识别和更新相关的相关记录，如下图所示。表名是 tblSource。

+------+-----+-----------------+---------+
| Item | key | DenseRankWrtKey | Req_Res |
+------+-----+-----------------+---------+
| a    | 1   | 1               | 1       |
+------+-----+-----------------+---------+
| a    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| a    | 3   | 3               | 1       |
+------+-----+-----------------+---------+
| b    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| b    | 9   | 7               | 1       |
+------+-----+-----------------+---------+
| c    | 1   | 1               | 1       |
+------+-----+-----------------+---------+
| c    | 6   | 5               | 1       |
+------+-----+-----------------+---------+
| d    | 5   | 4               | 4       |
+------+-----+-----------------+---------+
| e    | 8   | 6               | 6       |
+------+-----+-----------------+---------+
| f    | 2   | 2               | 1       |
+------+-----+-----------------+---------+
| f    | 6   | 5               | 1       |
+------+-----+-----------------+---------+

Item和Key是相互关联的列，DenseRankWrtKey 是通过使用 Dense rank 相对于 key 创建的。我需要为所有相关的值分配相同的 DenseRankWrtKey 值。

场景解释：

项目a 的键值为1，并且1也与c相关，因此a和1的所有相关值都是a 、b、c、f、2、3、7、6、5，因此所有这些值都被分配通过引用DenseRank列作为1 ， d和e与任何其他值没有进一步的关系，因此其值与DenseRank列保持不变。

我尝试了查询

Update a 
SET a.Req_Res = b.DenseRankWrtKey 
from tblSource a 
inner join tblSource b on a.DenseRankWrtKey = b.DenseRankWrtKey

这是不够的。

也试试这张桌子： DECLARE @Table AS TABLE ( Id INT IDENTITY(1,1) PRIMARY KEY ,Item varchar(100) ,[key] INT ,DenseRankWrtKey INT ,Req_Res INT ) INSERT INTO @Table ( Item ,[key] ,DenseRankWrtKey ) VALUES ('p', 10 ,1 ), ('q', 10 ,1 ), ('r', 20 ,2 ), ('s', 30 ,3 ), ('t', 30 ,3 ), ('u', 40 ,4 ), ('v', 40 ,4 ), ('w', 40 ,4 ), ('p', 50 ,5 ), ('q', 50 ,5 ), ('r', 50 ,5 ), ('s', 50 ,5 ), ('t', 50 ,5 ), ('u', 50 ,5 ), ('v', 50 ,5 ), ('w', 50 ,5 )

标签： sqlsql-server

解决方案

您不能在单个语句中进行更新。

CREATE TABLE #Table 
(
    Id INT 
    ,Item varchar(30)
    ,[key] INT
    ,DenseRankWrtKey INT
    ,Req_Res INT
)

INSERT INTO #Table
(
    Item
    ,[key]
    ,DenseRankWrtKey
)
VALUES
<YOUR DATA>


;WITH CTE
AS
(
    SELECT
        T.Item
        ,T.[Key]
        ,Id = RANK() OVER(order by T.DenseRankWrtKey,T.Item)
    FROM 
        #Table AS T
)
UPDATE
    T
SET
    T.Id = CTE.Id
FROM
    CTE 
    INNER JOIN #Table AS T ON T.Item = CTE.Item AND T.[key] = CTE.[key]

DECLARE @LoopVal INT = 0
        ,@LoopReq INT = NULL
        ,@LoopKey VARCHAR(50) = NULL

WHILE 1 = 1
BEGIN

    SELECT TOP 1
        @LoopVal = T.DenseRankWrtKey
        ,@LoopReq = T.Req_Res
    FROM
        #Table AS T
    WHERE
        T.DenseRankWrtKey > @LoopVal
    ORDER BY
        T.DenseRankWrtKey ASC

    IF @@ROWCOUNT = 0
        BREAK;

    UPDATE T2
    SET Req_Res = CASE WHEN @LoopReq IS NOT NULL THEN @LoopReq ELSE T.DenseRankWrtKey END
    FROM 
        #Table AS T
        INNER JOIN #Table AS T2 ON T.[key] = T2.[key]
    WHERE
        T.DenseRankWrtKey = @LoopVal
        AND T2.Req_Res IS NULL

    UPDATE
        T
    SET
        T.Req_Res = CASE WHEN @LoopReq IS NOT NULL THEN @LoopReq ELSE T2.Req_Res END 
    FROM 
        #Table AS T
        INNER JOIN #Table AS T2 ON T.Item = T2.Item
                                    AND T2.Req_Res IS NOT NULL
                                    AND T.Req_Res IS NULL

END
SELECT * FROM #Table
ORDER BY
    DenseRankWrtKey
DROP TABLE #Table
GO

推荐阅读