序号 低保编号 姓名
1 3711000102080001 曹现菊
2 宋祥华
3 宋志昊
1 3711000102080002 陈斌
2 高丽
3 陈宇
1 3711000102080003 刘田信
2 郁桂英
3 刘婷婷
4 刘源
1 3711000102080004 鲁彬
2 徐翠霞
实现如下
序号 低保编号 姓名
1 3711000102080001 曹现菊
2 3711000102080001 宋祥华
3 3711000102080001 宋志昊
1 3711000102080002 陈斌
2 3711000102080002 高丽
3 3711000102080002 陈宇
1 3711000102080003 刘田信
2 3711000102080003 郁桂英
3 3711000102080003 刘婷婷
4 3711000102080003 刘源
1 3711000102080004 鲁彬
2 3711000102080004 徐翠霞
1 3711000102080001 曹现菊
2 宋祥华
3 宋志昊
1 3711000102080002 陈斌
2 高丽
3 陈宇
1 3711000102080003 刘田信
2 郁桂英
3 刘婷婷
4 刘源
1 3711000102080004 鲁彬
2 徐翠霞
实现如下
序号 低保编号 姓名
1 3711000102080001 曹现菊
2 3711000102080001 宋祥华
3 3711000102080001 宋志昊
1 3711000102080002 陈斌
2 3711000102080002 高丽
3 3711000102080002 陈宇
1 3711000102080003 刘田信
2 3711000102080003 郁桂英
3 3711000102080003 刘婷婷
4 3711000102080003 刘源
1 3711000102080004 鲁彬
2 3711000102080004 徐翠霞
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 ALL
SELECT 2,NULL,'宋祥华' UNION ALL
SELECT 3,NULL,'宋志昊' UNION ALL
SELECT 1,'3711000102080002','陈斌' UNION ALL
SELECT 2,NULL,'高丽' UNION ALL
SELECT 3,NULL,'陈宇' UNION ALL
SELECT 1,'3711000102080003','刘田信' UNION ALL
SELECT 2,NULL,'郁桂英' UNION ALL
SELECT 3,NULL,'刘婷婷' UNION ALL
SELECT 4,NULL,'刘源' UNION ALL
SELECT 1,'3711000102080004','鲁彬' UNION ALL
SELECT 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 行受影响)*/
--> 测试数据:[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 all
select 2,null,'宋祥华' union all
select 3,null,'宋志昊' union all
select 1,'3711000102080002','陈斌' union all
select 2,null,'高丽' union all
select 3,null,'陈宇' union all
select 1,'3711000102080003','刘田信' union all
select 2,null,'郁桂英' union all
select 3,null,'刘婷婷' union all
select 4,null,'刘源' union all
select 1,'3711000102080004','鲁彬' union all
select 2,null,'徐翠霞'
alter table test add id int identity
go
with t
as(
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.序号go
alter table test drop column id
go
select * 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 徐翠霞
*/不到万不得已不要用游标,游标性能很低
基于游标的解决方案。
--> 测试数据:[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 ALL
SELECT 2,NULL,'宋祥华' UNION ALL
SELECT 3,NULL,'宋志昊' UNION ALL
SELECT 1,'3711000102080002','陈斌' UNION ALL
SELECT 2,NULL,'高丽' UNION ALL
SELECT 3,NULL,'陈宇' UNION ALL
SELECT 1,'3711000102080003','刘田信' UNION ALL
SELECT 2,NULL,'郁桂英' UNION ALL
SELECT 3,NULL,'刘婷婷' UNION ALL
SELECT 4,NULL,'刘源' UNION ALL
SELECT 1,'3711000102080004','鲁彬' UNION ALL
SELECT 2,NULL,'徐翠霞'
--------------开始查询--------------------------
DECLARE @id INT,@bh VARCHAR(16),@name VARCHAR(10),@bh2 VARCHAR(16)
DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATIC
FOR
SELECT [序号],[低保编号],[姓名] FROM tb ORDER BY GETDATE()
OPEN c
FETCH NEXT FROM c INTO @id, @bh,@name
WHILE @@fetch_status = 0
BEGIN
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,@name
END
CLOSE c
DEALLOCATE cSELECT * FROM tb