mysql - 加入两个表而不丢失相关值

Question

我有两个表代表客户产品及其竞争对手产品的数据库:

tmp_match - from_product_id 和 to_product_id 分别表示客户产品和竞争对手产品之间的匹配。

tmp_price_history - 显示每个日期每个产品的价格。

我正在尝试编写一个查询，该查询将列出表 tmp_price_history 中的所有日期。对于每个日期，我都想根据表 tmp_match 中的产品匹配对查看客户产品价格与竞争对手产品价格，无论是否有客户产品或竞争对手产品或两者的价格历史记录 :

如果两个价格在特定日期都可用 - 将它们都列在各自的列中

如果只有客户产品的记录 - 仅显示客户价格(并将竞争对手列留空)。

如果只有竞争对手产品的记录 - 在其列中显示竞争对手的价格。

预期结果:

date    from_product_id to_product_id   cust_price  comp_price
1       1               11              99          95
2       1               11              98          94
1       1               12                          92
2       1               12                          91
2       2                               108                 

我尝试使用这个查询来实现:

 select cust_hist.date, from_product_id, to_product_id, cust_hist.price as cust_price,comp_hist.price as comp_price
from tmp_match as matches
       left join tmp_price_history cust_hist
         on cust_hist.product_id = matches.from_product_id
       left join tmp_price_history comp_hist
         on  comp_hist.product_id = matches.to_product_id
;

但它并没有实现我的目标，如 sql snippet 所示.

Answer 1

我认为您正在寻找这个:

select distinct *
from (SELECT date,
             if(group_concat(distinct cust_price), from_product_id, null)as from_product_id,
             if(group_concat(distinct comp_price), to_product_id, null)  as to_product_id,
             group_concat(distinct cust_price)                           as cust_price,
             group_concat(distinct comp_price)                           as comp_price
      FROM (select cust_hist.date,matches.from_product_id,
                   matches.to_product_id,cust_hist.price cust_price,
                   comp_hist.price                       comp_price
            from tmp_match matches
                   inner join tmp_price_history cust_hist on matches.from_product_id = cust_hist.product_id
                   inner join tmp_price_history comp_hist on matches.to_product_id = comp_hist.product_id
            WHERE comp_hist.date = cust_hist.date
            union
            select comp_hist.date,matches.from_product_id,
                   matches.to_product_id,null as cust_price,
                   comp_hist.price               comp_price
            from tmp_price_history comp_hist
                   join tmp_match matches
                     on matches.to_product_id = comp_hist.product_id # and matches.from_product_id is null

            union
            select cust_hist.date,matches.from_product_id,
                   matches.to_product_id,
                   cust_hist.price cust_price,
                   null            comp_price
            from tmp_price_history cust_hist
                   join tmp_match matches
                     on matches.from_product_id = cust_hist.product_id # and matches.to_product_id is null

            order by DATE, from_product_id, to_product_id, cust_price, comp_price) as u
      group by date,from_product_id,to_product_id) g

你关于 sql 片段的想法很棒!