学习 通过游标实现以下更改 谢谢
序号低保编号 姓名
13711000102080001曹现菊
2 宋祥华
3 宋志昊
13711000102080002陈斌
2 高丽
3 陈宇
13711000102080003刘田信
2 郁桂英
3 刘婷婷
4 刘源
13711000102080004鲁彬
2 徐翠霞
。。。
实现如下
序号低保编号姓名
13711000102080001曹现菊
23711000102080001宋祥华
33711000102080001宋志昊
13711000102080002陈斌
23711000102080002高丽
33711000102080002陈宇
13711000102080003刘田信
23711000102080003郁桂英
33711000102080003刘婷婷
43711000102080003刘源
13711000102080004鲁彬
23711000102080004徐翠霞
。。。
[解决办法]
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([序号] INT,[低保编号] VARCHAR(16),[姓名] VARCHAR(6))INSERT [tb]SELECT 1,'3711000102080001','曹现菊' UNION ALLSELECT 2,NULL,'宋祥华' UNION ALLSELECT 3,NULL,'宋志昊' UNION ALLSELECT 1,'3711000102080002','陈斌' UNION ALLSELECT 2,NULL,'高丽' UNION ALLSELECT 3,NULL,'陈宇' UNION ALLSELECT 1,'3711000102080003','刘田信' UNION ALLSELECT 2,NULL,'郁桂英' UNION ALLSELECT 3,NULL,'刘婷婷' UNION ALLSELECT 4,NULL,'刘源' UNION ALLSELECT 1,'3711000102080004','鲁彬' UNION ALLSELECT 2,NULL,'徐翠霞'--------------开始查询--------------------------;WITH cte AS(SELECT *,row=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM [tb])SELECT [序号],低保编号=ISNULL(低保编号,(SELECT TOP 1 低保编号 FROM cte WHERE 低保编号 IS NOT NULL AND row<t.row ORDER BY row DESC)),姓名 FROM cte t----------------结果----------------------------/* 序号 低保编号 姓名----------- ---------------- ------1 3711000102080001 曹现菊2 3711000102080001 宋祥华3 3711000102080001 宋志昊1 3711000102080002 陈斌2 3711000102080002 高丽3 3711000102080002 陈宇1 3711000102080003 刘田信2 3711000102080003 郁桂英3 3711000102080003 刘婷婷4 3711000102080003 刘源1 3711000102080004 鲁彬2 3711000102080004 徐翠霞(12 行受影响)*/
[解决办法]
非游标不行吗？
[解决办法]
- SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([序号] int,[低保编号] varchar(16),[姓名] varchar(6))insert [test]select 1,'3711000102080001','曹现菊' union allselect 2,null,'宋祥华' union allselect 3,null,'宋志昊' union allselect 1,'3711000102080002','陈斌' union allselect 2,null,'高丽' union allselect 3,null,'陈宇' union allselect 1,'3711000102080003','刘田信' union allselect 2,null,'郁桂英' union allselect 3,null,'刘婷婷' union allselect 4,null,'刘源' union allselect 1,'3711000102080004','鲁彬' union allselect 2,null,'徐翠霞'alter table test add id int identitygowith tas(select ROW_NUMBER()over(order by getdate())-[序号] as px, * from test), m as(select px, [序号], ISNULL([低保编号],(select [低保编号] from t b where [序号]=1 and a.px=b.px )) [低保编号], [姓名]from t a)update test set test.[低保编号]=a.[低保编号] from m a where test.id=a.px+a.序号goalter table test drop column idgoselect * from test/*序号 低保编号 姓名-----------------------------------1 3711000102080001 曹现菊2 3711000102080001 宋祥华3 3711000102080001 宋志昊1 3711000102080002 陈斌2 3711000102080002 高丽3 3711000102080002 陈宇1 3711000102080003 刘田信2 3711000102080003 郁桂英3 3711000102080003 刘婷婷4 3711000102080003 刘源1 3711000102080004 鲁彬2 3711000102080004 徐翠霞*/不到万不得已不要用游标，游标性能很低
[解决办法]
基于游标的解决方案。
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([序号] INT,[低保编号] VARCHAR(16),[姓名] VARCHAR(6))INSERT [tb]SELECT 1,'3711000102080001','曹现菊' UNION ALLSELECT 2,NULL,'宋祥华' UNION ALLSELECT 3,NULL,'宋志昊' UNION ALLSELECT 1,'3711000102080002','陈斌' UNION ALLSELECT 2,NULL,'高丽' UNION ALLSELECT 3,NULL,'陈宇' UNION ALLSELECT 1,'3711000102080003','刘田信' UNION ALLSELECT 2,NULL,'郁桂英' UNION ALLSELECT 3,NULL,'刘婷婷' UNION ALLSELECT 4,NULL,'刘源' UNION ALLSELECT 1,'3711000102080004','鲁彬' UNION ALLSELECT 2,NULL,'徐翠霞'--------------开始查询--------------------------DECLARE @id INT,@bh VARCHAR(16),@name VARCHAR(10),@bh2 VARCHAR(16)DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATICFOR SELECT [序号],[低保编号],[姓名] FROM tb ORDER BY GETDATE()OPEN cFETCH NEXT FROM c INTO @id, @bh,@nameWHILE @@fetch_status = 0BEGIN IF @bh IS NOT NULL BEGIN PRINT @BH SET @bh2=@bh END UPDATE tb SET [低保编号] =@bh2 WHERE [序号]=@id AND [低保编号] IS NULL AND [姓名]=@name FETCH NEXT FROM c INTO @id, @bh,@nameEND CLOSE cDEALLOCATE cSELECT * FROM tb
[解决办法]
目前为止本人没遇到过非要用游标才能解决的数据库问题，而且用游标在生产环境几乎“一定”会产生性能问题，轻则运行缓慢，重则服务器宕机。切记切记。while循环也是类似，能不用就不用，游标其实可以使用CTE来替代的。详细请查阅联机丛书