使用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原因停机的时间。那么我所期望的是...

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

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

- Tharaka Deshan

Answer 1

你的第一个外连接，如预期一样，产生了以下结果：

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

然而，由于外连接只要满足至少一次连接条件就会产生结果（并且仅在条件从未满足时引入NULL记录），你的第二个外连接因此没有为(B, July)生成记录；它还完全删除了Reason = 'D'，因为连接条件未满足（所有三个月份在其他地方都已满足）：

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

虽然你可以通过将OR a.Month IS NULL添加到连接条件中解决丢失的Reason = 'D'，但你仍然无法生成(B, July)。相反，因为你想要获得每对(Reason, Month)，你必须使用你的材料化Reasons表和你的材料化Months表进行CROSS JOIN：

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

| 原因 | 月份 |
------------------
|      A |   五月 |
|      B |   五月 |
|      D |   五月 |
|      A |   六月 |
|      B |   六月 |
|      D |   六月 |
|      A |   七月 |
|      B |   七月 |
|      D |   七月 |

在sqlfiddle上查看。

然后你只需要将结果与你的基础数据进行外连接:

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

| 原因 | 月份 | 宕机时间 |
-----------------------------
|    A | 七月 |        3 |
|    A | 六月 |        8 |
|    A | 五月 |        7 |
|    B | 七月 |   (null) |
|    B | 六月 |        6 |
|    B | 五月 |        5 |
|    D | 七月 |   (null) |
|    D | 六月 |   (null) |
|    D | 五月 |   (null) |

在sqlfiddle上查看。

- eggyal

---

谢谢 @eggyal！你是天才！也是一位优秀的老师。这个完美地解决了问题。再次感谢你详细的解释。 - Tharaka Deshan