UNION SQL的SQL Server问题

I have an issue with my query.

我的查询有问题。

Msg 205, Level 16, State 1, Line 5 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

消息205，级别16，状态1，行5使用UNION，INTERSECT或EXCEPT运算符组合的所有查询在其目标列表中必须具有相同数量的表达式。

I'm basic user (still learning SQL).

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

I have combined few Selects together here my code:

我在这里结合了几个选择我的代码：

I want to get beside of FCRR the Category.

我想在FCRR旁边找到类别。

I have tried many combinations but failed :(

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

Thank you for your help,

感谢您的帮助，

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

3 个解决方案

#1

Hello the comment from B House helped me :)

您好B House的评论帮助了我:)

Here the solution:

这里的解决方案：

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

#2

Your first SELECT has 2 columns, [FCRR] and Category. The 2 other SELECT that you are trying to UNION ALL must have this 2 columns (or at least the same data type). Both are missing the one corresponding to the Category column.

您的第一个SELECT有2列，[FCRR]和Category。您尝试UNION ALL的另外两个SELECT必须具有此2列（或至少相同的数据类型）。两者都缺少与Category列对应的那个。

You can solve it by adding a default hard-coded value (SELECT cast(100 * ([FCRR] / CASESCOUNT) AS DECIMAL(5, 2)) AS [FCRR], 0 as Category) or actually selecting them from your data tables, if it corresponds.

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

#3

The first rule of debugging: simplify the problem.

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

• You have 3 parts UNIONed together; try combinations of just two of them first, to reduec the amount you have to look at.
• 你有3个部分UNIONed在一起;首先尝试其中两个的组合，以重新计算您需要查看的金额。
• Run each query separately and see how many columns it returns. This is what the error message means by "number of expressions in their target lists".
• 单独运行每个查询并查看它返回的列数。这就是错误消息所指的“目标列表中的表达式数量”。
• Build up from simpler queries, understanding what you're adding each time.
• 从简单的查询构建，了解每次添加的内容。