我的表结构及数据如下
-- ID int 递增，主键
-- 纳税人内部码 varchar(50)， 指的是纳税的单位对应的纳税ID
-- 纳税个人内部码 varchar(50),指的是纳税个人对应的纳税ID
-- station varchar(8), 指的是通讯地址所属的站，8位数字组成，也有null值存在
-- sect varchar(3)， 指的是通讯地址所属的段，1-3位数组成，也有null值存在，不足3位前面没有补位
-- countnumber int, 指的是数据中同一 纳税人内部码 且 同一通讯地址的记录条数
-- 信息编码 varchar(50)，
信息编码是由 station + '-' + 'sect + '-' + 纳税人内部码 + '-' + 流水号 构成，
即流水号为 1 至 countnumber 的最大值，流水号为固定6位，不足6位的前面补0.
即同一 station、sect、纳税人内部码, 流水号就由“纳税个人内部码排序”（注意，区别于纳税人内部码字段！！）数据样板如下：
id 纳税个人内部码 station sect 纳税人内部码 信息编码 countnumber
22258 977181817 51900006 1 3625567 51900006-1-3625567-000239 239
24040 977263996 51900006 1 3625567 51900006-1-3625567-000239 239
45905 993968667 51900006 1 3625567 51900006-1-3625567-000239 239
55289 1004801513 51900006 1 3625567 51900006-1-3625567-000239 239
94695 1015897644 51900006 1 3625567 51900006-1-3625567-000239 239
...
74621 1012841411 51900006 15 1034833579 51900006-15-1034833579-000011 11
74624 1012841474 51900006 15 1034833579 51900006-15-1034833579-000011 11
417952 1036293115 51900006 15 1034833579 51900006-15-1034833579-000011 11
445502 1039053552 51900006 15 1034833579 51900006-15-1034833579-000011 11
445503 1039053553 51900006 15 1034833579 51900006-15-1034833579-000011 11
...
87865 1014872096 51901502 7 3598987 51901502-7-3598987-001279 1279
87866 1014872132 51901502 7 3598987 51901502-7-3598987-001279 1279
87867 1014872137 51901502 7 3598987 51901502-7-3598987-001279 1279
87770 1014871613 51901502 7 3598987 51901502-7-3598987-001279 1279
87771 1014871642 51901502 7 3598987 51901502-7-3598987-001279 1279
...我现在需要更新 信息编码 这个字段，更新后的数据为：
id 纳税个人内部码 station sect 纳税人内部码 信息编码 countnumber
22258 977181817 51900006 1 3625567 51900006-1-3625567-000001 239
24040 977263996 51900006 1 3625567 51900006-1-3625567-000002 239
45905 993968667 51900006 1 3625567 51900006-1-3625567-000003 239
55289 1004801513 51900006 1 3625567 51900006-1-3625567-000004 239
94695 1015897644 51900006 1 3625567 51900006-1-3625567-000005 239
...
74621 1012841411 51900006 15 1034833579 51900006-15-1034833579-000001 11
74624 1012841474 51900006 15 1034833579 51900006-15-1034833579-000002 11
417952 1036293115 51900006 15 1034833579 51900006-15-1034833579-000003 11
445502 1039053552 51900006 15 1034833579 51900006-15-1034833579-000004 11
445503 1039053553 51900006 15 1034833579 51900006-15-1034833579-000005 11
...
87865 1014872096 51901502 7 3598987 51901502-7-3598987-000001 1279
87866 1014872132 51901502 7 3598987 51901502-7-3598987-000002 1279
87867 1014872137 51901502 7 3598987 51901502-7-3598987-000003 1279
87770 1014871613 51901502 7 3598987 51901502-7-3598987-000004 1279
87771 1014871642 51901502 7 3598987 51901502-7-3598987-000005 1279
...
create table t1
(
id int,
col1 varchar(10),
station varchar(10),
sect varchar(3),
col2 varchar(10),
col3 varchar(50),
countnumber int
)
insert into t1
select 22258, '977181817', '51900006', '1', '3625567', '51900006-1-3625567-000239', 239 union all
select 24040, '977263996', '51900006', '1', '3625567', '51900006-1-3625567-000239', 239 union all
select 45905, '993968667', '51900006', '1', '3625567', '51900006-1-3625567-000239', 239 union all
select 55289, '1004801513', '51900006', '1', '3625567', '51900006-1-3625567-000239', 239 union all
select 94695, '1015897644', '51900006', '1', '3625567', '51900006-1-3625567-000239', 239 union all
select 74621, '1012841411', '51900006', '15', '1034833579', '51900006-15-1034833579-000011', 11 union all
select 74624, '1012841474', '51900006', '15', '1034833579', '51900006-15-1034833579-000011', 11 union all
select 417952, '1036293115', '51900006', '15', '1034833579', '51900006-15-1034833579-000011', 11 union all
select 445502, '1039053552', '51900006', '15', '1034833579', '51900006-15-1034833579-000011', 11 union all
select 445503, '1039053553', '51900006', '15', '1034833579', '51900006-15-1034833579-000011', 11
select * from t1;with aaa as
(
select ROW_NUMBER() over(PARTITION by station,sect,col2 order by id) as rowindex,* from t1
)
update aaa set col3=LTRIM(station)+'-'+LTRIM(sect)+'-'+LTRIM(col2)+'-'+RIGHT('000000'+ltrim(rowindex),6)select * from t1---------------------------
id col1 station sect col2 col3 countnumber
22258 977181817 51900006 1 3625567 51900006-1-3625567-000001 239
24040 977263996 51900006 1 3625567 51900006-1-3625567-000002 239
45905 993968667 51900006 1 3625567 51900006-1-3625567-000003 239
55289 1004801513 51900006 1 3625567 51900006-1-3625567-000004 239
94695 1015897644 51900006 1 3625567 51900006-1-3625567-000005 239
74621 1012841411 51900006 15 1034833579 51900006-15-1034833579-000001 11
74624 1012841474 51900006 15 1034833579 51900006-15-1034833579-000002 11
417952 1036293115 51900006 15 1034833579 51900006-15-1034833579-000003 11
445502 1039053552 51900006 15 1034833579 51900006-15-1034833579-000004 11
445503 1039053553 51900006 15 1034833579 51900006-15-1034833579-000005 11
--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[id] varchar(6),
[纳税个人内部码] int,
[station] int,[sect] int,
[纳税人内部码] int,
[信息编码] varchar(30),
[countnumber] int
)
go
insert [tbl]
select '22258',977181817,51900006,1,3625567,null,239 union all
select '24040',977263996,51900006,1,3625567,null,239 union all
select '45905',993968667,51900006,1,3625567,null,239 union all
select '55289',1004801513,51900006,1,3625567,null,239 union all
select '94695',1015897644,51900006,1,3625567,null,239 union all
select '74621',1012841411,51900006,15,1034833579,null,11 union all
select '74624',1012841474,51900006,15,1034833579,null,11 union all
select '417952',1036293115,51900006,15,1034833579,null,11 union all
select '445502',1039053552,51900006,15,1034833579,null,11 union all
select '445503',1039053553,51900006,15,1034833579,null,11 union all
select '87865',1014872096,51901502,7,3598987,null,1279 union all
select '87866',1014872132,51901502,7,3598987,null,1279 union all
select '87867',1014872137,51901502,7,3598987,null,1279 union all
select '87770',1014871613,51901502,7,3598987,null,1279 union all
select '87771',1014871642,51901502,7,3598987,null,1279
;with t as
(
select *,row_number()over(partition by [纳税人内部码]
order by getdate()) as rownum
from tbl
)
update tbl
set [信息编码]=ltrim(a.station)+'-'+ltrim(a.sect)+'-'
+ltrim(a.纳税人内部码) +'-'+right('000000'+ltrim(a.rownum),6)
from t a where a.[id]=tbl.id
select * from tbl order by sect
/*
id 纳税个人内部码 station sect 纳税人内部码 信息编码 countnumber
22258 977181817 51900006 1 3625567 51900006-1-3625567-000001 239
24040 977263996 51900006 1 3625567 51900006-1-3625567-000002 239
45905 993968667 51900006 1 3625567 51900006-1-3625567-000003 239
55289 1004801513 51900006 1 3625567 51900006-1-3625567-000004 239
94695 1015897644 51900006 1 3625567 51900006-1-3625567-000005 239
87865 1014872096 51901502 7 3598987 51901502-7-3598987-000001 1279
87866 1014872132 51901502 7 3598987 51901502-7-3598987-000002 1279
87867 1014872137 51901502 7 3598987 51901502-7-3598987-000003 1279
87770 1014871613 51901502 7 3598987 51901502-7-3598987-000004 1279
87771 1014871642 51901502 7 3598987 51901502-7-3598987-000005 1279
74621 1012841411 51900006 15 1034833579 51900006-15-1034833579-000001 11
74624 1012841474 51900006 15 1034833579 51900006-15-1034833579-000002 11
417952 1036293115 51900006 15 1034833579 51900006-15-1034833579-000003 11
445502 1039053552 51900006 15 1034833579 51900006-15-1034833579-000004 11
445503 1039053553 51900006 15 1034833579 51900006-15-1034833579-000005 11
*/
2000的可以使用临时表代替row_number（）over()