连接2个字段相同但不重复的表

Question

我有2个表与相同的行-我需要将这些表附加到两行(产品和vint)，出价的id不应该重复的出价和订单，如果它已经是然后需要NULL。

SELECT b.product, b.vint, o.id as offer_id, b.id as bid_id from bids b LEFT 
JOIN offers o ON b.product = o.product AND o.vint=b.vint group by 
b.product, b.vintage, b.id
 UNION
SELECT o.product, o.vint, o.id as offer_id, b.id as bid_id from bids b RIGHT 
JOIN offers o ON b.product = o.product AND o.vint=b.vint group 
by o.product, o.vint, o.id

示例:我们需要：

 product    |   vint  |  bid_id | offer_id
 Pro_1      |    s12  |   1     |   3
 Pro_2      |    s13  |   2     |   NULL
 Pro_1      |    s13  |   4     |   1
 Pro_2      |    s11  |   NULL  |   2
 Pro_3      |    s10  |   5     |   4

但我的结果是：

product     |   vint  |  bid_id | offer_id
Pro_1       |    s12  |   1     |   3
Pro_2       |    s13  |   2     |   1
Pro_1       |    s13  |   4     |   1
Pro_2       |    s11  |   NULL  |   2
Pro_3       |    s10  |   5     |   4
Pro_1       |    s12  |   1     |   NULL

Answer 1

我认为在像这样的联合2表之后，你应该再按bid_id分组一次。

select * from (
   SELECT b.product, b.vint, MAX(o.id) as offer_id, MIN(b.id)  as bid_id from 
   bids b LEFT JOIN offers o ON b.product = o.product AND o.vint=b.vint group 
   by b.product, b.vintage, b.id
     union 
   SELECT o.product, o.vint, MIN(o.id) as offer_id, MAX(b.id)  as 
   bid_id from bids b RIGHT JOIN offers o ON b.product = o.product AND 
   o.vint=b.vint group by o.product, o.vint, o.id
) as demo_table group by bid_id