用SQL语句实现以下结果:
说明:一张表RMAHeader,有一列RMANumber
select * from RMAHeader 结果如下 RAMNumber
00001111
00001111
00001111
00001111
55511111
55511111
55511111
99999999
99999999要实现以下结果:select * from RMAHeader 结果如下 RAMNumber 次序
00001111 1
00001111 2
00001111 3
00001111 4
55511111 1
55511111 2
55511111 3
99999999 1
99999999 2
意思是说增加一列"次序",并判断RMANumber相同的以递增的方式来显示.
例如:
00001111有四条,所以次序为1到4.有三条就为1到3
说明:一张表RMAHeader,有一列RMANumber
select * from RMAHeader 结果如下 RAMNumber
00001111
00001111
00001111
00001111
55511111
55511111
55511111
99999999
99999999要实现以下结果:select * from RMAHeader 结果如下 RAMNumber 次序
00001111 1
00001111 2
00001111 3
00001111 4
55511111 1
55511111 2
55511111 3
99999999 1
99999999 2
意思是说增加一列"次序",并判断RMANumber相同的以递增的方式来显示.
例如:
00001111有四条,所以次序为1到4.有三条就为1到3
insert RMAHeader select '00001111'
union all select '00001111'
union all select '00001111'
union all select '00001111'
union all select '55511111'
union all select '55511111'
union all select '55511111'
union all select '99999999'
union all select '99999999'select ID=identity(int, 1, 1), * into #T from RMAHeader select A.RMANumber,
次序=(select count(*) from #T as B where B.RMANumber=A.RMANumber and B.ID<=A.ID)
from #T as A--result
RMANumber 次序
-------------------- -----------
00001111 1
00001111 2
00001111 3
00001111 4
55511111 1
55511111 2
55511111 3
99999999 1
99999999 2(9 row(s) affected)
drop table RMAHeader, #T
insert RMAHeader select '00001111'
union all select '00001111'
union all select '00001111'
union all select '00001111'
union all select '55511111'
union all select '55511111'
union all select '55511111'
union all select '99999999'
union all select '99999999'看效果:
declare @RMAHeader table(RMAHeader varchar(20),num int)--通过表变量看结果
insert @RMAHeader(RMAHeader) select * from RMAHeader
declare @a varchar(20),@i int
update a
set @i=case when RMAHeader=@a then @i+1 else 1 end,
@a=RMAHeader,num=@i
from @RMAHeader aselect * from @RMAHeader(所影响的行数为 9 行)
(所影响的行数为 9 行)RMAHeader num
-------------------- -----------
00001111 1
00001111 2
00001111 3
00001111 4
55511111 1
55511111 2
55511111 3
99999999 1
99999999 2(所影响的行数为 9 行)