SQL中使用UNION时，如何对相同ID的字符串字段进行拼接？

看你的SQL结构，现在是用UNION ALL把两个查询的结果堆在一起，但想要把相同HVID的记录合并，把IMPORT_MESSAGE里的提示文本拼接起来对吧？同时应该还要处理IMPORT_STATUS这类状态字段——比如只要有一条记录是错误状态（0），最终就显示错误状态？

核心思路很简单：先把两个SELECT的结果通过UNION ALL汇总成一个临时数据集，再对这个数据集按HVID分组，用对应数据库的字符串聚合函数来拼接消息，同时根据需求处理其他字段。

步骤1：先整理合并后的基础数据集 #

用CTE（公共表表达式）把你的两个查询结果先合并，确保字段完全对齐：

WITH combined_results AS (
    SELECT DISTINCT 
        V1.FORPDM_ID AS HVID, 
        1 AS IMPORT_STATUS, 
        V1.CREATEDAT AS IMPORT_DATE, 
        'ok' AS IMPORT_MESSAGE, 
        V1.ID AS AT_ID, 
        V1.FORPDM_TRANSACTION_ID AS TRANSACTION_ID 
    FROM V_VALIDATIONCARRIER V1
    JOIN V_FORPDM_DAVE_HWDEMAND V2 ON V1.FORPDM_ID = V2.HVID
    -- ...你的其他筛选条件...
    UNION ALL
    SELECT DISTINCT 
        V1.FORPDM_ID AS HVID, 
        0 AS IMPORT_STATUS, 
        V1.CREATEDAT AS IMPORT_DATE, 
        'PC_ID is null or not in V_PLANNINGCATEGORY' AS IMPORT_MESSAGE, 
        V1.ID AS AT_ID, 
        V1.FORPDM_TRANSACTION_ID AS TRANSACTION_ID 
    FROM V_VALIDATIONCARRIER V1
    -- ...这里放对应错误场景的筛选条件，比如PC_ID为空或不在V_PLANNINGCATEGORY中
)

步骤2：分组拼接字符串并处理其他字段 #

不同数据库的字符串聚合函数不一样，下面是主流数据库的实现方式：

1. MySQL/MariaDB：用GROUP_CONCAT #

SELECT 
    HVID,
    -- 只要有一条错误状态（0），最终状态就设为0；全是正常就显示1
    MIN(IMPORT_STATUS) AS IMPORT_STATUS,
    -- 取最晚的导入日期，你也可以换成MIN取最早的
    MAX(IMPORT_DATE) AS IMPORT_DATE,
    -- 拼接消息，用逗号分隔，加DISTINCT避免重复内容
    GROUP_CONCAT(DISTINCT IMPORT_MESSAGE SEPARATOR ', ') AS IMPORT_MESSAGE,
    -- 对于ID类字段，同样可以用GROUP_CONCAT拼接，或者根据需求取任意一个
    GROUP_CONCAT(DISTINCT AT_ID SEPARATOR ', ') AS AT_ID,
    GROUP_CONCAT(DISTINCT TRANSACTION_ID SEPARATOR ', ') AS TRANSACTION_ID
FROM combined_results
GROUP BY HVID;

2. PostgreSQL：用STRING_AGG #

SELECT 
    HVID,
    MIN(IMPORT_STATUS) AS IMPORT_STATUS,
    MAX(IMPORT_DATE) AS IMPORT_DATE,
    STRING_AGG(DISTINCT IMPORT_MESSAGE, ', ') AS IMPORT_MESSAGE,
    -- 数字类型的ID要转成文本才能拼接
    STRING_AGG(DISTINCT AT_ID::TEXT, ', ') AS AT_ID,
    STRING_AGG(DISTINCT TRANSACTION_ID::TEXT, ', ') AS TRANSACTION_ID
FROM combined_results
GROUP BY HVID;

3. SQL Server 2017+：用STRING_AGG #

SELECT 
    HVID,
    MIN(IMPORT_STATUS) AS IMPORT_STATUS,
    MAX(IMPORT_DATE) AS IMPORT_DATE,
    STRING_AGG(DISTINCT IMPORT_MESSAGE, ', ') WITHIN GROUP (ORDER BY IMPORT_MESSAGE) AS IMPORT_MESSAGE,
    -- 数字转字符串后拼接
    STRING_AGG(DISTINCT CAST(AT_ID AS VARCHAR(50)), ', ') AS AT_ID,
    STRING_AGG(DISTINCT CAST(TRANSACTION_ID AS VARCHAR(50)), ', ') AS TRANSACTION_ID
FROM combined_results
GROUP BY HVID;

如果是SQL Server 2016及更早版本，需要用STUFF + FOR XML PATH的老方法：

SELECT 
    cr.HVID,
    MIN(cr.IMPORT_STATUS) AS IMPORT_STATUS,
    MAX(cr.IMPORT_DATE) AS IMPORT_DATE,
    -- 拼接IMPORT_MESSAGE
    STUFF((
        SELECT DISTINCT ', ' + IMPORT_MESSAGE
        FROM combined_results cr2
        WHERE cr2.HVID = cr.HVID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS IMPORT_MESSAGE,
    -- 同理处理AT_ID
    STUFF((
        SELECT DISTINCT ', ' + CAST(AT_ID AS VARCHAR(50))
        FROM combined_results cr2
        WHERE cr2.HVID = cr.HVID
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS AT_ID
FROM combined_results cr
GROUP BY cr.HVID;

4. Oracle：用LISTAGG #

SELECT 
    HVID,
    MIN(IMPORT_STATUS) AS IMPORT_STATUS,
    MAX(IMPORT_DATE) AS IMPORT_DATE,
    LISTAGG(DISTINCT IMPORT_MESSAGE, ', ') WITHIN GROUP (ORDER BY IMPORT_MESSAGE) AS IMPORT_MESSAGE,
    -- 数字转字符串后拼接
    LISTAGG(DISTINCT TO_CHAR(AT_ID), ', ') WITHIN GROUP (ORDER BY AT_ID) AS AT_ID,
    LISTAGG(DISTINCT TO_CHAR(TRANSACTION_ID), ', ') WITHIN GROUP (ORDER BY TRANSACTION_ID) AS TRANSACTION_ID
FROM combined_results
GROUP BY HVID;

几个关键注意点 #

• 状态字段处理：这里用MIN(IMPORT_STATUS)是因为0比1小，只要有一条错误记录，最终状态就会显示0，完全符合你的错误逻辑；如果需求不同可以换成MAX()或者其他聚合方式。
• 去重拼接：如果同一个HVID可能出现重复的消息，一定要加DISTINCT避免重复拼接相同内容。
• 数据类型转换：如果要拼接的字段是数字/日期类型，必须先转成字符串，否则会报错。

内容的提问来源于stack exchange，提问作者Rexam