MySql query to get all combinations of two columns

问题:

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

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

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 :(

回答1:

Your first outer join, as expected, produces:

| 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) |

See it on sqlfiddle.