sql-server - 将 PostgreSQL string_agg 移植到 SQL Server，ORDER BY 有问题

Question

在 PostgreSQL 中，有一个非常有用的 string_agg 函数，它允许像这样的查询:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            string_agg(number, ', ' ORDER BY phones.priority)
        FROM
            phones
        WHERE
            phones.contact_id = contacts.id
    ) AS phone_numbers
FROM
    contacts

同样，这可以在 MySQL 中使用 group_concat 完成。

现在，我正在尝试将其作为 CLR 用户定义的聚合移植到 SQL Server 中。代码本身不是问题，因为网络上有数以千计的示例来创建这个特定的聚合(这引出了一个问题:为什么它还不是 SQL Server 的一部分？？)。

问题是，我找不到一个干净的方法来应用 ORDER BY，因为 SQL Server 不仅不支持聚合函数中的 ORDER BY，它还禁止在子查询中使用 ORDER BY。我最好的选择是:

SELECT
    contacts.first_name,
    contacts.last_name,
    (
        SELECT
            dbo.string_agg(number, ', ')
        FROM
            (
                SELECT TOP <some really large number>
                    number
                FROM
                    phones
                WHERE
                    phones.contact_id = contacts.id
                ORDER BY
                    phones.priority
            ) AS phones
    ) AS phone_numbers
FROM
    contacts

有更好的解决方法吗？是的，我读过Is order by clause allowed in a subquery ，我敢说，这是子查询中 ORDER BY 的一个有效用例。

Answer 1

您的最佳选择查询不会返回您想要的结果。当 ORDER BY 在这样的子查询中与 TOP 一起使用时，ORDER BY 仅用于评估顶部条件。它实际上并没有为您订购数据。