为两个查询中的每一行增加一列

Question

我有两个查询，分别给出客户购买的工作单元数量和消耗的工作单元的数量。

我正在开发SQL Server 2014

WUBought查询返回如下示例：

Customer    Year    Month   UnitBought
Cust1       2015    6       50
Cust2       2014    7       100
Cust1       2013    10      30
Cust3       2015    2       40

另一个查询返回客户端使用的号码：

Customer    Year    Month   UnitConsumed
Cust1       2015    2       6
Cust1       2015    5       20
Cust2       2015    3       8
Cust1       2015    4       3
Cust3       2015    2       10

我基本上想做的是，把每个月的购买量减去消耗量。以下是我想要的Cust1:前六个月的结果示例

Customer    Year    Month   Remaining
Cust1       2015    1       30
Cust1       2015    2       24
Cust2       2015    3       24
Cust1       2015    4       21
Cust3       2015    5       1
Cust3       2015    6       51

该查询从每月列出的表中返回使用UNION ALL购买的WU，以在没有值的情况下获得每月：

SELECT Customer, [Year], [Month], SUM(UOBought) AS UORest
    FROM WU_Bought
    GROUP BY [Customer], [PurchaseDate]
UNION ALL
SELECT '' AS Customer, [Year], [Month], '' AS UORest
    FROM  Months
    GROUP BY [Year], [Month]

以下是每个月购买的每个单元的总和查询，使用相同的联合语句：

SELECT Customer, [Year], [Month], SUM(TotalConsumed) * -1 AS UORest
    FROM WUConsumed
    GROUP BY Customer, Year, Month
UNION ALL
SELECT '' AS Customer, [Year], [Month], '' AS UORest
    FROM EveryMonths
    GROUP BY Year, Month

现在我想我必须调整第一个，迫使它保持之前的总和，但我不确定我该怎么做。

Answer 1

这对你有用吗？

SELECT b.customer_id, b.year, b.month, SUM(b.units_bought) AS units_bought, ISNULL(c.units_consumed,0) AS units_consumed, SUM(b.units_bought) - ISNULL(c.units_consumed,0) AS units_remaining
    FROM Bought b
    LEFT JOIN Consumed c
        ON b.customer_id = c.customer_id AND b.year = c.year AND b.month = c.month
GROUP BY b.customer_id, b.year, b.month

Answer 2

好的，我开始工作了。

我所做的非常"简单"，使用了自2012年起提供的SQL Server功能：前面的无界行

这里有一篇关于这个功能的非常清晰的文章。

我创建了另一个视图，用UNION ALL子句对消耗和购买单位的查询结果进行分组，称为"WU_Closing_view"，然后在其中使用ROWS UNBOUNDED PREEDING：

SELECT Customer, Year, Month, SUM(Closing) OVER(PARTITION BY Customer ORDER BY Year, Month ROWS UNBOUNDED PRECEDING) AS Closing
    FROM WU_Closing_View
    GROUP BY Customer, Year, Month, Closing
UNION ALL
SELECT     '' AS Customer, Year, Month, '' AS Sum_bought
    FROM         Months
    GROUP BY Year, Month
    ORDER BY Customer, Year, Month

请注意，我使用了PARTITION BY，以便按客户端求和。因为我想在SSRS矩阵中显示每个月，所以我添加了一个"UNION ALL"，指向一个空客户的表，该表上有从2010年到2017年的每个年份和月份。但如果你不需要每个月都进行进化，这是可选的。

也许有一种更简单的方法，但这就是我迄今为止找到的方法。