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