sql - 如何使用标准 SQL 连接以逗号分隔的行？

Question

假设我们有一张表 T1 和一张表 T2。 T1 和 T2 之间存在 1:n 的关系。我想选择所有 T1 及其所有 T2，每一行对应于 T1 记录，T2 值串联，仅使用 SQL 标准操作。

例子:T1 = 人T2 = 流行度(按年份)

每一年都有一定的人气

我想使用 SQL 标准操作编写一个选择，结果如下:

Person.Name    Popularity.Value
John Smith     1.2,5,4.2
John Doe       NULL
Jane Smith     8

其中 John Smith 的流行度表中有 3 条记录，John Doe 没有一条记录，Jane Smith 有一条记录，它们的值是上面表示的值。这可能吗？怎么办？

我正在使用 Oracle，但只想使用标准 SQL 来执行此操作。

Answer 1

这是一种技术，使用递归公用表表达式。不幸的是，我对它的性能没有信心。

我确信有改进此代码的方法，但它表明似乎没有一种简单的方法可以仅使用 SQL 标准来执行此类操作。

据我所知，确实应该有某种可与 GROUP BY 一起使用的 STRINGJOIN 聚合函数。这会让事情变得更容易......

---

此查询假定存在某种连接这两个关系的 PersonID，但 Name 也可以。

WITH cte (id, Name, Value, ValueCount) AS (
    SELECT id,
        Name,
        CAST(Value AS VARCHAR(MAX)) AS Value,
        1 AS ValueCount
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS id,
            Name,
            Value
        FROM Person AS per
        INNER JOIN Popularity AS pop
            ON per.PersonID = pop.PersonID
    ) AS e
    WHERE id = 1

    UNION ALL

    SELECT e.id,
        e.Name,
        cte.Value + ',' + CAST(e.Value AS VARCHAR(MAX)) AS Value,
        cte.ValueCount + 1 AS ValueCount
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) AS id,
            Name,
            Value
        FROM Person AS per
        INNER JOIN Popularity AS pop
            ON per.PersonID = pop.PersonID
    ) AS e
    INNER JOIN cte
        ON e.id = cte.id + 1
            AND e.Name = cte.Name
)
SELECT p.Name, agg.Value
FROM Person p
LEFT JOIN (
    SELECT Name, Value
    FROM (
        SELECT Name,
            Value,
            ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ValueCount DESC)AS id
        FROM cte
    ) AS p
    WHERE id = 1
) AS agg
    ON p.Name = agg.Name

这是一个示例结果:

--------------------------------
| Name        | Value          |
--------------------------------
| John Smith  | 1.2,5,4.2      |
--------------------------------
| John Doe    | NULL           |
--------------------------------
| Jane Smith  | 8              |
--------------------------------