MySql查询，如果没有匹配的记录，则获取两列为NULL的所有组合 [英] MySql query to get all combinations of two columns with NULL if there is no matching record

问题描述

我有一个表，其中包含服务器停机时间的记录.
我在sqlfiddle中创建了此表的简化版本.请在此处 sqlfiddle
表中的每个记录都类似于

I have a Table which has the records of the down time of a server.
I have created a simplified version of this table at sqlfiddle. Please see here sqlfiddle
The table has each record like

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

如果没有匹配的记录，我需要编写一个查询以获取Give Month和Reason与NULL的所有组合
例如:如果由于原因A，B或D而需要在5月，6月和7月关闭系统时间.我期望的是..

I need to write a query to get all combinations of give Month and Reason with NULL if there is no matching record
As an example : If I need to get the down time of the system in May, June and July due to Reason A,B or D.. What I am expecting is..

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
这是我的查询:

Since we don't have D in the records for given months, it should be NULL
This is my Query:

 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

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

Why i am not getting the expected output :(

推荐答案

您的第一个外部联接预期会产生:

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

但是，因为如果至少满足一次条件 ，外部联接就会产生结果(并且如果从来没有满足，则仅引入NULL记录)，您的第二个外部联接，然后不产生一个记录(B, July);它也完全删除了Reason = 'D'，因为不满足加入条件(并且其他三个月都已满足):

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 |

您可以解决Reason = 'D'的丢失通过在加入条件中添加 OR a.Month IS NULL，您仍然不会产生(B, July).相反，由于要获取每对(Reason, Month)，因此必须CROSS JOIN物化的Reasons表与物化的Months表:

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 |

在 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 上查看.

这篇关于MySql查询，如果没有匹配的记录，则获取两列为NULL的所有组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！