php - 当其中一个为空时，我无法减去两个不同的表

Question

我有 2 个表 rsales 和 rreturn 并且我有以下代码。当两个表都有值时它可以正常运行，但问题是当其中一个表为空或没有存储数据时，它不会显示结果。我的代码有问题吗？

$result = mysql_query("SELECT category, (SELECT SUM(s.total)-SUM(r.total) FROM rsales AS s WHERE r.pcode=s.pcode) as total, r.pcode FROM rreturn AS r GROUP BY r.pcode;");

Answer 1

MySQL 不支持 FULL OUTER JOIN，因此您需要模拟它。

一种方法:

SELECT IFNULL(sr.category,ss.category) AS category
     , IFNULL(s.total,0)-IFNULL(r.total,0) AS total
  FROM (
         SELECT cr.pcode
           FROM rreturn cr
          GROUP BY cr.pcode
          UNION 
         SELECT cs.pcode
           FROM rsales cs
          GROUP BY cs.pcode
       ) c
  LEFT
  JOIN ( SELECT sr.pcode
              , MAX(sr.category) AS category
              , SUM(sr.total) AS total
           FROM rreturn sr
          GROUP BY sr.pcode
       ) r
    ON r.pcode = c.pcode
  LEFT
  JOIN ( SELECT ss.pcode
              , MAX(ss.category) AS category
              , SUM(sr.total) AS total
           FROM rsales ss
          GROUP BY ss.pcode
       ) s
    ON s.pcode = c.pcode

内联 View c 获取 rsales 或 rreturn 中出现的所有 pcode 的完整列表。

然后，内联 View r 从 rreturn 获取每个 pcode 的总计，而 s 从 rsales 执行相同操作。

然后我们可以使用LEFT JOIN操作来根据pcode匹配行。

(这不是返回指定结果集的唯一方法，只是一个示例。)