如何合并2个表，以便获得每个子类别的愿望日期和最后订单日期?

Question

我有一个愿望例如，如下表所示，该表包含有关客户愿望清单详细信息的数据

EmailAddress    wish_created_date   Category
[e-mail]     Aug-3-2021          Curtain
[e-mail]     Aug-2-2021          Table
[e-mail]     Aug-2-2021          Sofa
[e-mail]     Aug-2-2021          Sofa
[e-mail]     Aug-2-2021          Sofa
[e-mail]     Aug-3-2021          Sofa
[e-mail]     Aug-3-2021          Sofa
[e-mail]     Aug-3-2021          Sofa

然后我有另一个表叫做-last_category_orders． 该表包含每个类别中客户上次订购日期的数据。有4个类别窗帘,表格椅子上,沙发

EmailAddress    last_curtain_order  last_table_order    last_sofa_order last_chair_order
[e-mail]     Aug-5-2021           Aug-3-2021     
[e-mail]                                              Aug-5-2021 
[e-mail]     Aug-9-2021                                               Aug-9-2021
[e-mail]                          Aug-9-2021          Aug-9-2021 
[e-mail]                                                              Aug-10-2021

预期结果我的最终表应该如下所示，

EmailAddress    wish_created_date   Category            last_cat_order
[e-mail]     Aug-3-2021          Curtain             Aug-5-2021
[e-mail]     Aug-2-2021          Table               Aug-3-2021
[e-mail]     Aug-2-2021          Sofa                Aug-5-2021
[e-mail]     Aug-2-2021          Sofa                Aug-5-2021
[e-mail]     Aug-2-2021          Sofa                Aug-5-2021
[e-mail]     Aug-3-2021          Sofa                null
[e-mail]     Aug-3-2021          Sofa                null
[e-mail]     Aug-3-2021          Sofa                null

最后一个表应该是的左连接与last_category_orders表，last_cat_order日期

示例:[e-mail]为窗帘创建了一个愿望。[e-mail]的最后一次curtain_order是2021年8月5日。这必须在最终表中显示。

我不知道如何得到最终表中的结果。

Answer 1

Mysql没有unpivot操作符，所以你可以使用下面的union来实现它

select w.*, cat.date last_cat_order
from wish w
left join 
(
select 
max(last_curtain_order ) date, 'curtain' category, emailaddress
from  last_category_orders LC
group by emailaddress
union 
max(last_table_order) date,'table' category, emailaddress
from  last_category_orders LC
group by emailaddress
union 
max(last_sofa_order) date,'sofa' category, emailaddress
from  last_category_orders LC
group by emailaddress
union 
max(last_chair_order) date,'chair' category, emailaddress
from  last_category_orders LC
group by emailaddress
)Cat
on cat.emailaddress = w.emailaddress
and cat.category =w.Category

Answer 2

您可以通过产品类别的id加入。用例，则u联接必须正确示例

select
t1.emailAddress,
t1.wish_creater_date,
t1.category_id,(exm curtain = 1, table = 2 ...)
case
when t1.category_id = 1 then t2.last_curtain_order
when t1.category_id = 2 then t2.last_table_order
when t1.category_id = 3 then t2.last_sofa_order
when t1.category_id = 4 then t2.last_chair_order
end as last_cat_order
from table1 t1
left join table2 t2 on
t2.emailAddress = t1.emailAddress