生成带有字母的连续号
现在有这样一个表
ID No Name
1 00001 AAA
2 00001 AAA
3 00001 AAA
4 00001 AAA
5 00001 AAA
6 00001 AAA
7 00001 AAA
8 00001 AAA
9 00001 AAA
10 00001 AAA
11 00001 AAA
12 00001 AAA
13 00002 BBB
14 00002 BBB
15 00002 BBB
想生成一个连续的号码来区别
想生成的结果是
ID No Name SubNo
1 00001 AAA 01
2 00001 AAA 02
3 00001 AAA 03
4 00001 AAA 04
5 00001 AAA 05
6 00001 AAA 06
7 00001 AAA 07
8 00001 AAA 08
9 00001 AAA 09
10 00001 AAA 0A
11 00001 AAA 0B
12 00001 AAA 0C
13 00002 BBB 01
14 00002 BBB 02
15 00002 BBB 03
也就是说No相同的记录会有N条，想把这N条用一个SubNo区分开，这个SubNo的号码
是0~9再加上A~Z的也就是说
最开始01,02，......09,0A,0B,0C,......0Z,10,11,......19,1A,1B......1Z,2A......9Z
A0............
谢谢了
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [ID] INT, [No] varchar(100), [Name] varchar(100));
insert #temp
select '1','00001','AAA' union all
select '2','00001','AAA' union all
select '3','00001','AAA' union all
select '4','00001','AAA' union all
select '5','00001','AAA' union all
select '6','00001','AAA' union all
select '7','00001','AAA' union all
select '8','00001','AAA' union all
select '9','00001','AAA' union all
select '10','00001','AAA' union all
select '11','00001','AAA' union all
select '12','00001','AAA' union all
select '13','00002','BBB' union all
select '14','00002','BBB' union all
select '15','00002','BBB'
--SQL:
;WITH cte1 AS
(
SELECT ch=CHAR(number)
FROM master..spt_values
WHERE type = 'p'
AND (number BETWEEN 48 AND 57 OR number BETWEEN 65 AND 90)
),
cte2 AS
(
SELECT rowid=ROW_NUMBER() OVER(ORDER BY a.ch+b.ch), ch=a.ch+b.ch
FROM cte1 a
CROSS JOIN cte1 b
WHERE a.ch+b.ch <> '00'
)
SELECT a.id,a.[no],a.name,b.ch FROM
(select rowid=ROW_NUMBER() OVER(PARTITION BY [No] ORDER BY ID), * FROM #temp) a
INNER JOIN cte2 b
ON a.rowid = b.rowid
ORDER BY a.ID
/*
idnonamech
100001AAA01
200001AAA02
300001AAA03
400001AAA04
500001AAA05
600001AAA06
700001AAA07
800001AAA08
900001AAA09
1000001AAA0A
1100001AAA0B
1200001AAA0C
1300002BBB01
1400002BBB02
1500002BBB03
*/