减去两个和

Question

请假设这是DB。

-- INVENTORY TABLE DECLARATION AND DATA INSERTION
DECLARE @COLORSIZEQTYS TABLE(ITEID int, COLORCODE varchar(15), QTYMODE smallint, SIZE4 float, SIZE5 float, SIZE6 float, SIZE7 float);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1, -2, -1,  3,  4);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Red',   1,  2,  5,  5,  2);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Green', 1,  2,  2,  4, -1);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  0,  2,  0, -1,  1);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'White', 0,  2,  3,  3,  0);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Blue',  1, -1,  6,  0,  3);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1,  7,  2, -1,  4);
INSERT @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(5594, 'Grey',  1,  1,  0, -1, -1);
-- PURCHASES TABLE DECLARATION AND DATA INSERTION
DECLARE @STORECOLORSIZEEST TABLE(ID int, ITEID int, COLORCODE varchar(15), SIZE4 float, SIZE5 float, SIZE6 float, SIZE7 float);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1703, 5594, 'Grey', 0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1704, 6811, 'Red',  0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1706, 5594, 'Grey', 0, 1, 1, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1707, 5594, 'Grey', 1, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1709, 8372, 'Blue', 0, 1, 0, 1);
INSERT @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7) VALUES(1712, 5594, 'Grey', 0, 0, 1, 1);
-- QUERY SUMMING INVENTORY ROWS
SELECT SUM(SIZE4) AS QTY4,
SUM(SIZE5) AS QTY5,
SUM(SIZE6) AS QTY6,
SUM(SIZE7) AS QTY7
FROM @COLORSIZEQTYS
WHERE ITEID = 5594
AND COLORCODE = 'Grey'
AND QTYMODE = 1
-- QUERY SUMMING INVENTORY ROWS AND ZERO-ING NEGATIVE VALUES
SELECT SUM(CASE WHEN SIZE4 < 0 THEN 0 ELSE SIZE4 END) AS QTY4,
SUM(CASE WHEN SIZE5 < 0 THEN 0 ELSE SIZE5 END) AS QTY5,
SUM(CASE WHEN SIZE6 < 0 THEN 0 ELSE SIZE6 END) AS QTY6,
SUM(CASE WHEN SIZE7 < 0 THEN 0 ELSE SIZE7 END) AS QTY7
FROM @COLORSIZEQTYS
WHERE ITEID = 5594
AND COLORCODE = 'Grey'
AND QTYMODE = 1
-- QUERY SUMMING PURCHASE ROWS
SELECT SUM(SIZE4) AS RSV4,
SUM(SIZE5) AS RSV5,
SUM(SIZE6) AS RSV6,
SUM(SIZE7) AS RSV7
FROM @STORECOLORSIZEEST
WHERE ITEID = 5594
AND COLORCODE = 'Grey'
-- SUBTRACTING THE TWO SUMS TO FIND THE FINAL INVENTORY QUANTITIES - CLEARLY THIS DOESN'T WORK
SELECT (SUM(CASE WHEN inv.SIZE4 < 0 THEN 0 ELSE inv.SIZE4 END) - SUM(pcs.SIZE4)) AS TOT4,
(SUM(CASE WHEN inv.SIZE5 < 0 THEN 0 ELSE inv.SIZE5 END) - SUM(pcs.SIZE5)) AS TOT5,
(SUM(CASE WHEN inv.SIZE6 < 0 THEN 0 ELSE inv.SIZE6 END) - SUM(pcs.SIZE6)) AS TOT6,
(SUM(CASE WHEN inv.SIZE7 < 0 THEN 0 ELSE inv.SIZE7 END) - SUM(pcs.SIZE7)) AS TOT7
FROM @COLORSIZEQTYS AS inv
INNER JOIN @STORECOLORSIZEEST AS pcs ON pcs.ITEID = inv.ITEID AND pcs.COLORCODE = inv.COLORCODE
WHERE inv.ITEID = 5594 AND inv.COLORCODE = 'Grey' AND inv.QTYMODE = 1

所以我有两个表，一个是产品的库存和每种尺寸的数量，另一个是产品的采购线，每种尺寸的采购数量。

我想计算每种尺寸的最终新库存数量，将超额销售的尺寸归零(这种超额销售有时会发生，因为系统操作员未能注册一些销售)。

无论如何，对于小提琴的例子，总数应该像

<表类>TOT4TOT5TOT6TOT7tbody><<tr>7114

你差一点。

查询中的错误是所有相关的个人购买行都连接到所有相关的个人库存行，其中由"relevant"我是说灰色的5594。要理解我的意思，最简单的方法是运行最后的查询，但是使用select *而不是取总和。

解决方案是确保先进行求和，然后将购买结果与库存结果结合起来，然后进行减法(而不是在求和之前加入)。

当我们这样做时，我们可以交叉连接，因为在我们过滤到我们想要的颜色和项目并取总数之后，只有一行用于购买，一行用于库存。

为了简单起见，我删除了与查询无关的示例数据。

另外，我还包含了一个完整的设置和解决方案，因为我认为您也可以从中学到一些东西。例如，请注意，您不必为每个值行执行单独的插入语句。您可以在插入的每一行后面加一个逗号。这使得设置更容易阅读。

-- INVENTORY TABLE DECLARATION AND DATA INSERTION
declare @COLORSIZEQTYS TABLE
(
ITEID     int,
COLORCODE varchar(15),
QTYMODE   smallint,
SIZE4     float,
SIZE5     float,
SIZE6     float,
SIZE7     float
);
-- PURCHASES TABLE DECLARATION AND DATA INSERTION
declare @STORECOLORSIZEEST TABLE
(
ID        int,
ITEID     int,
COLORCODE varchar(15),
SIZE4     float,
SIZE5     float,
SIZE6     float,
SIZE7     float
);
insert @COLORSIZEQTYS(ITEID, COLORCODE, QTYMODE, SIZE4, SIZE5, SIZE6, SIZE7)
values
(5594, 'Grey', 1,     -2, -1, 3, 4),
(5594, 'Grey', 0,     2, 0, -1, 1),
(5594, 'Grey', 1,     7, 2, -1, 4),
(5594, 'Grey', 1,     1, 0, -1, -1);
insert @STORECOLORSIZEEST(ID, ITEID, COLORCODE, SIZE4, SIZE5, SIZE6, SIZE7)
values
(1703, 5594, 'Grey',  0, 1, 0, 1),
(1706, 5594, 'Grey',  0, 1, 1, 1),
(1707, 5594, 'Grey',  1, 1, 0, 1),
(1712, 5594, 'Grey',  0, 0, 1, 1);
with zeroedinv as
(
select   SUM(case when SIZE4 < 0 then 0 else SIZE4 end) as QTY4,
SUM(case when SIZE5 < 0 then 0 else SIZE5 end) as QTY5,
SUM(case when SIZE6 < 0 then 0 else SIZE6 end) as QTY6,
SUM(case when SIZE7 < 0 then 0 else SIZE7 end) as QTY7
from     @COLORSIZEQTYS
where    ITEID = 5594
and COLORCODE = 'Grey'
and QTYMODE = 1
),
purchases as
(
select   SUM(SIZE4) as RSV4,
SUM(SIZE5) as RSV5,
SUM(SIZE6) as RSV6,
SUM(SIZE7) as RSV7
from     @STORECOLORSIZEEST
where    ITEID = 5594
and COLORCODE = 'Grey'
)
select      totl4 = iif(qty4 - rsv4 < 0, 0, qty4 - rsv4),
totl5 = iif(qty5 - rsv5 < 0, 0, qty5 - rsv5),
totl6 = iif(qty6 - rsv6 < 0, 0, qty6 - rsv6),
totl7 = iif(qty7 - rsv7 < 0, 0, qty7 - rsv7)
from        zeroedinv   
cross join  purchases