sql - SQL 聚合查询，其中数据位于可能有也可能没有记录的连接表中

Question

背景：我有一个系统可以跟踪订购并运送到各个地方的库存。有一个 Inventory 表，其中包含所有库存特定属性以及每个项目最初订购的数量：

库存表

CREATE TABLE Inventory
(
    InventoryID int, 
    InventoryName varchar, 
    AmtOrdered float, 
    Weight float, 
    Color string
)

然后我有一个 InventoryTransportation 表，它充当这些 Ordered 项目到达终点时的移动历史记录

库存运输表

CREATE TABLE InventoryTransportation
(
    InventoryTransportationId int,
    fkInventoryId int, 
    AmtTransported float, 
    FromLocation string, 
    ToLocation string
)

这些物品经过定义的位置才能到达最终位置。（供应商 -> 仓库，供应商 -> 站点，仓库 -> 站点，仓库 -> 供应商，站点 -> 供应商）。

我需要计算的列是供应商数量、仓库数量和现场数量。

目前，我只是返回所有历史数据并在我的业务层中计算这些值。但是如果我只是在数据库端这样做会容易得多，这可能会比我做的效率更高。

我现在计算的方式是：

供应商数量 = AmtOrdered + Sum(AmtTransported where ToLocation = Vendor) - Sum(AmtTransported where FromLocation = Vendor)

仓库数量 = Sum(AmtTransported where ToLocation = Warehouse) - Sum(AmtTransported where FromLocation = Warehouse)

现场数量 = Sum(AmtTransported where ToLocation = Site) - Sum(AmtTransported where FromLocation = Site)

问题 有没有办法编写一个查询，将 Sums 作为与它们所属的 Inventory 记录关联的列返回？

我开始使用 Left Join 子查询进行计算，但我不知道如何使用 +if 它来自某个位置或 -if 它到公式的某个位置部分来进行聚合总和，并且我不知道如何从每个数量的子查询（或者如果我必须使用多个子查询）。

我认为要做多列，我必须弄清楚如何使用 SQL Pivot 命令，但我还没有完全理解如何使用它。

任何帮助将不胜感激

更新 目前，为此，我添加了一系列 LeftJoins 来分别计算每个单独的指标。它有效，但我不知道这是否是正确的方法。

SELECT
    Ordered = ps.quantity
    ,[@Vendor] = ps.Quantity - COALESCE(psrToVendor.ToVendor, 0) - COALESCE(psrFromVendor.FromVendor, 0)
FROM Project.ProjectSpec ps
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,ToVendor = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.ToProjectSpecLocationID = 1
    GROUP BY psrd.ProjectSpecID
) psrToVendor ON psrToVendor.ProjectSpecID = ps.ID
LEFT JOIN
(
    SELECT
        psrd.ProjectSpecID
        ,FromVendor = SUM(psrd.QuantityReceived)
    FROM Project.ProjectSpecReceivedDetails psrd
    WHERE
        psrd.Deleted = 0
        AND psrd.FromProjectSpecLocationID = 1
    GROUP BY psrd.ProjectSpecID
) psrFromVendor ON psrFromVendor.ProjectSpecID = ps.ID

标签： sqlazure-sql-database