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

我有一个表，其中记录了服务器的停机时间。
我在 sqlfiddle 创建了该表的简化版本。请看这里sqlfiddle http://www.sqlfiddle.com/#!2/8479e/8
该表的每条记录如下

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 导致的系统在五月、六月和七月的停机时间。我期望的是..

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

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

你的第一个外部连接 http://www.sqlfiddle.com/#!2/8479e/19/0正如预期的那样，产生：

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

但是，因为如果满足连接条件，外连接就会产生结果至少一次（并且只介绍NULL记录如果条件是never使满意），你的第二个外部连接 http://www.sqlfiddle.com/#!2/8479e/20/0 then does not产生一个记录(B, July);它也会下降Reason = 'D'完全是这样，因为未满足连接条件（并且所有三个月都已在其他地方满足）：

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

当你could解决损失Reason = 'D' 通过增加 http://www.sqlfiddle.com/#!2/8479e/21/0 OR a.Month IS NULL根据您的加入条件，您仍然不会产生(B, July)。相反，因为你想获得每一对(Reason, Month)， 你必须CROSS JOIN你的物化Reasons与你的物化表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 |
  

看到它sqlfiddle http://www.sqlfiddle.com/#!9/8479e/1/0.

然后，您只需将结果与基础数据进行外部联接：

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 http://www.sqlfiddle.com/#!9/8479e/2/0.