紧急求助：用sql语句如何实现如下功能？

[shuaigc]

我有一表 stdata有3个字段为：
自增字段： ID
char(21):stdata
int: num

现在表里有记录，num值都为空值。

需求：将stdata相同的记录筛选出来（即：group by stdata having count(stdata)>1）
后，将num修改为1、2、3...n,n为相同stdata的记录数。

谢谢！

[wwwwb]

try:
update stdata from (select stdata,count(*) as dd from stdata group by stdata having count(stdata)>1) rr set num=rr.dd where stdata.stdata=rr.stdata

[QQMagicer]

update stdata
set num=t.c1
from
(select a.stdata,count(*) as c1 from stdata a
where a.id<b.id
group by stdata
having count(stdata)>1) b
where stdata.stdata=b.stdata

[churchatp1]

好像比较麻烦，帮你顶一下

[churchatp1]

得用游标吧?

[samfeng_2003]

declare @t1 table
(id int identity(1,1),stdata char(21),num int)

insert @t1 values ('1',null)
insert @t1 values ('1',null)
insert @t1 values ('2',null)
insert @t1 values ('3',null)
insert @t1 values ('3',null)
insert @t1 values ('3',null)
insert @t1 values ('4',null)
insert @t1 values ('5',null)
insert @t1 values ('6',null)
insert @t1 values ('7',null)
insert @t1 values ('7',null)
insert @t1 values ('7',null)
insert @t1 values ('8',null)

/*----主语句---*/
update @t1 set num=new_num
from
(
select [new_num]=
(select count(1) from
(select stdata from @t1 group by stdata having count(stdata)>=2) a
where a.stdata<=b.stdata),stdata
from
(select stdata from @t1 group by stdata having count(stdata)>=2) b
) a,@t1 b where a.stdata=b.stdata


/*---显示查询----*/


select * from @t1

（所影响的行数为 8 行）

id stdata num
----------- --------------------- -----------
1 1 1
2 1 1
3 2 NULL
4 3 2
5 3 2
6 3 2
7 4 NULL
8 5 NULL
9 6 NULL
10 7 3
11 7 3
12 7 3
13 8 NULL

（所影响的行数为 13 行）

[shuaigc]

samfeng_2003(风云) 非常感谢。若实现如下结果呢？


id stdata num
----------- --------------------- -----------
1 1 1
2 1 2
3 2 NULL
4 3 1
5 3 2
6 3 3
7 4 NULL
8 5 NULL
9 6 NULL
10 7 1
11 7 2
12 7 3
13 8 NULL

[zzit0721]

create table a ----测试数据生成
(id int identity(1,1),stdata char(21),num int)

insert into a select '1',null
insert into a select '1',null
insert into a select '1',null
insert into a select '7',null
insert into a select '2',null
insert into a select '3',null
insert into a select '3',null
insert into a select '3',null
insert into a select '4',null
insert into a select '2',null


declare -------双游标判断输入NUM
@a char(21),
@b char(21),
@c int,
@num int
declare x cursor for
select stdata
from a
group by stdata
having count(stdata)>=2

open x
fetch next from x
into @a

while(@@fetch_status=0)
begin
set @num=1
declare y cursor for
select id,stdata from a where stdata=@a

open y
fetch next from y into @c,@b

while(@@fetch_status=0)
begin
update a
set num=@num
where id=@c
set @num=@num+1
fetch next from y into @c,@b
end
close y
deallocate y

fetch next from x
into @a
end
close x
deallocate x


select *
from a

[凌雯]

我不会

[zxbyhcsdn]

级联更新

[samfeng_2003]

昨天走了没有看到，今天来回！

declare @t1 table
(id int identity(1,1),stdata char(21),num int)

insert @t1 values ('1',null)
insert @t1 values ('1',null)
insert @t1 values ('2',null)
insert @t1 values ('3',null)
insert @t1 values ('3',null)
insert @t1 values ('3',null)
insert @t1 values ('4',null)
insert @t1 values ('5',null)
insert @t1 values ('6',null)
insert @t1 values ('7',null)
insert @t1 values ('7',null)
insert @t1 values ('7',null)
insert @t1 values ('8',null)


/*----主语句---*/
update @t1 set num=new_num
from
(
select [new_num]=
(select count(1) from (select * from @t1 where stdata in (
select stdata from @t1 group by stdata having count(stdata)>=2
)) a where a.stdata=b.stdata and a.id<=b.id),*
from
(select * from @t1 where stdata in (select stdata from @t1 group by stdata having count(stdata)>=2)) b
) a,@t1 b where a.id=b.id

/*---显示查询----*/


select * from @t1


id stdata num
----------- --------------------- -----------
1 1 1
2 1 2
3 2 NULL
4 3 1
5 3 2
6 3 3
7 4 NULL
8 5 NULL
9 6 NULL
10 7 1
11 7 2
12 7 3
13 8 NULL

（所影响的行数为 13 行）

[shuaigc]

忘了回帖了，非常感谢 samfeng_2003(风云)、 zzit0721() 。
我是如此实现的：

if (exists (select * from sysobjects where name='temptale'))
drop table temptale

select id,stdata,IDENTITY(int, 1,1) as num into temptale from lstable
where stdata in (select stdata from lstable group by stdata having count(stdata)>=2)
order by stdata,macid

update lstable set num=temptale.num from temptale where lstable.macid=temptale.macid