SQL 添加几行并从三个表中减去日期约束 完全卡住

Question

我正在尝试从两个不同的表中提取总和，减去它们并将它们连接到第一个表。第一个有客户，第二个按月销售，第三个返回。如果我将最后两个中的任何一个与第一个连接起来，我可以让它工作，但是当试图将整个事情放在一起时，我只会得到疯狂的数字。任何帮助将不胜感激！

第一个查询，工作正常：

SELECT  
CUST.CODE, CUST.NAME,
SUM (CASE 
WHEN (INVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) -1, -1))
THEN INVI.AMMO 
ELSE 0 
END) AS VCURK
FROM    
BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN 
BAESQL_CO3.dbo.INVI INVI ON CUST.CODE = INVI.CSTC
WHERE 
CUST.STAT = 'A' 
AND (INVI.STAT = 'E' OR INVI.STAT = 'O')
GROUP BY 
CUST.CODE, CUST.NAME
ORDER BY 
CUST.CODE

第二个查询，确定：

SELECT  
CUST.CODE, CUST.NAME,
SUM (CASE 
WHEN (DNVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1))
THEN DNVI.AMMO 
ELSE 0 
END) AS DCURK
FROM 
BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN 
BAESQL_CO3.dbo.DNVI DNVI ON CUST.CODE = DNVI.CSTC
WHERE 
CUST.STAT = 'A' 
AND (DNVI.STAT = 'E' OR DNVI.STAT = 'O')
GROUP BY 
CUST.CODE, CUST.NAME
ORDER BY 
CUST.CODE

我需要得到：

SELECT CUST.CODE, CUST.NAME, (VCURK - DCURK) as NETA

我尝试过的任何事情都奏效了....

Answer 1

只需将两个查询作为子查询连接在一起，然后进行数学运算：

SELECT [Q1].[CODE], [Q1].[NAME], COALESCE([Q1].[VCURK], 0) - COALESCE([Q2].[DCURK], 0) AS [NETA]
FROM (
SELECT  CUST.CODE, CUST.NAME,
SUM (CASE WHEN (INVI.DTTE>
DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))
THEN INVI.AMMO ELSE 0 END) AS VCURK
FROM    BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN BAESQL_CO3.dbo.INVI INVI
ON CUST.CODE = INVI.CSTC
WHERE CUST.STAT = 'A' AND (INVI.STAT = 'E' OR INVI.STAT = 'O')
GROUP BY CUST.CODE, CUST.NAME
) AS [Q1] INNER JOIN (
SELECT  CUST.CODE, CUST.NAME,
SUM (CASE WHEN (DNVI.DTTE>
DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))
THEN DNVI.AMMO ELSE 0 END) AS DCURK
FROM BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN BAESQL_CO3.dbo.DNVI DNVI
ON CUST.CODE = DNVI.CSTC
WHERE CUST.STAT = 'A' AND (DNVI.STAT = 'E' OR DNVI.STAT = 'O')
GROUP BY CUST.CODE, CUST.NAME
) AS [Q2] ON [Q1].[CODE] = [Q2].[CODE]
ORDER BY [Q1].[CODE]

Answer 2

让我们从第一个查询开始。由于我们使用LEFT OUTER JOIN我们可以将CASE条件移动到ON子句中，以及将JOINed表限制在其自身值上的WHERE子句。在子句WHERE中使用AND (INVI.STAT = 'E' OR INVI.STAT = 'O')JOINed表的条件有效地将LEFT OUTER JOIN变成INNER JOIN，但我们稍后可以解决这个问题：

SELECT  
CUST.CODE, CUST.NAME,
COALESCE(SUM(INVI.AMMO), 0) AS VCURK
FROM    
BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN 
BAESQL_CO3.dbo.INVI INVI ON CUST.CODE = INVI.CSTC
AND INVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) -1, -1)
AND (INVI.STAT = 'E' OR INVI.STAT = 'O')
WHERE 
CUST.STAT = 'A' 
GROUP BY 
CUST.CODE, CUST.NAME
ORDER BY 
CUST.CODE

请注意，现在我们有WHERE，GROUP BY和ORDER BY都只引用主CUST表。

第二个查询JOIN另一个表完全相同，因此我们可以将其JOIN到重构的第一个表中：

SELECT
CUST.CODE, CUST.NAME,
COALESCE(SUM(INVI.AMMO), 0) - COALESCE(SUM(DNVI.AMMO), 0) AS NETA
FROM
BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN
BAESQL_CO3.dbo.INVI INVI ON CUST.CODE = INVI.CSTC
AND (INVI.STAT = 'E' OR INVI.STAT = 'O')
AND INVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) -1, -1)
LEFT OUTER JOIN
BAESQL_CO3.dbo.DNVI DNVI ON CUST.CODE = DNVI.CSTC
AND (DNVI.STAT = 'E' OR DNVI.STAT = 'O')
AND DNVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)
WHERE 
CUST.STAT = 'A' 
GROUP BY 
CUST.CODE, CUST.NAME
ORDER BY 
CUST.CODE

现在，与原始查询的唯一区别是，在此具有NETA = 0的组合查询中，没有一个JOINed 表具有匹配记录的记录仍返回。为了复制原始结果，我们可以(如果实际需要)在子句中使用一个额外的条件WHERE：

SELECT
CUST.CODE, CUST.NAME,
COALESCE(SUM(INVI.AMMO), 0) - COALESCE(SUM(DNVI.AMMO), 0) AS NETA
FROM BAESQL_CO3.dbo.CUST CUST
LEFT OUTER JOIN
BAESQL_CO3.dbo.INVI INVI ON CUST.CODE = INVI.CSTC
AND (INVI.STAT = 'E' OR INVI.STAT = 'O')
AND INVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) -1, -1)
LEFT OUTER JOIN
BAESQL_CO3.dbo.DNVI DNVI ON CUST.CODE = DNVI.CSTC
AND (DNVI.STAT = 'E' OR DNVI.STAT = 'O')
AND DNVI.DTTE > DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)
WHERE 
CUST.STAT = 'A'
AND (
INVI.STAT IS NOT NULL OR INVI.STAT IS NOT NULL OR
DNVI.STAT IS NOT NULL OR DNVI.STAT IS NOT NULL
)
GROUP BY 
CUST.CODE, CUST.NAME
ORDER BY 
CUST.CODE

这样，查询不仅更短、更易读，而且效率也更高：没有CASE语句，只有一个GROUP BY和日期限制的JOINed 表扫描。