Dept Inv createdDate
001 A001 2010-09-10
001 A002 2010-09-12
001 A002 2010-09-16
002 A001 2010-09-01
002 A002 2010-09-06
002 A003 2010-09-09
002 B001 2010-09-10以Dept和Inv为条件分组产生序号SeqNo,SeqNo的大小以CreatedDate的先后产生大小
Dept Inv createdDate SeqNo
001 A001 2010-09-10 1
001 A002 2010-09-12 2
001 A002 2010-09-16 3
002 A001 2010-09-01 1
002 A002 2010-09-06 2
002 A003 2010-09-09 3
002 B001 2010-09-10 4 谢谢
001 A001 2010-09-10
001 A002 2010-09-12
001 A002 2010-09-16
002 A001 2010-09-01
002 A002 2010-09-06
002 A003 2010-09-09
002 B001 2010-09-10以Dept和Inv为条件分组产生序号SeqNo,SeqNo的大小以CreatedDate的先后产生大小
Dept Inv createdDate SeqNo
001 A001 2010-09-10 1
001 A002 2010-09-12 2
001 A002 2010-09-16 3
002 A001 2010-09-01 1
002 A002 2010-09-06 2
002 A003 2010-09-09 3
002 B001 2010-09-10 4 谢谢
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=row_number()over(partition by Dept order by createdDate) from #/*
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
*/
(
Dept varchar(8),
Inv varchar(8),
createdDate datetime
)
insert into T
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'--SQL2000
select *,SeqNo=(select count(1)+1 from T where a.Dept=Dept and a.createdDate>createdDate)
from T a--SQL2005
select *,SeqNo=rank() over(partition by Dept order by createdDate)
from TDept 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(7 行受影响)
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
*/
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=row_number()over(partition by Dept order by createdDate) from #DROP TABLE #tempCREATE TABLE #temp (SeqNo INT IDENTITY(1,1) NOT NULL,Dept VARCHAR(20),num INT);INSERT INTO #temp(Dept ,num) SELECT Dept,COUNT(*) FROM # GROUP BY Dept;SELECT * FROM #tempCREATE TABLE #t(SeqNo INT IDENTITY(1,1),dept VARCHAR(20),inv VARCHAR(8),createdDate DATETIME)CREATE TABLE #shuju(SeqNo INT,Dept VARCHAR(8),inv VARCHAR(8),createdDate DATETIME);DECLARE cur_num CURSOR FOR SELECT dept FROM #temp;OPEN cur_num;DECLARE @dep VARCHAR(8)FETCH NEXT FROM cur_num INTO @depWHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #t( dept, inv, createdDate ) SELECT dept ,inv,createdDate FROM # WHERE Dept = @dep;
IF @@ERROR = 0
INSERT INTO #shuju( SeqNo, Dept, inv, createdDate )SELECT * FROM #t;
DELETE #t;
FETCH NEXT FROM cur_num INTO @dep
END