时间戳

首页 > 解决方案 > 加入 2 个具有相同字段且不重复的表

mysql - 加入 2 个具有相同字段且不重复的表

问题描述

我有 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

标签： mysqljoin

解决方案

我认为你应该在联合 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

推荐阅读