时间戳

首页 > 解决方案 > 如何在行号之间添加破折号，在非行号之间添加逗号

tsql - 如何在行号之间添加破折号，在非行号之间添加逗号

问题描述

我想在适当的地方用逗号和连字符替换一组运行和非运行数字。

使用STUFF&XML PATH我能够通过获得类似1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 15, 19, 20, 21, 22, 24.

WITH CTE AS (  
SELECT DISTINCT t1.ORDERNo, t1.Part, t2.LineNum  
FROM [DBName].[DBA].Table1 t1    
JOIN Table2 t2 ON t2.Part = t1.Part    
WHERE t1.ORDERNo = 'AB12345') 

SELECT c1.ORDERNo, c1.Part, STUFF((SELECT ', ' + CAST(LineNum AS VARCHAR(5))  
FROM CTE c2  
WHERE c2.ORDERNo= c1.ORDERNo
FOR XML PATH('')), 1, 2, '') AS [LineNums]  
FROM CTE c1  
GROUP BY c1.ORDERNo, c1.Part

这是一些示例输出：

ORDERNo Part        LineNums
ON5650  PT01-0181   5, 6, 7, 8, 12
ON5652  PT01-0181   1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 13, 15, 19, 20, 21, 22, 24
ON5654  PT01-0181   1, 4
ON5656  PT01-0181   1, 2, 4
ON5730  PT01-0181   1, 2
ON5253  PT16-3934   1, 2, 3, 4, 5
ON1723  PT02-0585   1, 2, 3, 6, 8, 9, 10

想拥有：

OrderNo Part        LineNums
ON5650  PT01-0181   5-8, 12
ON5652  PT01-0181   1-10, 13, 15, 19-22, 24
ON5654  PT01-0181   1, 4
ON5656  PT01-0181   1-2, 4
ON5730  PT01-0181   1-2
ON5253  PT16-3934   1-5
ON1723  PT02-0585   1-3, 6, 8-10

标签： tsql

解决方案

这是一个典型的问题。
（关于这个主题的好读物是来自 SQL Server MVP Deep Dives的 Itzik Ben-Gan 的Gaps and island）

这个想法是您首先需要识别连续数字组。一旦你做到了，剩下的就很容易了。

首先，创建并填充示例表（请在以后的问题中保存我们这一步）：

DECLARE @T AS TABLE
(
    N int
);

INSERT INTO @T VALUES
(1), (2), (3), (4), 
(6), 
(8), 
(10), (11), 
(13), (14), (15), 
(17), 
(19), (20), (21), 
(25);

然后，使用公用表表达式来标识组。

With Grouped AS
(
    SELECT N,
           N - ROW_NUMBER() OVER(ORDER BY N) As Grp
    FROM @T
)

如果这个 cte 是这样的结果：

N   Grp
1   0
2   0
3   0
4   0
6   1
8   2
10  3
11  3
13  4
14  4
15  4
17  5
19  6
20  6
21  6
25  9

如您所见，虽然数字是连续的，但grp值保持不变。
当一行的数字与前一个数字不连续时，该grp值会发生变化。

然后，您从该 cte 中进行选择，使用case表达式选择单个数字（如果它是其组中唯一的数字）或组的开始和结束，用破折号分隔：

SELECT STUFF(
(
    SELECT ', ' +
           CASE WHEN MIN(N) = MAX(N) THEN CAST(MIN(N) as varchar(11))
           ELSE CAST(MIN(N) as varchar(11)) +'-' + CAST(MAX(N) as varchar(11)) 
           END
    FROM Grouped   
    GROUP BY grp
    FOR XML PATH('')
), 1, 2, '')  As GapsAndIslands

结果：

GapsAndIslands
1-4, 6, 8, 10-11, 13-15, 17, 19-21, 25

推荐阅读