使用UNION ALL合并子查询时报错：字符转uniqueidentifier失败

问题原因 #

两个查询单独运行正常，但合并时报错的核心原因是两个结果集的Owner列数据类型不匹配：

• 第一个查询的Owner来自incident.owneridname，属于字符串类型（通常是用户名称文本）
• 第二个查询的Owner来自incident.svb_cssowner，属于uniqueidentifier类型（GUID格式的用户ID）

当使用UNION ALL时，SQL Server会自动对齐两个结果集的列类型，尝试将字符串类型的owneridname转换为uniqueidentifier，但owneridname是普通文本（并非合法GUID格式），因此触发转换失败错误。

解决方案 #

需要让两个结果集的Owner列类型统一，推荐将uniqueidentifier类型的svb_cssowner转换为字符串类型，修改后的完整SQL如下：

SELECT * 
FROM (
        SELECT 
            Table1.[Owner]
        FROM
            (
            SELECT
                owneridname [Owner], 
                incidentid [Ticket Number1]
                FROM
                    incident
            ) as Table1
            LEFT JOIN
            (
                SELECT
                    MAX(DATEADD(hh,-7,svb_timestamp)) [Time Stamp],
                    svb_caseid [Ticket Number2]
                FROM
                    svb_caseupdate
                GROUP BY svb_caseid
            ) as Table2
         ON Table1.[Ticket Number1] = Table2.[Ticket Number2]
) sq1
         
UNION ALL

SELECT *
FROM (
         SELECT 
            Table1.[Owner]
         FROM
            (
            -- 将uniqueidentifier类型转换为字符串
            SELECT
                CAST(svb_cssowner AS VARCHAR(36)) [Owner], 
                incidentid [Ticket Number1]
                FROM
                    incident
            ) as Table1
            LEFT JOIN
            (
                SELECT
                    MAX(DATEADD(hh,-7,svb_timestamp)) [Time Stamp],
                    svb_caseid [Ticket Number2]
                FROM
                    svb_caseupdate
                GROUP BY svb_caseid
            ) as Table2
         ON Table1.[Ticket Number1] = Table2.[Ticket Number2]
) sq2

简化优化 #

你的查询外层SELECT *仅返回Owner列，可以去掉多余嵌套，让逻辑更清晰：

SELECT 
    Table1.[Owner]
FROM
    (
    SELECT
        owneridname [Owner], 
        incidentid [Ticket Number1]
        FROM
            incident
    ) as Table1
    LEFT JOIN
    (
        SELECT
            MAX(DATEADD(hh,-7,svb_timestamp)) [Time Stamp],
            svb_caseid [Ticket Number2]
        FROM
            svb_caseupdate
        GROUP BY svb_caseid
    ) as Table2
 ON Table1.[Ticket Number1] = Table2.[Ticket Number2]

UNION ALL

SELECT 
    Table1.[Owner]
FROM
    (
    SELECT
        CAST(svb_cssowner AS VARCHAR(36)) [Owner], 
        incidentid [Ticket Number1]
        FROM
            incident
    ) as Table1
    LEFT JOIN
    (
        SELECT
            MAX(DATEADD(hh,-7,svb_timestamp)) [Time Stamp],
            svb_caseid [Ticket Number2]
        FROM
            svb_caseupdate
        GROUP BY svb_caseid
    ) as Table2
 ON Table1.[Ticket Number1] = Table2.[Ticket Number2]

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