22,209
社区成员
发帖
与我相关
我的任务
分享
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 #temp
CREATE 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 #temp
CREATE 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 @dep
WHILE @@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
--> 测试数据:#
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 T
(
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 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
(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=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
*/