时间戳

首页 > 解决方案 > 语法 sql server

sql - 语法 sql server

问题描述

我的 sql 语法有问题，我有 3 个这样的表

表：库存

StockCode | StockName  | BeginStock
0001      | Mouse      | 5
0002      | Keyboard   | 1
0003      | MousePad   | 4

表：购买

NoDoc  | StockCode | PurchaseDate | Qty
P-0001 | 0001      | 06/12/2019   | 1
P-0002 | 0002      | 06/20/2019   | 5
P-0003 | 0002      | 06/21/2019   | 2
P-0004 | 0003      | 06/22/2019   | 1
P-0005 | 0001      | 07/01/2019   | 2

表：销售额

NoDoc  | StockCode | PurchaseDate | Qty
S-0001 | 0001      | 06/30/2019   | 1
S-0002 | 0002      | 06/30/2019   | 1
S-0003 | 0002      | 06/30/2019   | 1
S-0004 | 0003      | 06/30/2019   | 1
S-0005 | 0003      | 07/01/2019   | 1

我想如果我查询日期 >= '07/01/2019' 和日期 <='07/07/2019' 库存将是

StockCode | StockName  | BeginStock | Purchase | Sales | Actual 
0001      | Mouse      | 5          | 1        | 0     | 6
0002      | Keyboard   | 6          | 0        | 0     | 6
0003      | MousePad   | 4          | 0        | 1     | 3

我想知道月前和月后的beginstock，请帮助我如何在sql中查询。对不起，我是新手。谢谢你的帮助

标签： sqlsql-server

解决方案

假设所有股票代码都存在于 Stock 表中，您可以CROSS APPLY这样使用：

SELECT  St.StockCode, St.StockName, St.BeginStock,
        ISNULL(X.Purchase,0) AS Purchase, ISNULL(Y.Sales,0) AS Sales,
        St.BeginStock+ISNULL(X.Purchase,0)-ISNULL(Y.Sales,0) AS Actual
FROM Stock St
CROSS APPLY (
    SELECT SUM(P.Qty) AS Purchase
    FROM Purchase P
    WHERE P.StockCode=St.StockCode
    AND P.PurchaseDate BETWEEN '20190701' AND '20190707'
) X
CROSS APPLY (
    SELECT SUM(Sa.Qty) AS Sales
    FROM Sales Sa
    WHERE Sa.StockCode=St.StockCode
    AND Sa.PurchaseDate BETWEEN '20190701' AND '20190707'
) Y

或者，您可以在派生表中使用LEFT JOINwith ，如下所示：GROUP BY

SELECT  St.StockCode, St.StockName, St.BeginStock,
        ISNULL(X.Purchase,0) AS Purchase, ISNULL(Y.Sales,0) AS Sales,
        St.BeginStock+ISNULL(X.Purchase,0)-ISNULL(Y.Sales,0) AS Actual
FROM Stock St
LEFT JOIN (
    SELECT P.StockCode, SUM(P.Qty) AS Purchase
    FROM Purchase P
    WHERE P.PurchaseDate BETWEEN '20190701' AND '20190707'
    GROUP BY P.StockCode
) X ON St.StockCode=X.StockCode
LEFT JOIN (
    SELECT Sa.StockCode, SUM(Sa.Qty) AS Purchase
    FROM Sales Sa
    WHERE Sa.PurchaseDate BETWEEN '20190701' AND '20190707'
    GROUP BY Sa.StockCode
) Y ON St.StockCode=Y.StockCode

推荐阅读