时间戳

首页 > 解决方案 > STRING_AGG 未按预期运行

sql - STRING_AGG 未按预期运行

问题描述

我有以下查询：

WITH cteCountryLanguageMapping AS (
    SELECT * FROM (
        VALUES
            ('Spain', 'English'),
            ('Spain', 'Spanish'),
            ('Sweden', 'English'),
            ('Switzerland', 'English'),
            ('Switzerland', 'French'),
            ('Switzerland', 'German'),
            ('Switzerland', 'Italian')
    ) x ([Country], [Language])
)
SELECT
    [Country],
    CASE COUNT([Language])
        WHEN 1 THEN MAX([Language])
        WHEN 2 THEN STRING_AGG([Language], ' and ')
        ELSE STRING_AGG([Language], ', ')
    END AS [Languages],
    COUNT([Language]) AS [LanguageCount]
FROM cteCountryLanguageMapping
GROUP BY [Country]

我期望瑞士的 Languages 列中的值用逗号分隔，即：

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French, German, Italian, English          | 4

相反，我得到以下输出（4 个值由 分隔and）：

  | Country     | Languages                                 | LanguageCount
--+-------------+-------------------------------------------+--------------
1 | Spain       | Spanish and English                       | 2
2 | Sweden      | English                                   | 1
3 | Switzerland | French and German and Italian and English | 4

我错过了什么？

这是另一个例子：

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG(z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

  | y | STRING_AGG_PLUS | STRING_AGG_MINUS
--+---+-----------------+-----------------
1 | 1 | a+b             | a+b

这是 SQL Server 中的错误吗？

标签： sqlsql-serversql-server-2017string-aggregation

解决方案

是的，这是一个错误 (tm)，存在于 SQL Server 2017 的所有版本中（截至撰写时）。它已在 Azure SQL Server 和 2019 RC1 中修复。具体来说，优化器中执行公共子表达式消除的部分（确保我们不会计算超出必要的表达式）不正确地认为STRING_AGG(x, <separator>)只要x匹配形式的所有表达式都是相同的，不管是什么<separator>，并将它们与第一个计算的表达式统一起来查询中的表达式。

一种解决方法是通过对其执行某种（接近）身份转换来确保x不匹配。由于我们正在处理字符串，连接一个空的就可以了：

SELECT y, STRING_AGG(z, '+') AS STRING_AGG_PLUS, STRING_AGG('' + z, '-') AS STRING_AGG_MINUS
FROM (
    VALUES
        (1, 'a'),
        (1, 'b')
) x (y, z)
GROUP by y

推荐阅读