T1
编号 名称
1001 XX
1001 XX1
1002 XX2
1002 XX3
1001 XX4
1003 XX5
1003 XX6
1002 XX7想要的结果
T1
编号 编号内流水号 名称
1001 01 XX
1001 02 XX1
1002 01 XX2
1002 02 XX3
1001 03 XX4
1003 01 XX5
1003 02 XX6
1002 03 XX7根据同编号队编号内流水号进行UPDATE
原先表中没有“编号内流水号”这个字段,现在根据程序需要,要增加一个,并按上面的结果进行UPDATE
编号 名称
1001 XX
1001 XX1
1002 XX2
1002 XX3
1001 XX4
1003 XX5
1003 XX6
1002 XX7想要的结果
T1
编号 编号内流水号 名称
1001 01 XX
1001 02 XX1
1002 01 XX2
1002 02 XX3
1001 03 XX4
1003 01 XX5
1003 02 XX6
1002 03 XX7根据同编号队编号内流水号进行UPDATE
原先表中没有“编号内流水号”这个字段,现在根据程序需要,要增加一个,并按上面的结果进行UPDATE
insert into T1 select '1001','XX'
insert into T1 select '1001','XX1'
insert into T1 select '1002','XX2'
insert into T1 select '1002','XX3'
insert into T1 select '1001','XX4'
insert into T1 select '1003','XX5'
insert into T1 select '1003','XX6'
insert into T1 select '1002','XX7'
go
alter table t1 add id1 nvarchar(10)
go
update t1 set id1='0'+ltrim(b.rm) from t1 a inner join (
select id,ROW_NUMBER() OVER (PARTITION BY id ORDER BY cname) AS rm,cname from t1
)b on a.id=b.id and a.cname=b.cname
select * from t1
go
drop table t1
/*
id cname id1
---------- ---------- ----------
1001 XX 01
1001 XX1 02
1002 XX2 01
1002 XX3 02
1001 XX4 03
1003 XX5 01
1003 XX6 02
1002 XX7 03(8 行受影响)
*/
(
编号 varchar(10),
名称 varchar(10)
)
insert into t1
select '1001','XX' union all
select '1001','XX1' union all
select '1002','XX2' union all
select '1002','XX3' union all
select '1001','XX4' union all
select '1003','XX5' union all
select '1003','XX6' union all
select '1002','XX7'
--添加字段编号内流水号
alter table t1 add 编号内流水号 varchar(10)
--更新
update t1
set 编号内流水号=right(100+cast(RIGHT(编号,3) as int),2)select * from t1
/*
编号 名称 编号内流水号
1001 XX 01
1001 XX1 01
1002 XX2 02
1002 XX3 02
1001 XX4 01
1003 XX5 03
1003 XX6 03
1002 XX7 02
*/
create table t1
(
编号 varchar(10),
名称 varchar(10)
)
insert into t1
select '1001','XX' union all
select '1001','XX1' union all
select '1002','XX2' union all
select '1002','XX3' union all
select '1001','XX4' union all
select '1003','XX5' union all
select '1003','XX6' union all
select '1002','XX7'
--添加字段编号内流水号
alter table t1 add 编号内流水号 varchar(10)
--更新
;with cte as
(
select ROW_NUMBER()over(partition by 编号 order by 名称) as rn,* from t1
)
update cte set 编号内流水号=right(100+rn,2)select * from t1
/*
编号 名称 编号内流水号
1001 XX 01
1001 XX1 01
1002 XX2 02
1002 XX3 02
1001 XX4 01
1003 XX5 03
1003 XX6 03
1002 XX7 02
*/
(
编号 varchar(100),
名称 varchar(100),
编号内流水号 varchar(100)
)
insert #temp(编号, 名称)
select '1001', 'xx' union all
select '1001', 'xx1' union all
select '1002', 'xx2' union all
select '1002', 'xx3' union all
select '1001', 'xx4' union all
select '1003', 'xx5' union all
select '1003', 'xx6' union all
select '1002', 'xx7'update A
set 编号内流水号 = right('0' + (select cast(COUNT(*)+1 as varchar(2)) from #temp where 编号 = A.编号 and 名称 < A.名称), 2)
from #temp A
还是觉得ROW_NUMBER() OVER 好些