mysql - 从两个表中减去列

Question

我需要从两个表中获取总可用数量

Table T1
+-------------+------------+  
| Code        | Qty        |  
+-------------+------------+  
| A           | 500        |  
+-------------+------------+    


Table T2
+-------------+------------+  
| Code        | Qty        |  
+-------------+------------+  
| A           | 10         |  
+-------------+------------+    
| A           | 20         |  
+-------------+------------+    

使用此代码我得到结果 970 而不是 470:

SELECT
  `T1`.`Code`,
  (IFNULL(Sum(`T1`.`Qty`),0) - IFNULL(Sum(`T2`.`Qty`),0)) AS TotalQty
FROM
  `T1`
  LEFT JOIN `T2` ON `T1`.`Code` =
    `T2`.`Code`
GROUP BY
  `T1`.`Code`

我做错了什么？

Answer 1

检查此代码:)

SELECT Code,IFNULL(SUM(Qty),0)-IFNULL((SELECT SUM(Qty) FROM T2 WHERE T1.Code = Code),0) AS 答案 FROM T1 GROUP BY Code

我编辑我的答案，以防您想添加基于代码的条件并防止返回空值，请尝试这个兄弟