mysql - SQL:使用同一张表核对数量

Question

我有这张表:

努力做穷人的供应链。我们得到操作的消息(R 代表接收，S 代表发送)。每个地点都使用其 ID 代码 (SRC)、发送或接收的数量以及目的地进行报告。如果有人发送，destination 就是 cargo 要去的地方，如果有人接收，destination 字段就是数据的来源。

唯一没有目的地的位置是 HO(总部)，因为它是根节点。

现在，我想获得一份差异报告。最后 2 笔交易(ID 21 和 22)分别丢失了 10 和 5 个网。

我想连接数据，使 dest 列与 src 列匹配，并查看 QTYS 和 QTYR 之间的区别。

示例报告:

但是我写的SQL也提供了一些误报？!？

我的 SQL 代码:

SELECT datas.SRC, datas.QTYR - datar.qtyS AS Difference 
FROM data as datas JOIN data as datar on datas.dest = datar.src 
WHERE datas.QTYR - datar.qtyS < 0 GROUP BY datas.src

数据库:

CREATE TABLE data (ID Int NOT NULL AUTO_INCREMENT, SRC NVARCHAR(20) NOT NULL, QTYR INT NOT NULL DEFAULT 0, QTYS INT NOT NULL  DEFAULT 0, DEST NVARCHAR(20), OP NVARCHAR(20) NOT NULL, PRIMARY KEY(ID));
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HO01",1500000,"","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HO02",1500000,"","R");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HO01",750000,"DO01","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HO01",750000,"DO02","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HO02",750000,"DO03","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HO02",750000,"DO04","S");

INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DO01",750000,"HO01","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DO02",750000,"HO01","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DO03",750000,"HO02","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DO04",750000,"HO02","R");

INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("DO01",375000,"HC01","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("DO02",375000,"HC02","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("DO03",375000,"HC03","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("DO04",375000,"HC04","S");

INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HC01",375000,"DO01","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HC02",375000,"DO02","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HC03",375000,"DO03","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("HC04",375000,"DO04","R");

INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HC01",100000,"DP01","S");
INSERT INTO DATA (SRC, QTYS, DEST, OP) VALUES ("HC01",100000,"DP02","S");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DP01",99990,"HC01","R");
INSERT INTO DATA (SRC, QTYR, DEST, OP) VALUES ("DP02",99995,"HC01","R");

Answer 1

我认为这个查询可以解决您的问题:

select *,
  qtyr-qtys as Diff
from (
  select SRC, sum(QTYR) as qtyr
  from data s
  group by SRC
  ) s
  join (
    select DEST, sum(QTYS) as qtys
    from data s
    group by DEST
    ) d
    ON s.SRC=d.DEST
where qtyr!=qtys

它只是将发送的金额和收到的金额相加，分别按发送方/接收方分组，然后检查数字是否匹配。请注意，您要按两个不同的概念进行分组，首先按发送者，然后按接收者。这部分可能是您查询的问题。