联接两个表而不会丢失相关值 [英] join two tables without losing relevant values

问题描述

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

I have two tables representing a database for customer products and its competitors' products:

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

tmp_match - from_product_id and to_product_id representing matches between customer product and competitor product respectively.

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

tmp_price_history - shows the price of each product per date.

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

I am trying to write a query which will list all dates from table tmp_price_history. For each date I want to see customer product price vs competitor product price according to product matches pairs in table tmp_match, regardless of whether there was a price history record for customer product or competitor product or both:

如果两个价格都可以在特定日期使用-请在其列中同时列出两个价格

if both prices are available for a specific date - list them both in their columns

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

if there is only a record for customer product - show only customer price (and leave the competitor column blank).

如果仅存在竞争对手产品的记录，请在其栏中显示竞争对手的价格.

if there is only record for competitor product - show competitor price in its column.

预期结果:

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                 

我尝试使用以下查询来实现这一目标:

I tried to achieve that using this query:

 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代码段.

but it doesn't achieve my goal as can be seen in this sql snippet.

推荐答案

我认为您正在寻找这个东西:

I think that you are looking for this:

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片段的想法很棒！

Your idea about sql snippet was great!

这篇关于联接两个表而不会丢失相关值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！