MySql查询如果没有匹配的记录，则获取两列的所有组合与NULL

Question

我有一个表，里面有服务器宕机时间的记录。
我在 sqlfiddle 创建了这个表的简化版本。请看这里sqlfiddle
表格中的每条记录都是

Reason  Month   Down_Time
A       May     2
A       May     5
B       May     5
C       July    15
A       July    3
B       June    6
A       June    8
C       June    2

如果没有匹配的记录，我需要编写一个查询来获取给定月份和原因与 NULL 的所有组合
例如:如果由于原因 A、B 或 D，我需要在 5 月、6 月和 7 月获得系统的停机时间。我期望的是..

Reason  Month   DOWNTIME
A       May     7
A       June    8
A       July    3
B       May     5
B       June    6
B       July    NULL
D       May     NULL
D       June    NULL
D       July    NULL

由于给定月份的记录中没有 D，因此它应该为 NULL
这是我的查询:

 SELECT Reasons.reason, Months.Month,sum( a.Down_time ) AS downtime
 FROM tabledown a
 RIGHT JOIN (
 SELECT 'A' AS reason
 UNION ALL SELECT 'B' AS reason
 UNION ALL SELECT 'D' AS reason
 ) Reasons
  ON a.reason = Reasons.reason
 RIGHT JOIN (
 SELECT 'May' AS month
  UNION ALL SELECT 'June' AS month
  UNION ALL SELECT 'July' AS month
 ) Months
 ON a.Month = Months.month
 GROUP BY Reasons.reason,Months.month
 ORDER BY Reasons.reason

为什么我没有得到预期的输出:(

Answer 1

Your first outer join ，正如预期的那样，产生:

| REASON |  MONTH |-------------------|      A |    May ||      A |    May ||      A |   July ||      A |   June ||      B |    May ||      B |   June ||      D | (null) |

However, because outer joins produce results if the join condition is satisfied at least once (and only introduce NULL records if the condition is never satisfied), your second outer join then does not produce a record for (B, July); it also drops Reason = 'D' entirely, because the join condition is not met (and all three months have been satisfied elsewhere):

| REASON | MONTH |------------------|      A |   May ||      A |   May ||      B |   May ||      A |  June ||      B |  June ||      A |  July |

Whilst you could resolve the loss of Reason = 'D' by adding OR a.Month IS NULL to your join condition, you still will not produce (B, July). Instead, because you want to obtain every pair of (Reason, Month), you must CROSS JOIN your materialised Reasons table with your materialised Months table:

SELECT Reason, Month
FROM   
  (
    SELECT 'A' AS Reason
    UNION ALL SELECT 'B'
    UNION ALL SELECT 'D'
  ) Reasons CROSS JOIN (
    SELECT 'May' AS Month
    UNION ALL SELECT 'June'
    UNION ALL SELECT 'July'
  ) Months

| REASON | MONTH |------------------|      A |   May ||      B |   May ||      D |   May ||      A |  June ||      B |  June ||      D |  June ||      A |  July ||      B |  July ||      D |  July |

See it on sqlfiddle.

You then merely need outer join the result with your underlying data:

SELECT Reason, Month, SUM(Down_time) downtime
FROM   
  (
    SELECT 'A' AS Reason
    UNION ALL SELECT 'B'
    UNION ALL SELECT 'D'
  ) Reasons CROSS JOIN (
    SELECT 'May' AS Month
    UNION ALL SELECT 'June'
    UNION ALL SELECT 'July'
  ) Months
  LEFT JOIN tabledown USING (Reason, Month)
GROUP BY Reason, Month

| REASON | MONTH | DOWNTIME |-----------------------------|      A |  July |        3 ||      A |  June |        8 ||      A |   May |        7 ||      B |  July |   (null) ||      B |  June |        6 ||      B |   May |        5 ||      D |  July |   (null) ||      D |  June |   (null) ||      D |   May |   (null) |

查看 sqlfiddle .