mysql - NULL与JOIN-MySQL

我有一个特定的问题。我想获取账单数据。该数据将用于图表，因此需要总和。这里是解释：

Sub1获取本月已支付的帐单金额的总和。

Sub2获取本月未支付的账单金额的总和。

Sub3将获得本月已付款的账单金额的总和，但实际上未付款。如果其他公司在5年内未支付账单，或者超过该账单“已支付”的账单（此后将进行法院诉讼程序），以防万一。

问题是，例如，如果我没有sub1的记录，则sub2或sub3无法加入，并且无论如何我都想显示它们。如果sub2没有记录，而sub3有记录，则未显示sub3，这也是问题所在。即使有sub1的NULL值，而sub 2和sub3的NOT NULL，也如何显示一行中的所有记录，反之亦然。任何帮助将不胜感激，因为我几乎整个下午都在研究此查询解决方案。 ：/

SELECT sub1.y,
       sub1.m,
       sub1.val1,
       sub2.val2,
       sub2.y,
       sub2.m,
       sub3.y,
       sub3.m,
       sub3.val3
FROM
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(paidd)
     AND MONTH(curdate())=MONTH(paidd)
   GROUP BY y,
            m) sub1
INNER JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub2
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub3
ON sub1.y=sub2.y
AND sub1.m=sub2.m
AND sub2.y=sub3.y
AND sub2.m=sub3.m

最佳答案

有一个最初的选择只是获得年/月，然后与其他人对抗吗？

SELECT sub0.y,
       sub0.m,
       sub1.val1,
       sub2.val2,
       sub2.y,
       sub2.m,
       sub3.y,
       sub3.m,
       sub3.val3
FROM  (SELECT YEAR(curdate()) AS y, MONTH(curdate()) AS m) Sub0
LEFT JOIN(SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(paidd)
     AND MONTH(curdate())=MONTH(paidd)
   GROUP BY y,
            m) sub1
ON sub0.y=sub1.y
AND sub0.m=sub1.m
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
LEFT JOIN
  (SELECT YEAR(curdate()) AS y,
          MONTH(curdate()) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
     AND YEAR(curdate())=YEAR(pdate2)
     AND MONTH(curdate())=MONTH(pdate2)
   GROUP BY y,
            m) sub3
ON sub0.y=sub3.y
AND sub0.m=sub3.m

或简化触摸：-

SELECT sub0.y,
       sub0.m,
       sub1.val1,
       sub2.val2,
       sub3.val3
FROM  (SELECT YEAR(curdate()) AS y, MONTH(curdate()) AS m) Sub0
LEFT JOIN(SELECT YEAR(paidd) AS y,
          MONTH(paidd) AS m,
          SUM(totwdisc) AS val1
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=0
   GROUP BY y,
            m) sub1
ON sub0.y=sub1.y
AND sub0.m=sub1.m
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,
          SUM(netto) AS val2
   FROM pro_partial_inv
   WHERE paidd IS NULL
     AND bringbar=0
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,
          SUM(totwdisc) AS val3
   FROM pro_partial_inv
   WHERE paidd IS NOT NULL
     AND bringbar=1
   GROUP BY y,
            m) sub3
ON sub0.y=sub3.y
AND sub0.m=sub3.m

甚至更简化

SELECT YEAR(curdate()) AS y,
        MONTH(curdate()) AS m,
        SUM(IF(paidd IS NOT NULL AND bringbar=0, totwdisc, 0)),
        sub2.val2,
        sub2.val3
FROM  pro_partial_inv
LEFT JOIN
  (SELECT YEAR(pdate2) AS y,
          MONTH(pdate2) AS m,
          SUM(IF(paidd IS NULL AND bringbar=0, netto, 0)) AS val2,
          SUM(IF(paidd IS NOT NULL AND bringbar=0, totwdisc, 0)) AS val3
   FROM pro_partial_inv
   GROUP BY y,
            m) sub2
ON sub0.y=sub2.y
AND sub0.m=sub2.m
GROUP BY y, m