我有一张表,上面有服务器停机时间的记录。
我在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

如果没有匹配的记录,我需要编写一个查询来获取给定月份和原因的所有组合
例如:如果由于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

因为我们在记录中没有记录,所以应该是空的
这是我的问题:
 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

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

最佳答案

Your first outer join,如预期,产生:

| 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

|原因月停工|
———————————————————————————---
|七月三日|
|六月八日|
|五月七日|
| b七月(无效)|
|六月六日|
|五月五日|
| D七月(空)|
| D六月(空)|
| D五月(空)|
sqlfiddle上查看。

09-10 12:52
查看更多