如何结合2个数据集，同时拉动后期生效日期，以避免重复？ [英] How do I union 2 datasets while also pulling the later effective date, to avoid duplicates?

问题描述

我将从我想要完成的事情开始：



- 我有一张当前的历史表。

- 我想检查两个表中的同一个客户是否有过reason_iden_fk = 1.

- 如果他们在两个表中都有记录我想要采取2的后期。

- 如果忽略所有其他日期，我如何强制返回TL的最近生效日期？

I'll start with what I'm trying to accomplish:

- I have a current and historic table.
- I want to check if the same customer in both tables, has ever had a reason_iden_fk = 1.
- If they have a record in both tables I'd like to take the later of the 2.
- How can I force the most recent effective date from TL to be returned while all other dates are ignored?

select 
 custid
,tl.effectivedate

from tblcurrent c 
    join translog tl 
        on c.custid = tl.custid
           and tl.reason_iden_fk = 1 

union 

select 

from tblhistory h 
    join translog tl 
        on h.custid = tl.custid
           and tl.reason_iden_fk

我尝试了什么：



Row_number排名功能但性能受到巨大影响。

What I have tried:

Row_number rank functions but the performance hit is massive.

推荐答案

如果您对最新日期感兴趣（无论是当前表格还是表格表格），UNION都无法帮助您...

UNION将删除重复项，但是最新的约会你必须在表之间进行联接...

这样的事情：

If you are interested in the latest date (either form current or archieved table) UNION can not help you...
UNION will remove duplicates, but for latest date you will have to do a JOIN between the tables...
Something like this:

select *
from table1
  left join table2 on table1.customer = table2.customer and table1.date > table2.date -- will bring only rows where there is not newer row in table2
where table2.customer is null

union

select *
from table2

这篇关于如何结合2个数据集，同时拉动后期生效日期，以避免重复？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！