将 PostgreSQL string_agg 移植到 SQL Server，ORDER BY 出现问题

发布于 2024-11-03 11:40:48 字数 1303 浏览 0 评论 0原文

在 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

有更好的解决方法吗？是的，我已经阅读了子查询中是否允许 order by 子句，我敢说，这是子查询中 ORDER BY 的一个有效用例。

原文

In PostgreSQL, there is this very useful string_agg function, that allows query like:

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

Similarly, this can be done in MySQL using group_concat.

Now, I am trying to port this into SQL Server as a CLR User-Defined Aggregate. The code itself is not a problem, as there are thousands of examples all over the web to create this particular aggregate (which beg the question: why is it not part of SQL Server already??).

The problem is, I can't find a clean way to apply the ORDER BY, since SQL Server not only doesn't support ORDER BY within aggregate function, it also forbids the use of ORDER BY in subquery. My best bet is this:

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 there a better workaround? And yes, I have read Is order by clause allowed in a subquery, and this is, dare I say, a valid use case of ORDER BY in subquery.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

决绝 2024-11-10 11:40:48

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

回复收藏 0 原文

憧憬巴黎街头的黎明 2024-11-10 11:40:48

看起来没有比使用带有 TOP 的子查询更好的替代方法来绕过 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

Looks like there is no better alternative than using a subquery with TOP <some really large number> to get around SQL Server's limitation:

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

回复收藏 0 原文

~没有更多了~