如何在连续数字和非连续数字之间的逗号之间添加破折号 [英] How to add a dash between running numbers and comma between non-running numbers

问题描述

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

I would like to replace a set of running and non running numbers with commas and hyphens where appropriate.

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

Using STUFF & XML PATH I was able to accomplish some of what I want by getting something like 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

推荐答案

结合我已有的东西并使用 Zohar Peled 的代码，我终于找到了一个解决方案:

Combining what I already had and using Zohar Peled's code I was finally able to figure out a solution:

WITH cteLineNums AS (
SELECT TOP 100 PERCENT t1.OrderNo, t1.Part, t2.LineNum
, (t2.line_number - ROW_NUMBER() OVER(PARTITION BY t1.OrderNo, t1.Part ORDER BY t1.OrderNo, t1.Part, t2.LineNum)) AS RowSeq
FROM [DBName].[DBA].Table1 t1    
JOIN Table2 t2 ON t2.Part = t1.Part    
WHERE t1.OrderNo = 'AB12345')
GROUP BY t1.OrderNo, t1.Part, t2.LineNum
ORDER BY t1.OrderNo, t1.Part, t2.LineNum)

SELECT OrderNo, Part
,  STUFF((SELECT ', ' +
       CASE WHEN MIN(line_number) = MAX(line_number) THEN CAST(MIN(line_number) AS VARCHAR(3))
             WHEN MIN(line_number) = (MAX(line_number)-1) THEN CAST(MIN(line_number) AS VARCHAR(3)) + ', ' + CAST(MAX(line_number) AS VARCHAR(3)) 
       ELSE CAST(MIN(line_number) AS VARCHAR(3)) + '-' + CAST(MAX(line_number) AS VARCHAR(3)) 
       END
    FROM cteLineNums c1
        WHERE c1.OrderNo = c2.OrderNo
        AND c1.Part = c2.Part
    GROUP BY OrderNo, Part
        ORDER BY OrderNo, Part
    FOR XML PATH('')), 1, 2, '') AS [LineNums]
FROM cteLineNums c2
GROUP BY OrderNo, Part

我使用了 ROW_NUMBER() OVER PARTITION BY，因为我返回了多个具有不同订单号和零件号的记录.所有这些导致我仍然必须在第二部分中进行自我连接，以便为每条记录显示正确的 LineNums.CASE 语句中的第二个 WHEN 是由于代码默认显示 2, 5, 8-9, 14 之类的东西，而应该是 2, 5, 8, 9, 14.

I used the ROW_NUMBER() OVER PARTITION BY since I returned multiple records with different Order Numbers and Part Numbers. All this lead to me still having to do the self join in the second part in order to get the correct LineNums to show for each record. The second WHEN in the CASE statement is due to the code defaulting to having something like 2, 5, 8-9, 14 displayed when it should be 2, 5, 8, 9, 14.

这篇关于如何在连续数字和非连续数字之间的逗号之间添加破折号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！