34,575
社区成员
发帖
与我相关
我的任务
分享
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
/*
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
*/