use testgo
declare @t table (auno int, type int, stat nvarchar(50))
insert @t select 2659, 807, 'TYPE'
insert @t select 2658, 805, 'CHIPSET'
insert @t select 2655, 806, 'TYPE'
insert @t select 2661, 808, 'CHIPSET'
insert @t select 2654, 802, 'WxHxD(mm)'
insert @t select 2741, 804, 'Weight(Kg/Pound)' select auno,type,
[stat]=stat+
case when (select count(1) from @t where stat=t.stat and auno>t.auno)=0 then '' else
rtrim((select count(1) from @t where stat=t.stat and auno>t.auno)) end
from @t tauno type stat
----------- ----------- --------------------------------------------------------------
2659 807 TYPE
2658 805 CHIPSET1
2655 806 TYPE1
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)
declare @t table (auno int, type int, stat nvarchar(50))
insert @t select 2659, 807, 'TYPE'
insert @t select 2658, 805, 'CHIPSET'
insert @t select 2655, 806, 'TYPE'
insert @t select 2661, 808, 'CHIPSET'
insert @t select 2654, 802, 'WxHxD(mm)'
insert @t select 2741, 804, 'Weight(Kg/Pound)' select auno,type,
[stat]=stat+
case when (select count(1) from @t where stat=t.stat and auno>t.auno)=0 then '' else
rtrim((select count(1) from @t where stat=t.stat and auno>t.auno)) end
from @t tauno type stat
----------- ----------- --------------------------------------------------------------
2659 807 TYPE
2658 805 CHIPSET1
2655 806 TYPE1
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)
insert into tb values(2659, 807, 'TYPE')
insert into tb values(2658, 805, 'CHIPSET')
insert into tb values(2655, 806, 'TYPE')
insert into tb values(2661, 808, 'CHIPSET')
insert into tb values(2654, 802, 'WxHxD(mm)')
insert into tb values(2741, 804, 'Weight(Kg/Pound)')
goselect auno,type,stat = case when px > 1 then stat+'1' else stat end from
(
select px=(select count(1) from tb where stat=a.stat and auno<a.auno)+1 , * from tb a
) t
order by aunodrop table tb/*
auno type stat
----------- ----------- ---------------------
2654 802 WxHxD(mm)
2655 806 TYPE
2658 805 CHIPSET
2659 807 TYPE1
2661 808 CHIPSET1
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)
*/
create table t3(auno int,type int,stat varchar(50))
insert into t3 select 2659, 807, 'TYPE'
insert into t3 select 2658, 805 ,'CHIPSET'
insert into t3 select 2655, 806, 'TYPE'
insert into t3 select 2661, 808, 'CHIPSET'
insert into t3 select 2654 ,802, 'WxHxD(mm)'
insert into t3 select 2741, 804, 'Weight(Kg/Pound)' select auno,type,stat + replace(right(cast((select count(1) from t3 where auno<a.auno and a.stat=t3.stat) as varchar),1),'0','') stat
from t3 a
declare @t table (auno int, type int, stat nvarchar(50))
insert @t select 2659, 807, 'TYPE'
insert @t select 2658, 805, 'CHIPSET'
insert @t select 2655, 806, 'TYPE'
insert @t select 2661, 808, 'CHIPSET'
insert @t select 2654, 802, 'WxHxD(mm)'
insert @t select 2741, 804, 'Weight(Kg/Pound)' select
t1.auno,
t1.type,
[stat]=t1.stat+case when t2.con=0 then '' else rtrim(t2.con) end
from
@t t1
left join
(select auno,con=(select count(1) from @t where stat=t.stat and auno>t.auno) from @t t)t2
on t1.auno=t2.auno
auno type stat
----------- ----------- --------------------------------------------------------------
2659 807 TYPE
2658 805 CHIPSET1
2655 806 TYPE1
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)