[80]更新语句

2016-03-19 02:57:15 298 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

OpenCv案例（七）：基于OpenCvSharp计算图像的清晰度(自动对焦) 自动对焦，其实是对相机成像的清晰值得计算，若对焦不清晰，成像的清晰度低，视觉效果模糊。若是在工业检测行业，对焦不准确，可能会造成严重后果；对焦准确的图像，其清晰度高，对比度鲜明，层次明显。阅读详情

---------------  已知表 #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

...全文

Mac M1安装Hive 如果你确定装过mysql，但是执行上述命令后，发现不存在mysql命令，那说明你的系统环境没有配置。出现一段空白，接着出现Initialization script completed。关闭hiveServer2时，执行如下命令查看hive进程；执行上面命令后，若有结果输出，则证明配置完成了。如果出现mac权限问题，解决方法参考。就可以见到hive的web界面了。若出现路径，则存在mysql。配置好后保存，使配置文件生效。用户名hive，密码hive。用户名通过如下命令查看。通过如下命令杀死该进程。阅读详情

霜寒月冷

community_281 706 成员 253,687 内容

+ 关注 + 加入社区

切换为时间正序

中国风 2016-03-19

打赏
举报

引用 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

霜寒月冷 2016-03-19

打赏
举报

引用 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

结果和我期待的不一样。

霜寒月冷 2016-03-19

打赏
举报

引用 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 了。

中国风 2016-03-19

打赏
举报

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

mingqing6364 2016-03-19

打赏
举报

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

mingqing6364 2016-03-19

打赏
举报

第一种方案，利用变量，不过不建议使用这个方案有一定的随机性，我们不能控制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)

[80]更新语句

相关推荐