上面好象不对 try: Select f1,identity(int,1,1) as iid into #tmp from 表 Select f1,(Select sum(1) from #tmp where f1 = a.f1 and iid<= a.iid) as id from #tmp a
select column, (select count(*) from table where column = a.column) [no],* from table a;
select *,(select count(*) from 表 where 列1=tem.列1 and 编号<=tem.编号) id from 表 tem
sorry , should be <=
--用临时表吧 select id=identity(int,1,1),* into #tb from 原表 select *,(select sum(1) from #tb where id<=a.id) from #tb a drop table #tb
--上面的有些错,下面是两种方法--数据测试环境 declare @tb table(a varchar(10)) insert into @tb select 'A01' union all select 'A01' union all select 'A01' union all select 'A01' union all select 'A01' union all select 'R01' union all select 'R01' union all select 'R01' union all select 'R01'--临时表处理,方法1 select id=0,a into #tb from @tb declare @id int,@a varchar(10) update #tb set @id=case a when @a then @id+1 else 1 end ,id=@id,@a=a select * from #tbdrop table #tb--临时表处理,方法2 select id=identity(int,1,1),* into #tb1 from @tb select a,(select sum(1) from #tb1 where id<=a.id and a=a.a) from #tb1 a drop table #tb1
try:
Select f1,identity(int,1,1) as iid into #tmp from 表
Select f1,(Select sum(1) from #tmp where f1 = a.f1 and iid<= a.iid) as id from #tmp a
select id=identity(int,1,1),* into #tb from 原表
select *,(select sum(1) from #tb where id<=a.id) from #tb a
drop table #tb
declare @tb table(a varchar(10))
insert into @tb
select 'A01'
union all select 'A01'
union all select 'A01'
union all select 'A01'
union all select 'A01'
union all select 'R01'
union all select 'R01'
union all select 'R01'
union all select 'R01'--临时表处理,方法1
select id=0,a into #tb from @tb
declare @id int,@a varchar(10)
update #tb set @id=case a when @a then @id+1 else 1 end
,id=@id,@a=a
select * from #tbdrop table #tb--临时表处理,方法2
select id=identity(int,1,1),* into #tb1 from @tb
select a,(select sum(1) from #tb1 where id<=a.id and a=a.a) from #tb1 a
drop table #tb1