我有一个 T-SQL 查询,我需要对 CASE
语句的计数进行总计。
我尝试添加 UNION,但出现错误:
包含 UNION 运算符的 SQL 语句中的所有查询都必须具有 目标列表中的表达式数量相等。
有什么想法吗? 谢谢。
查询:
SELECT
CustomerID, Name, DueDate,
CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 1
THEN PaymentAmount ELSE 0
END AS [Early],
CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >=0
THEN PaymentAmount ELSE 0
END AS [On Time],
CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) = -1
THEN PaymentAmount ELSE 0
END AS [Late]
FROM
Customers
WHERE
DATEDIFF(MONTH, PaymentDate,DueDate), GetDate()) = 1
AND PaymentAmount= DuesAmount
UNION
SELECT
'-Total', '', CustomerID, Name, DueDate,
SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 1
THEN PaymentAmount ELSE 0 END) AS [Early],
SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) >= 0
THEN PaymentAmount ELSE 0 END) AS [On Time],
SUM(CASE WHEN DATEDIFF(Month, PaymentDate, DueDate) = -1
THEN PaymentAmount ELSE 0 END) AS [Late]
FROM
Customers
WHERE
DATEDIFF(MONTH, PaymentDate,DueDate), GetDate()) = 1
AND PaymentAmount = DuesAmount
错误显示:"包含UNION
运算符的 SQL 语句中的所有查询在其目标列表中必须具有相同数量的表达式。 这意味着每个SELECT
必须返回相同数量的表达式。
在您提供给我们的代码示例中,第一个 SELECT
语句返回 6 个表达式,而第二个语句返回 8 个表达式:
SELECT CustomerID, -- 1
Name, -- 2
DueDate, -- 3
CASE ... END AS [Early], -- 4
CASE ... END AS [On Time], -- 5
CASE ... END AS [Late] -- 6
...
UNION
SELECT '-Total', -- 1
'', -- 2
CustomerID, -- 3
Name, -- 4
DueDate, -- 5
SUM(CASE ... END) AS [Early], -- 6
SUM(CASE ... END) AS [On Time], -- 7
SUM(CASE ... END) AS [Late] -- 8
...
看看第一SELECT
如何返回 6 个表达式,但第二个返回 8 个表达式? 在UNION
中,所有SELECT
语句必须返回相同数量的表达式。
如有必要,可以在第一个查询中为第二个查询中不匹配的列返回NULL
。 例如:
SELECT NULL as [RowType], -- 1
NULL as [Padding], -- 2
CustomerID, -- 3
Name, -- 4
DueDate, -- 5
CASE ... END AS [Early], -- 6
CASE ... END AS [On Time], -- 7
CASE ... END AS [Late] -- 8
...
UNION
SELECT '-Total', -- 1
'', -- 2
CustomerID, -- 3
Name, -- 4
DueDate, -- 5
SUM(CASE ... END) AS [Early], -- 6
SUM(CASE ... END) AS [On Time], -- 7
SUM(CASE ... END) AS [Late] -- 8
...
另请注意,"到期日期"列后没有逗号。