coop name idno id
001 邓卫红 1 1
null 张宝香 null 2
null 邓鑫 null 3
002 邓洪锋 1 4
null 田华 null 5
null 范香来 null 6-------解释:COOP不为NULL 表示是户主,家庭序号IDNO='1' ,ID是流水号
我想通过游标处理返回结果为:
coop name idno id
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6我写的这个能更新coopmedcode 但是更新IDNO 有问题
不知道原因 请教高手
declare @id bigint,@coopmedcode varchar(15) ,@coopmedcode2 varchar(15),@idno smallint,@idno2 smallint;
declare cur cursor fast_forward for
select top 100 id,coopmedcode,idno from tmpcbnhgrxx2 order by id;
open cur;
fetch next from cur into @id,@coopmedcode,@idno;
while @@fetch_status=0
begin
if len(@coopmedcode)>=1
begin
set @coopmedcode2=@coopmedcode
set @idno2=@idno
update tmpcbnhgrxx2 set coopmedcode=@coopmedcode,relation='本人或户主' where id = @id
end
else
update tmpcbnhgrxx2 set coopmedcode=@coopmedcode2,@idno=@idno2+1 where id = @id fetch next from cur into @id,@coopmedcode,@idno;
end
close cur;
deallocate cur;
001 邓卫红 1 1
null 张宝香 null 2
null 邓鑫 null 3
002 邓洪锋 1 4
null 田华 null 5
null 范香来 null 6-------解释:COOP不为NULL 表示是户主,家庭序号IDNO='1' ,ID是流水号
我想通过游标处理返回结果为:
coop name idno id
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6我写的这个能更新coopmedcode 但是更新IDNO 有问题
不知道原因 请教高手
declare @id bigint,@coopmedcode varchar(15) ,@coopmedcode2 varchar(15),@idno smallint,@idno2 smallint;
declare cur cursor fast_forward for
select top 100 id,coopmedcode,idno from tmpcbnhgrxx2 order by id;
open cur;
fetch next from cur into @id,@coopmedcode,@idno;
while @@fetch_status=0
begin
if len(@coopmedcode)>=1
begin
set @coopmedcode2=@coopmedcode
set @idno2=@idno
update tmpcbnhgrxx2 set coopmedcode=@coopmedcode,relation='本人或户主' where id = @id
end
else
update tmpcbnhgrxx2 set coopmedcode=@coopmedcode2,@idno=@idno2+1 where id = @id fetch next from cur into @id,@coopmedcode,@idno;
end
close cur;
deallocate cur;
insert into tb values('001', '邓卫红', 1 ,1)
insert into tb values(null , '张宝香', null ,2)
insert into tb values(null , '邓鑫' , null ,3 )
insert into tb values('002', '邓洪锋', 1 ,4 )
insert into tb values(null , '田华' , null ,5 )
insert into tb values(null , '范香来', null , 6 )
godeclare @coop varchar(10), @idno int , @id int;
declare @coop2 varchar(10),@idno2 int;
declare cur cursor fast_forward for
select coop,idno,id from tb;
open cur;
fetch next from cur into @coop,@idno,@id;
while @@fetch_status=0
begin
if @idno = 1
begin
set @idno2 = 1
set @coop2 = @coop
end
else
begin
set @idno2 = @idno2 + 1
update tb set coop = @coop2 , idno = @idno2 where id = @id
end
fetch next from cur into @coop,@idno,@id;
end
close cur;
deallocate cur;select * from tbdrop table tb/*
coop name idno id
---------- ---------- ----------- -----------
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6(所影响的行数为 6 行)
*/
--SQL 2000create table T(coop varchar(03), name nvarchar(20), idno int,id int)
insert into T select '001', '邓卫红', 1, 1
insert into T select null , '张宝香' , null, 2
insert into T select null , '邓鑫' ,null ,3
insert into T select '002' , '邓洪锋', 1, 4
insert into T select null , '田华' , null , 5
insert into T select null ,'范香来', null, 6
Go
select isnull(coop,(select top 1 coop from T where id<=A.id and coop is not null order by id desc)) as Coop,
name,
idno,
id
into #
from T as Aselect coop, name, (select count(*) from # where coop=A.coop and id<=A.id) as idno , id
from # as A
order by coop, iddrop table T,#
-- Author: Ken Wong
-- Create date: 2009-12-24 16:40:49
-- Description:
/*====================================================*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([coop] varchar(3),[name] varchar(6),[idno] int,[id] int)
insert [tb]
select '001','邓卫红',1,1 union all
select null,'张宝香',null,2 union all
select null,'邓鑫',null,3 union all
select '002','邓洪锋',1,4 union all
select null,'田华',null,5 union all
select null,'范香来',null,6
update t
set coop = (select top 1 coop from [tb] where id<t.id and isnull(coop,'')<>'' order by id desc),
idno = id+1-(select top 1 id from [tb] where id<=t.id and isnull(idno,'')<>'' order by id desc)
from [tb] t
where coop is null or idno is nullselect * from [tb]
------------------------
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6
coop
再更新
idno
--如果Id不连续,可以试下这个
create table T(coop varchar(03), name nvarchar(20), idno int,id int)
insert into T select '001', N'邓卫红', 1, 1
insert into T select null , N'张宝香' , null, 2
insert into T select null , N'邓鑫' ,null ,3
insert into T select '002' , N'邓洪锋', 1, 4
insert into T select null , N'田华' , null , 5
insert into T select null ,N'范香来', null, 6
Go
declare @Lcoop varchar(03),@Ncoop varchar(03),@Lidno int,@Nidno int
Update A
set @Ncoop=case when coop is null
then (select top 1 coop from T where id<=A.id and coop is not null order by id desc)
else coop end,
coop=@Ncoop,
@Nidno=case when @Lcoop=@Ncoop then @Lidno+1 else idno end,
@Lcoop=@Ncoop,
idno= @Nidno,
@Lidno=@Nidno
from T as Aselect * from T
/*
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6
*/drop table T
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([coop] varchar(3),[name] varchar(6),[idno] int,[id] int)
insert [tb]
select '001','邓卫红',1,1 union all
select null,'张宝香',null,2 union all
select null,'邓鑫',null,3 union all
select '002','邓洪锋',1,4 union all
select null,'田华',null,5 union all
select null,'范香来',null,6
goselect [coop],name,
idno=row_number() over(partition by [coop] order by id), id
from
(
select [coop]=isnull([coop],(select top 1 coop from tb where id<=A.id and coop is not null order by id desc)),
a.name,
a.idno,
a.id from [tb] a) a
/*
coop name idno id
---- ------ -------------------- -----------
001 邓卫红 1 1
001 张宝香 2 2
001 邓鑫 3 3
002 邓洪锋 1 4
002 田华 2 5
002 范香来 3 6(6 行受影响)
*/