同一个表中的 Sum 和 Substract 列 [英] Sum and Substract column in same table

问题描述

我有下表TableOfDelivery"，有 4 列

I've the following table "TableOfDelivery" with 4 columns

我已经制定了一个 SQL 查询(访问 2016)来获取 2 个不同报告周的 2 个交货周之间的差异:

I've worked out a SQL query (access 2016) to get the difference between 2 delivery week on 2 different reporting weeks:

SELECT TOP 2 
 t1.ref, t1.[delivery week], 
 (t2.qty-t1.qty) AS 
QtyDifferenceBetween2DeliveryWeekOn2DifferentWeekReporting
FROM 
  TableOfDelivery AS t1 INNER JOIN TableOfDelivery AS t2 ON (t1.ref = t2.ref) 
AND 
  (t1.[delivery week] = t2.[delivery week]  AND (t1.[reporting week] <> t2. 
[reporting week]) )
GROUP BY t1.[reporting week], t1.ref, t1.[delivery week], t2.qty-t1.qty
ORDER BY t1.[reporting week];

这里是这个 SQL 查询的结果:

here is the outcomes from this SQL query:

结果仅适用于匹配的交货周.但我也希望交货周数不匹配，请参见图片中以红色和绿色突出显示的线条.

The outcomes is ok only for delivery weeks that match. But I would like also to have the delivery weeks that do not match, see picture the lines highlighted in red and green.

例如，对于红线和绿线，我也应该在结果查询中:

For instance, for the red and green lines, I should have in the outcome query too:

有没有办法做到这一点?

Is there any away to do that?

非常感谢

推荐答案

您可以尝试使用 CASE 来获得您想要的.

You can try to use a CASE to get what you want.

SELECT t1.ref,
       t1.[delivery week],
       (t2.qty-t1.qty) AS QtyDiff,
       t1.[delivery week],
       CASE
           WHEN t1.[reporting week] <> t1.[delivery week] THEN t1.qty *-1
           ELSE t1.qty
       END AS new_qty
FROM TableOfDelivery AS t1
INNER JOIN TableOfDelivery AS t2 ON (t1.ref = t2.ref)
AND (t1.[delivery week] = t2.[delivery week]
     AND (t1.[reporting week] <> t2. [reporting week]))
GROUP BY t1.[reporting week],
         t1.ref,
         t1.[delivery week], (t2.qty-t1.qty),t1.qty
ORDER BY t1.[reporting week];

CASE检查交货周和报告周之间是否存在差异.如果是这样，它会给你数量 * -1.

The CASEchecks if there is a difference between the delivery week and the reporting week. If it is the case, it gives you the qty * -1.

我已经在 postgres 上测试过它，但没有在 msaccess 上测试过，因为我的电脑上没有这个数据库.

I've tested it on postgres but not on msaccess as I do not have this db on my pc.

这篇关于同一个表中的 Sum 和 Substract 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！