表 tb
列 no part,xu (null)数据 part xu
a
a
b
b
b想要的结果
part xu
a 1
a 2
b 1
b 2
b 3计算一个part总数。然后一个一个的给他排序。。
列 no part,xu (null)数据 part xu
a
a
b
b
b想要的结果
part xu
a 1
a 2
b 1
b 2
b 3计算一个part总数。然后一个一个的给他排序。。
xu=row_number() over(partition by part order by getdate())
from tb
part,
xu=row_number()over(partition by part order by getdate())
from tb
(
part nchar(2)
)
insert into #tb select 'a'
union all select 'a'
union all select 'b'
union all select 'b'
union all select 'b'select part,
row_number() over(partition by part order by part) xu
from #tbpart xu
---- --------------------
a 1
a 2
b 1
b 2
b 3(5 行受影响)
http://topic.csdn.net/u/20100909/22/84255d29-f703-428a-86d0-747d7304d888.html参考6楼和9楼
(
part nchar(2)
)
insert into #tb select 'a'
union all select 'a'
union all select 'b'
union all select 'b'
union all select 'b'select identity(int,1,1) ID,* into #t from #tb
select part,
(select count(*)+1 from #t where part=t.part and ID<t.ID) xu
from #t t
part xu
---- -----------
a 1
a 2
b 1
b 2
b 3(5 行受影响)
drop table tb
Go
Create table tb([part] nvarchar(1),[xu] int)
Insert tb
select N'a',null union all
select N'a',null union all
select N'b',null union all
select N'b',null union all
select N'b',null
Go
declare @i int,@part varchar(10)
select @i=0,@part=''
update tb set [xu]=case when [part]=@part then @i else [xu]end,
@i=case when [part]=@part then @i+1 else 1 end,
@part=[part]
select * from tb
/*
part xu
---- -----------
a 1
a 2
b 1
b 2
b 3*/