现在有这样一个表
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............谢谢了
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............谢谢了
http://bbs.csdn.net/topics/390568528前几天刚有人问这样的问题
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
/*
id no name ch
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
*/