create table test ( a varchar(10),
b int,
c int identity(1,1))
insert into test(a) values('A')
insert into test(a) values('A')
insert into test(a) values('B')
insert into test(a) values('C')
insert into test(a) values('A')
insert into test(a) values('B')
update test set test.b=
(
select distinct temp.t2 from
(
select a.a,b.c,count(a.a) as t2 from test a,test b
where a.a=b.a and a.c<=b.c group by a.a,b.c
) as temp
where temp.a=test.a and temp.c=test.c
)
GO
update test set count=con.bb(
select sum(1)as bb,max(a.uid) as uu from test a ,test b where a.test_id=b.test_id and a.uid>=b.uid group by a.test_id,a.uid)con
where test.uid=con.uu order by test_id,con.bb
insert into jj (a) values ('A')
insert into jj (a) values ('A')
insert into jj (a) values ('B')
insert into jj (a) values ('C')
insert into jj (a) values ('A')
insert into jj (a) values ('B')
insert into jj (a) values ('C')
create table kk (a varchar(10),b int,c int identity(1,1))
Insert into kk select * from jj
update kk set kk.b=(select distinct temp.t2 from (select mao.a,kk.c,t1,count(kk.a) as t2
from (select a,count(a) as t1,c from kk group by a,c) as mao,kk
where mao.a=kk.a and mao.c <= kk.c group by mao.a,mao.t1,kk.c) as temp where temp.a=kk.a and temp.c=kk.c)
問題已解決了
create test (
test_id varchar (10) not null,
test_name varchar (10) not null,
count numeric (28,2) ,
uid numeric (28,0) identity (1,1)
go
update test set count=con.bb(
select sum(1)as bb,max(a.uid) as uu from test a ,test b where a.test_id=b.test_id and a.uid>=b.uid group by a.test_id,a.uid)con
where test.uid=con.uu order by test_id,con.bb
你的field1一共有多少种类型,就定义多少个变量:
declar @a int,@b int,@c int
set @a=1
set @b=1
set @c=1
update 表 set
field2 =case when field1='A' then @a
when field1='B' then @b
when field1='C' then @c end
,@A=case when field1='A' then @a+1 else @a end
,@b=case when field1='B' then @b+1 else @b end
,@c=case when field1='C' then @c+1 else @c end