用的是SQL2000 有这样两个字段,分别叫票号和日期,想实现这样的效果,按天加序号
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020910206 20110209
A01C2011020910206 20110209
A01C2011020910206 20110209
A01C2011020910206 20110209
效果
A01C2011020110053 20110201 1
A01C2011020110053 20110201 2
A01C2011020110053 20110201 3
A01C2011020110053 20110201 4
A01C2011020110053 20110201 5
A01C2011020910206 20110209 1
A01C2011020910206 20110209 2
A01C2011020910206 20110209 3
A01C2011020910206 20110209 4
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020110053 20110201
A01C2011020910206 20110209
A01C2011020910206 20110209
A01C2011020910206 20110209
A01C2011020910206 20110209
效果
A01C2011020110053 20110201 1
A01C2011020110053 20110201 2
A01C2011020110053 20110201 3
A01C2011020110053 20110201 4
A01C2011020110053 20110201 5
A01C2011020910206 20110209 1
A01C2011020910206 20110209 2
A01C2011020910206 20110209 3
A01C2011020910206 20110209 4
into #temp
from T
select 票号,日期, sortid=(select count(*) from #temp where 票号=A.票号 and 日期=A.日期 and id<=A.id)
from #temp as A
select t.* , px = row_number() over(partition by 票号,日期 order by 票号,日期) from tb t
--2000select id=identity(int,1,1),票号,日期
into #t
from tb
select 票号,日期, sortid=(select count(*) from #t where 票号=a.票号 and 日期=a.日期 and id<=a.id)
from #t a--2005row_number() over (partition by .. order by ...)
--建表
create table #Temp(id varchar(50), date varchar(20))
--数据
insert into #temp
select 'A01C2011020110053','20110201'
union all select 'A01C2011020110053','20110201'
union all select 'A01C2011020110053','20110201'
union all select 'A01C2011020110053','20110201'
union all select 'A01C2011020110053','20110201'
union all select 'A01C2011020910206','20110209'
union all select 'A01C2011020910206','20110209'
union all select 'A01C2011020910206','20110209'
union all select 'A01C2011020910206','20110209'
--临时表
select identity(int,1,1) as autoid,* into #temp1 from #temp order by date
--显示
select id,date,(select count(0) from #temp1 where date=a.date and autoid<=a.autoid) from #temp1 as aid date
-------------------------------------------------- -------------------- -----------
A01C2011020110053 20110201 1
A01C2011020110053 20110201 2
A01C2011020110053 20110201 3
A01C2011020110053 20110201 4
A01C2011020110053 20110201 5
A01C2011020910206 20110209 1
A01C2011020910206 20110209 2
A01C2011020910206 20110209 3
A01C2011020910206 20110209 4(9 行受影响)
declare @table table (票号 varchar(17),日期 varchar(8))
insert into @table
select 'A01C2011020110053','20110201' union all
select 'A01C2011020110053','20110201' union all
select 'A01C2011020110053','20110201' union all
select 'A01C2011020110053','20110201' union all
select 'A01C2011020110053','20110201' union all
select 'A01C2011020910206','20110209' union all
select 'A01C2011020910206','20110209' union all
select 'A01C2011020910206','20110209' union all
select 'A01C2011020910206','20110209'
create table #t(id int identity(1,1),票号 varchar(17),日期 varchar(8))
insert into #t(票号,日期) select * from @tableselect 票号,日期,
(select count(1) from #t where 票号=b.票号 and 日期=b.日期 and id<=b.id) as id
from #t b
drop table #t
/*
票号 日期 id
----------------- -------- -----------
A01C2011020110053 20110201 1
A01C2011020110053 20110201 2
A01C2011020110053 20110201 3
A01C2011020110053 20110201 4
A01C2011020110053 20110201 5
A01C2011020910206 20110209 1
A01C2011020910206 20110209 2
A01C2011020910206 20110209 3
A01C2011020910206 20110209 4
*/
(select count(1) from tb where 票号=b.票号 and 日期=b.日期 and id<=b.id) as id
from tb b
drop table tb