写得有点乱,你自己替换其中的字段,type可以用你的编号替代,name用你的收费单位替代,表名不用说了,试试看:select tb.type + '-' + cast((SELECT COUNT(*) FROM ( select type,name, ROW_NUMBER() OVER (ORDER BY type) AS [serialnumber] FROM spt_values )tb1 WHERE type = tb.type and serialnumber <= tb.serialnumber) as varchar(20)), tb.name from ( select type,name, ROW_NUMBER() OVER (ORDER BY type) AS [serialnumber] FROM spt_values )tb
sorry,用partition更简单select type + '-' + cast(row_number() over (partition by type order by type) as varchar(20)),name from spt_values
就是不知如何让编号生成为:xhtw12345 把它变成 xhtw12345-1,xhtw12345-2,xhtw12345-3. N个收费的就循环到xhtw12345-N
(
select type,name, ROW_NUMBER() OVER (ORDER BY type) AS [serialnumber]
FROM spt_values
)tb1 WHERE type = tb.type and serialnumber <= tb.serialnumber) as varchar(20)),
tb.name from
(
select type,name, ROW_NUMBER() OVER (ORDER BY type) AS [serialnumber]
FROM spt_values
)tb