在sql2000中如何实现ROW_NUMBER函数的效果

if object_id('tempdb.dbo.#') is not null drop table #
create table #(Dept varchar(8), Inv varchar(8), createdDate datetime)
insert into #
select '001', 'A001', '2010-09-10' union all
select '001', 'A002', '2010-09-12' union all
select '001', 'A002', '2010-09-16' union all
select '002', 'A001', '2010-09-01' union all
select '002', 'A002', '2010-09-06' union all
select '002', 'A003', '2010-09-09' union all
select '002', 'B001', '2010-09-10'

select *, SeqNo=(select count(1) from # where Dept=t.Dept and createdDate<=t.createdDate) from # t

/*
Dept     Inv      createdDate             SeqNo
-------- -------- ----------------------- -----------
001      A001     2010-09-10 00:00:00.000 1
001      A002     2010-09-12 00:00:00.000 2
001      A002     2010-09-16 00:00:00.000 3
002      A001     2010-09-01 00:00:00.000 1
002      A002     2010-09-06 00:00:00.000 2
002      A003     2010-09-09 00:00:00.000 3
002      B001     2010-09-10 00:00:00.000 4
*/


此语句与下面的使用ROW_NUMBER函数的效果是相同的

select *, SeqNo=row_number()over(partition by Dept order by createdDate) from #

转载于:https://www.cnblogs.com/gaolonglong/archive/2010/09/24/1834207.html