id type name deviceName
1 2 A
2 3 B
3 4 C
4 2 AA
5 3 FF
6 4 CC
怎么把type = 3資料中name列的值更新到type=2的deviceName中。
也就是id = 1的行 deviceName = B
id = 4的行 deviceName = FF
uj5u.com熱心網友回復：
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(ID INT IDENTITY(1,1),
TYPE INT,
NAME VARCHAR(10),
DEVICENAME VARCHAR(10))
INSERT INTO #T(TYPE,NAME)
SELECT 2,'A' UNION ALL
SELECT 3,'B' UNION ALL
SELECT 4,'C' UNION ALL
SELECT 2,'AA' UNION ALL
SELECT 3,'FF' UNION ALL
SELECT 4,'CC'
GO
WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY ID) AS SEQ FROM #T WHERE TYPE IN (2,3))
UPDATE #T
SET DEVICENAME=D.NAME
FROM #T A
JOIN
(SELECT C.ID,B.NAME
FROM CTE B
JOIN CTE C ON B.TYPE=3 AND C.TYPE=2 AND B.SEQ=C.SEQ) AS D ON A.ID=D.ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/260815.html
標籤：基礎類
上一篇：微信二人實時共享位置修改可以嗎