我在加入两个表的内容时遇到了一些麻烦.这是目前的情况：
买
文章买了年
1 12400 2011
1 28000 2012
1 46351 2015年@H_403_7@
出售
文章销售年份
1 6400 2011
1 12000 2013
1 60900 2014@H_403_7@
期望的结果
文章买了卖年
1 12400 6400 2011
1 28000 NULL 2012
1 NULL 12000 2013
1 NULL 60900 2014
1 46351 NULL 2015@H_403_7@
SELECT b.article,b.bought,s.sold,b.year FROM Bought AS b LEFT JOIN Sold as s ON s.article = b.article AND s.year = b.year WHERE b.article = '1' ORDER BY b.year
这仅返回2011年的结果(两个值都存在).@H_403_7@
使用包含所有文章的第三个表的另一个尝试返回了相同的错误结果,并且它有两年的列,这是不理想的：@H_403_7@
SELECT art.article,b.year,s.year
FROM articles AS art
LEFT OUTER JOIN bought AS b ON art.article = b.article
LEFT OUTER JOIN Sold AS s ON art.article = s.article
AND (b.year = s.year OR b.year IS NULL OR s.year IS NULL)
WHERE art.article = '1'
解决方法
听起来你想要一个完整的外连接：
SELECT coalesce(b.article,s.article) as article,coalesce(b.year,s.year) as year
FROM Bought b FULL OUTER JOIN
Sold s
ON s.article = b.article AND s.year = b.year
WHERE (b.article = '1' OR s.article = '1')
ORDER BY year
