问题:将 PostgreSQL string_agg 移植到 SQL Server,ORDER BY 的问题

在 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 子查询中允许的子查询,我敢说,这是子查询中 ORDER BY 的有效用例。

解答

看起来没有比使用带有TOP <some really large number>的子查询来绕过 SQL Server 的限制更好的选择:

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