用于连接和删除公共前缀的 TSQL 查询 [英] TSQL query to concatenate and remove common prefix

问题描述

我有一些数据

id    ref
==   ==========
1    3536757616
1    3536757617
1    3536757618

想要得到结果

1    3536757616/7/8

所以基本上数据是在 id 上聚合的，refs 连接在一起，用斜杠/"分隔，但删除了任何公共前缀，所以如果数据是这样的

so essentially the data is aggregated on id, with the refs concatenated together, separated by a slash '/', but with any common prefix removed so if the data was like

id    ref
==   ==========
2    3536757628
2    3536757629
2    3536757630

我想得到结果

2    3536757629/28/30

我知道我可以简单地使用

I know I can simply concatenate the refs by using

SELECT distinct
    id,
    stuff ( ( SELECT
                  '/ ' + ref 
              FROM
                  tableA tableA_1
              where tableA_1.id = tableA_2.id
    FOR XML PATH ( '' ) ) , 1 , 2 , '' )
from TableA tableA_2

给予

1   3536757616/ 3536757617/ 3536757618
2   3536757628/ 3536757629/ 3536757630

但它删除了我所追求的共同元素......

but it's the bit that removes the common element that I'm after.....

测试数据代码:

create table tableA (id int, ref varchar(50))

insert into tableA
select 1, 3536757616
union select 1, 3536757617
union select 1, 3536757618
union select 2, 3536757628
union select 2, 3536757629
union select 2, 3536757630

推荐答案

WITH hier(cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt <= 100
        )
SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY id) = 1 THEN ref ELSE ' / ' + SUBSTRING(ref, mc + 1, LEN(ref)) END 
FROM    (
        SELECT  MIN(common) AS mc
        FROM    (
                SELECT  (
                        SELECT  MAX(cnt)
                        FROM    hier
                        WHERE   SUBSTRING(initref, 1, cnt) = SUBSTRING(ref, 1, cnt)
                                AND cnt <= LEN(ref)
                        ) AS common
                FROM    (
                        SELECT  TOP 1 ref AS initref
                        FROM    tableA
                        ) i,
                        tableA
                ) q
        ) q2, tableA
FOR XML PATH('')

---

3536757616 / 17 / 18 / 28 / 29 / 30

与组相同的事情:

WITH hier(cnt) AS
        (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt <= 100
        )
SELECT  (
        SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY a2.ref) = 1 THEN ref ELSE ' / ' + SUBSTRING(ref, mc + 1, LEN(ref)) END 
        FROM    tableA a2
        WHERE   a2.id = q2.id
        FOR XML PATH('')
        )
FROM    (
        SELECT  id, MIN(common) AS mc
        FROM    (
                SELECT  a.id,
                        (
                        SELECT  MAX(cnt)
                        FROM    hier
                        WHERE   SUBSTRING(i.initref, 1, cnt) = SUBSTRING(a.ref, 1, cnt)
                                AND cnt <= LEN(ref)
                        ) AS common
                FROM    (
                        SELECT  id, MIN(ref) AS initref
                        FROM    tableA
                        GROUP BY
                                id
                        ) i
                JOIN    tableA a
                ON      i.id = a.id
                ) q
        GROUP BY
                id
        ) q2
---
3536757616 / 7 / 8
3536757628 / 29 / 30

这篇关于用于连接和删除公共前缀的 TSQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！