找到你要的答案

Q:SQL join two tables with null values in either table

Q:SQL连接两个表在表空值

I'm having some trouble joining the contents of two tables. Here's the current situation:

BoughtArticle   Bought Year     1             12400    2011     1             28000    2012     1             46351    2015     

SoldArticle   Sold      Year     1             6400      2011     1             12000    2013     1             60900    2014     

Desired ResultArticle   Bought Sold    Year     1             12400    6400    2011     1             28000    NULL   2012     1             NULL     12000  2013     1             NULL     60900  2014     1             46351    NULL   2015     

I've tried the following to achieve the desired result:

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    

This only returns the result for 2011 (Where both values are present).

Another try using a third table holding all articles returned the same bad result and it has two year columns which is not ideal:

SELECT art.article, b.bought, s.sold, 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'    

I've tried using different kinds of joins with the last SQL statement but none of them seem to work. How can I achieve the desired result?

我有麻烦加入两个表的内容。以下是目前的情况:

boughtarticle  买 年     1            12400    2011     1            28000    2012 1 46351 2015                          

soldarticle  出售      年     1            6400      2011     1            12000    2013 1 60900 2014                          

所需的resultarticle  买     年     1            12400    6400    2011     1            28000    空   2012    1             空    12000  2013     1             空    60900  2014     1             46351   空  2015     出售

我尝试了以下来达到预期的效果:

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的结果(其中两个值都存在)。

另一种尝试使用三分之一表持有所有物品返回相同的坏结果,它有两年的列,这是不理想的:

SELECT art.article, b.bought, s.sold, 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'    

I've tried using different kinds of joins with the last SQL statement but none of them seem to work. How can I achieve the desired result?

answer1: 回答1:

It sounds like you want a full outer join:

SELECT coalesce(b.article, s.article) as article, b.bought, s.sold,
       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 

这听起来像你想要一个完整的外部连接:

SELECT coalesce(b.article, s.article) as article, b.bought, s.sold,
       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 
answer2: 回答2:

You should use full outer join :

select coalesce(b.article, s.article)
     , b.bought
     , s.sold
     , coalesce (b.year, s.year) as year
from Bought b
full join Sold s on s.article = b.article
                 and s.year = b.year
where coalesce(b.article, s.article) = '1'
order by year  

你应该使用完整的外部连接:

select coalesce(b.article, s.article)
     , b.bought
     , s.sold
     , coalesce (b.year, s.year) as year
from Bought b
full join Sold s on s.article = b.article
                 and s.year = b.year
where coalesce(b.article, s.article) = '1'
order by year  
answer3: 回答3:

You can do an union. First select with left join and then union the sold results that don't have corresponding rows in bought.

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 article = 1
Union 
SELECT article,null, sold, year
FROM sold where article =1 and year not in (select year from bought)

Something like that. I haven't tested it but you can get the idea.

你可以做一个联盟。首先选择与左连接,然后结合销售结果,没有相应的行在购买。

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 article = 1
Union 
SELECT article,null, sold, year
FROM sold where article =1 and year not in (select year from bought)

类似的东西。我没有测试过,但你可以得到这个想法。

sql  postgresql