[80]更新语句

[霜寒月冷]

---------------  已知表 #tmp_GridResults_1   ---------------
SELECT * INTO #tmp_GridResults_1
FROM (
SELECT N'1' AS [ID], NULL AS [Name],NULL AS [Number] UNION ALL
SELECT N'2' AS [ID], NULL AS [Name], NULL AS [Number] UNION ALL
SELECT N'3' AS [ID], N'1' AS [Name], NULL AS [Number] UNION ALL
SELECT N'4' AS [ID], NULL AS [Name],NULL AS [Number] UNION ALL
SELECT N'5' AS [ID], NULL AS [Name],NULL AS [Number] UNION ALL
SELECT N'6' AS [ID], NULL AS [Name], NULL AS [Number] UNION ALL
SELECT N'7' AS [ID], N'2' AS [Name], NULL AS [Number] UNION ALL
SELECT N'8' AS [ID], NULL AS [Name], NULL AS [Number] UNION ALL
SELECT N'9' AS [ID], NULL AS [Name], NULL AS [Number] UNION ALL
SELECT N'10' AS [ID], NULL AS [Name], NULL AS [Number] UNION ALL
SELECT N'11' AS [ID], N'3' AS [Name], NULL AS [Number] ) t;
SELECT [ID], [Name], [Number]
FROM #tmp_GridResults_1

DROP TABLE #tmp_GridResults_1
GO

-----求更新语句,期待结果---------------------------------
--ID	Name	Number
--1	NULL	1
--2	NULL	1
--3	1	    1
--4	NULL	2
--5	NULL	2
--6	NULL	2
--7	2	    2
--8	NULL	3
--9	NULL	3
--10 NULL	3
--11 3	    3

[mingqing6364]

第一种方案，利用变量，不过不建议使用 这个方案有一定的随机性，我们不能控制UPDATE的顺序

DECLARE @i INT = 1
UPDATE #tmp_GridResults_1 SET
Number = CASE WHEN Name IS NOT NULL THEN @i - 1 ELSE @i END,
@i = @i + CASE WHEN Name IS NOT NULL THEN 1 ELSE 0 END

第二种方案，利用临时表，然后INNER JOIN连接UPDATE

SELECT ID,ROW_NUMBER() OVER(ORDER BY CAST(ID AS INT)) AS Number INTO # FROM #tmp_GridResults_1 WHERE Name IS NOT NULL

UPDATE #tmp_GridResults_1
SET #tmp_GridResults_1.Number = T.Number
FROM #tmp_GridResults_1
INNER JOIN # T ON CAST(#tmp_GridResults_1.ID AS INT) <= CAST(T.ID AS INT)

[mingqing6364]

还有一个建议：如果你的ID字段是纯数字的话，最好用int类型

[中国风]

UPDATE a
SET [Number]=b.Name
FROM #tmp_GridResults_1 AS a
	CROSS APPLY (SELECT TOP 1 Name FROM #tmp_GridResults_1 WHERE ID>=a.ID AND Name IS NOT  NULL ORDER BY ID ASC) AS b

[霜寒月冷]

引用 1 楼 mingqing6364 的回复:

第一种方案，利用变量，不过不建议使用 这个方案有一定的随机性，我们不能控制UPDATE的顺序

DECLARE @i INT = 1
UPDATE #tmp_GridResults_1 SET
Number = CASE WHEN Name IS NOT NULL THEN @i - 1 ELSE @i END,
@i = @i + CASE WHEN Name IS NOT NULL THEN 1 ELSE 0 END

第二种方案，利用临时表，然后INNER JOIN连接UPDATE

SELECT ID,ROW_NUMBER() OVER(ORDER BY CAST(ID AS INT)) AS Number INTO # FROM #tmp_GridResults_1 WHERE Name IS NOT NULL

UPDATE #tmp_GridResults_1
SET #tmp_GridResults_1.Number = T.Number
FROM #tmp_GridResults_1
INNER JOIN # T ON CAST(#tmp_GridResults_1.ID AS INT) <= CAST(T.ID AS INT)

第二种方案OK，我改成cte 了。

[霜寒月冷]

引用 3 楼 roy_88 的回复:

UPDATE a
SET [Number]=b.Name
FROM #tmp_GridResults_1 AS a
	CROSS APPLY (SELECT TOP 1 Name FROM #tmp_GridResults_1 WHERE ID>=a.ID AND Name IS NOT  NULL ORDER BY ID ASC) AS b

结果和我期待的不一样。

[中国风]

引用 5 楼 chz415767975 的回复:

[quote=引用 3 楼 roy_88 的回复:]

UPDATE a
SET [Number]=b.Name
FROM #tmp_GridResults_1 AS a
	CROSS APPLY (SELECT TOP 1 Name FROM #tmp_GridResults_1 WHERE ID>=a.ID AND Name IS NOT  NULL ORDER BY ID ASC) AS b

结果和我期待的不一样。[/quote] ID类型为字符串引起的 转为int就行了

UPDATE a
SET [Number]=b.Name
FROM #tmp_GridResults_1 AS a
	CROSS APPLY (SELECT TOP 1 Name FROM #tmp_GridResults_1 WHERE ID>=a.ID*1 AND Name IS NOT  NULL ORDER BY ID ASC) AS b

SELECT [ID], [Name], [Number]
FROM #tmp_GridResults_1