declare @i int,@field1 varchar(20)
set @i=0
set @field1=''
update 表 set field2=@i,@i=(case when @field1=field1 then @i+1 else 1 end),@field1=field1
set @i=0
set @field1=''
update 表 set field2=@i,@i=(case when @field1=field1 then @i+1 else 1 end),@field1=field1
set @i=0
set @field1=''
update 表 set field2=@i,@i=(case when @field1=field1 then @i+1 else 1 end),@field1=field1
select * from 表 -----檢查一下結果就是了
where 1=1
order by field1,field2
查询和变量赋值不能同时进行,更新和赋值能同时进行么?
insert into test000 select 'A' union all select 'A' union all select 'A' union all select 'A' union all
select 'B' union all select 'B' union all select 'B' union all
select 'C' union all select 'C' union all select 'C' union all select 'C'select col, ( select count(*) from test000 where id <= a.id and col = a.col ) as field2
from test000 a
drop table test000
是这样吗?
field1 field2
--------------------
A 1
A 2
A 3
A 4
B 5
B 6
C 7
C 8
C 9只要有一个不断累加的字段就可以了,套用xluzhong(打麻将一缺三,咋办?) 的方法就可以实现了,请问各位有没有好办法? 谢谢
create table test000(col nvarchar(10))
insert into test000 select 'A' union all select 'A' union all select 'A' union all select 'A' union all
select 'B' union all select 'B' union all select 'B' union all
select 'C' union all select 'C' union all select 'C' union all select 'C'select id=identity(int,1,1),* into #t from test000 order by colselect col,( select count(*) from #t where id <= a.id and col = a.col ) as field2
from #t a
drop table test000