mysql 没有匹配的记录_如果没有匹配的记录,MySql查询将获得两列的所有组合

正如预期的那样,

Your first outer join产生：

| REASON | MONTH |

-------------------

| A | May |

| A | May |

| A | July |

| A | June |

| B | May |

| B | June |

| D | (null) |

但是,因为如果连接条件至少满足一次(如果条件永远不满足则只引入NULL记录),则外连接会产生结果,因此your second outer join不会产生(B,7月)的记录;它也完全丢弃了Reason =’D’,因为连接条件没有得到满足(并且所有三个月都在其他地方得到满足)：

| REASON | MONTH |

------------------

| A | May |

| A | May |

| B | May |

| A | June |

| B | June |

| A | July |

虽然您可以解决Reason =’D’by adding或a.Month IS NULL对您的连接条件的损失,但您仍然不会产生(B,7月).相反,因为您想获得每对(Reason,Month),您必须使用物化月份表CROSS JOIN您的物化原因表：

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 |

然后,您只需要将结果与您的基础数据进行外连接：

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