主页 / dba / 问题 / 187115
Accepted
M.I.M.F
Asked: 2017-09-28 21:23:04 +0800 CST

从两个以上的表格中获取摘要（库存变动报告）

  • 772

我有一个 2 表，

1- 内表

CREATE TABLE Inward
(
    GoodsInwardId BIGINT DEFAULT NEXT VALUE FOR SEQ_GoodsInwardId NOT NULL UNIQUE,
    GoodsInwardDate DATE NOT NULL,
    InventoryId BIGINT NOT NULL,
    Quantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsInwardId)
)

2- 外桌

CREATE TABLE GoodsOutward
(
    GoodsOutwardId BIGINT DEFAULT NEXT VALUE FOR SEQ_GoodsOutwardId NOT NULL UNIQUE,
    GoodsOutwardDate DATE NOT NULL,
    InventoryId BIGINT NOT NULL,
    Quantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsOutwardId)
)

在我在两个表结果中填充数据后，例如，

在此处输入图像描述

我的问题是，我如何总结查询，例如下面的屏幕截图。

在此处输入图像描述

sql-server-2008-r2 sql-server-2016

2 个回答

  1. 您可以假定这两个表然后 FULL OUTER JOIN

    SELECT isnull(i.Date, o.Date) AS Date, 
    isnull(i.InventoryId, o.InventoryId) AS InventoryId,
    i.Qty InwardQty ,
    o.Qty OutwardQty
FROM
(    SELECT GoodsInwardDate AS Date, InventoryId, SUM(Quantity) AS Qty
    FROM Inward
    GROUP BY Date, InventoryId
) AS i
FULL OUTER JOIN
(    SELECT GoodsOutwardDate AS Date, InventoryId, SUM(Quantity) AS Qty
    FROM GoodsOutward
    GROUP BY Date, InventoryId
) AS o 
ON i.Date=o.Date AND i.InventoryId=o.InventoryId
ORDER BY isnull(i.Date, o.Date)
    • 3
  2. Best Answer

    尝试这个

    SELECT Date, InventoryID, SUM(InwardQTY), SUM(OutwardQTY)
FROM (
select Date, InventoryID, SUM(QTY) as InwardQTY, NULL as OutwardQTY 
from Inward
group by Date, InventoryID
UNION ALL
select Date, InventoryID, NULL as InwardQTY, SUM(QTY) as OutwardQTY 
from Outward
group by Date, InventoryID
) A
GROUP BY Date, InventoryID
ORDER BY Date, InventoryID
    • 1

相关问题