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

正如预期的那样，您的第一个外部联接产生：

|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)，你必须用你的材料Months表CROSS JOIN你的材料化Reasons表：

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上查看。

然后，您只需要将结果与底层数据进行外部连接：

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|7月|3日||A|June|8||A|May|7||B|July|(空)||B | 6月6日||B|May|5||D|July|(空)||D|June|(空)||D|May|(null)|

请在sqlfiddle上查看。