我需要识别并更新与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 |
+------+-----+-----------------+---------+项目和密钥是共同相关的列，DenseRankWrtKey是通过对密钥使用Dense秩来创建的。我需要将相同的DenseBankWrtKey值分配给所有共同相关的值。
场景说明：
项目a具有键值1且1也与c共相关，因此a和1的所有相关值都是a、b、c、f、2、3、7、6、5，因此通过参考DenseBank列，所有这些值都被分配为1，d和e与任何其他值都没有进一步的关系，因此其值保持在DenseBank列中的原样。
我尝试了的查询
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 )
我发现这种方法更容易阅读和维护
DECLARE @TestTable TABLE (Item CHAR(1), ItemKey INT, DenseRankWrtKey INT, Req_Res INT)
INSERT @TestTable (Item, ItemKey, DenseRankWrtKey) VALUES
('a' , 1 , 1)
, ('a' , 2 , 2)
, ('a' , 3 , 3)
, ('b' , 2 , 2)
, ('b' , 9 , 7)
, ('c' , 1 , 1)
, ('c' , 6 , 5)
, ('d' , 5 , 4)
, ('e' , 8 , 6)
, ('f' , 2 , 2)
, ('f' , 6 , 5)
DECLARE @OtpTable TABLE (Item CHAR(1), ItemKey INT, DenseRankWrtKey INT)
DECLARE @RC INT = 1
WHILE @RC > 0
BEGIN
DELETE @OtpTable
;WITH UpdateCTE AS (
SELECT TOP 1 * from @TestTable
WHERE Req_Res IS NULL
)
UPDATE UpdateCTE
set Req_Res = DenseRankWrtKey
OUTPUT Inserted.Item, Inserted.ItemKey, inserted.DenseRankWrtKey INTO @OtpTable
SET @RC = @@ROWCOUNT
WHILE @@ROWCOUNT > 0
UPDATE T
SET Req_Res = (SELECT TOP 1 DenseRankWrtKey FROM @OtpTable)
OUTPUT Inserted.Item, Inserted.ItemKey, inserted.DenseRankWrtKey INTO @OtpTable
FROM @TestTable T
WHERE T.Req_Res IS NULL AND EXISTS (SELECT 1 FROM @OtpTable OT WHERE (T.Item = OT.Item OR T.ItemKey = OT.ItemKey))
END
SELECT * FROM @TestTable
您不能在单个语句中进行更新。
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