我有一个表，其中包含服务器停机时间的记录。 我在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如果没有匹配的记录，我需要编写一个查询来获取给予Month和Reason的所有组合为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为什么我没有得到预期的输出:(
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 2I 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 NULLSince 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.reasonWhy 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' ，因为连接条件没有得到满足（并且所有三个月都在其他地方得到满足）：
| 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 | 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上看到它。
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.
更多推荐
发布评论