SQL 2000SERVER
求解同ID时日期最大的为1依次加1递增?谢谢先
如
A表ID 日期
-----------------------
1 2011101
1 2011102
1 2011103
8 2011101
8 2011105
结果如下
ID 日期
-----------------------
1 2011101 3
1 2011102 2
1 2011103 1
8 2011105 1
8 2011101 2请教如何写?谢谢!
求解同ID时日期最大的为1依次加1递增?谢谢先
如
A表ID 日期
-----------------------
1 2011101
1 2011102
1 2011103
8 2011101
8 2011105
结果如下
ID 日期
-----------------------
1 2011101 3
1 2011102 2
1 2011103 1
8 2011105 1
8 2011101 2请教如何写?谢谢!
insert into tb(ID,日期) select 1,'20110101' --楼主的日期有误,改一下
insert into tb(ID,日期) select 1,'20110102'
insert into tb(ID,日期) select 1,'20110103'
insert into tb(ID,日期) select 8,'20110101'
insert into tb(ID,日期) select 8,'20110105'
go
declare @i int
set @i=1
while exists(select 1 from tb where is null)
begin
update a set =@i from tb a where is null and not exists(select 1 from tb where ID=a.ID and is null and 日期>a.日期)
set @i=@i+1
end
select * from tb
/*
ID 日期
----------- ----------------------- -----------
1 2011-01-01 00:00:00.000 3
1 2011-01-02 00:00:00.000 2
1 2011-01-03 00:00:00.000 1
8 2011-01-01 00:00:00.000 2
8 2011-01-05 00:00:00.000 1(5 行受影响)
*/
go
drop table tb
--日期 : date
select id,date,rid=identity(int,1,1)
into #tb
from tb
order by id,date descselect id,date,rid=(select count(*) from #tb where id = t.id and rid <= t.rid)
from #tb t
order by id,rid
create table tb(ID int,date datetime, int)
insert into tb(ID,date) select 1,'20110101' --楼主的日期有误,改一下
insert into tb(ID,date) select 1,'20110102'
insert into tb(ID,date) select 1,'20110103'
insert into tb(ID,date) select 8,'20110101'
insert into tb(ID,date) select 8,'20110105'
goselect id,date,rid=identity(int,1,1)
into #tb
from tb
order by id,date descselect id,date,rid=(select count(*) from #tb where id = t.id and rid <= t.rid)
from #tb t
order by id,riddrop table tb,#tb/*******************id date rid
----------- ----------------------- -----------
1 2011-01-03 00:00:00.000 1
1 2011-01-02 00:00:00.000 2
1 2011-01-01 00:00:00.000 3
8 2011-01-05 00:00:00.000 1
8 2011-01-01 00:00:00.000 2(5 行受影响)
insert into #tb(ID,日期) select 1,'2011-01-01' --楼主的日期有误,改一下
insert into #tb(ID,日期) select 1,'2011-01-02'
insert into #tb(ID,日期) select 1,'2011-01-03'
insert into #tb(ID,日期) select 8,'2011-01-01'
insert into #tb(ID,日期) select 8,'2011-01-05'
select * from #tb
update #tb set =b.rowid from #tb a
left join (select id,日期,ROW_NUMBER () over(partition by id order by 日期 desc) rowid from #tb) b
on a.ID=b.ID and a.日期=b.日期
a.ID ,a.日期,b.px as
from
tb a,
(select *,px=row_Number()over(partition by id order by 日期 desc) from tb)b
where
a.ID=b.ID and a.日期=b.日期