UNION SQL 的 SQL 服务器问题

我的查询有问题。

Msg 205，级别 16，状态 1，第 5 行使用 Union 组合的所有查询，相交或 EXCEPT 运算符必须具有相等数量的表达式在他们的目标列表中。

我是基本用户(仍在学习SQL(。

我在这里将几个选择组合在一起，我的代码：

我想在FCRR类别旁边。

我尝试了很多组合，但失败了:(

谢谢你的帮助，

SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
    ,CATEGORY
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'1 Time' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = '1 Time' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2
UNION ALL
SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'2-3 Times' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = '2-3 Times' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2
UNION ALL
SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'More than 3 Times' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = 'More than 3 Times' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2

你好，B House的评论帮助我:)

这是解决方案：

SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
    ,CATEGORY
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'1 Time' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = '1 Time' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2
UNION ALL
SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
,CATEGORY
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'2-3 Times' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = '2-3 Times' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2
UNION ALL
SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR]
,CATEGORY
FROM (
    SELECT cast(count(*) AS DECIMAL(5, 2)) AS [FCRR]
        ,'More than 3 Times' AS [CATEGORY]
        ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH_NAME]
        ,MONTH([TICKET_CLOSED]) AS [MONTH_NUMBER]
        ,YEAR([TICKET_CLOSED]) AS [YEARS]
    FROM [PL_Survey]
    WHERE [QUESTION6] = 'More than 3 Times' AND MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
    GROUP BY Month([TICKET_CLOSED])
        ,YEAR([TICKET_CLOSED])
        ,DATENAME(MONTH, [TICKET_CLOSED])
    ) AS T1
    ,(
        SELECT cast(count(*) AS DECIMAL(5, 2)) AS CASESCOUNT
            ,DATENAME(MONTH, [TICKET_CLOSED]) AS [MONTH NAME]
            ,MONTH([TICKET_CLOSED]) AS [MONTH]
            ,YEAR([TICKET_CLOSED]) AS [YEAR]
        FROM [PL_Survey]
        WHERE MONTH([TICKET_CLOSED]) = @MONTH AND [SUPPORT_GROUP] IN (@SUPPORT_GROUP)
        GROUP BY Month([TICKET_CLOSED])
            ,YEAR([TICKET_CLOSED])
            ,DATENAME(MONTH, [TICKET_CLOSED])
        ) AS T2

您的第一个SELECT有 2 列，[FCRR] 和类别。您尝试UNION ALL的其他 2 SELECT必须具有这 2 列(或至少具有相同的数据类型(。两者都缺少与"类别"列对应的一个。

您可以通过添加默认的硬编码值 ( SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR], 0 as Category ( 或从数据表中实际选择它们(如果它对应(来解决它。

调试的第一条规则：简化问题。

你有3个部分UNION在一起;首先尝试其中两个的组合，以重新确定你必须查看的数量。
单独运行每个查询，并查看它返回多少列。这就是错误消息中"目标列表中的表达式数"的含义。
从更简单的查询开始构建，了解每次要添加的内容。

相关内容

最新更新

热门标签：