SQL Server：从左连接返回一个匹配（使用month（）函数）

Question

我试图根据多个条件将两个表连接在一起：保单号，交易类型，年份，日期月份

大部分策略对于此代码都能正常工作，但有一些将有多行数据满足连接条件，因此会为我的select语句返回重复行。

没有最好的方法来选择哪一个匹配返回（最早的日期，第一次匹配等），所以真的我只是在意代码只返回一行数据（没有重复）。

任何建议/帮助非常感谢！

select 
a.POLICY_NUMBER, 
a.POLICY_TRANSACTION_TYPE, 
a.POLICY_START_DATE, 
b.COMMISSION_RATE, 
from Table_1 as a 
left join Table_2 as b 
on 
    a.POLICY_NUMBER = b.POLICY_NUMBER 
    and a.POLICY_TRANSACTION_TYPE = b.TRANSACTION_TYPE 
    and year(a.POLICY_START_DATE) = year(b.EFFECTIVE_DATE) 
    and month(a.POLICY_START_DATE) = month(b.EFFECTIVE_DATE) 

编辑

样品TABLE_1

POLICY_NUMBER POLICY_TRANSACTION_TYPE POLICY_YEAR_START_DATE 
1    REN      1/1/2016 
1    MTA      15/1/2016 

样品TABLE_2

POLICY_NUMBER TRANSACTION_TYPE EFFECTIVE_DATE COMMISSION_RATE 
1    REN    1/1/2016  0.3 
1    MTA    14/1/2016  0.1 
1    MTA    16/1/2016  -0.1 

所以原来的代码将返回这...

POLICY_NUMBER POLICY_TRANSACTION_TYPE POLICY_YEAR_START_DATE COMMISSION_RATE 
1    REN      1/1/2016    0.3 
1    MTA      15/1/2016    0.1 
1    MTA      15/1/2016    -0.1 

我希望它只是为MTA返回其中一行，无论是0.1佣金率还是-0.1佣金率，我都不介意。

Answer 1

你的问题有点含糊，没有样本数据和结果。让我假设重复来自table2。您可以使用row_number()任意选择一个：

from Table_1 a left join 
    (select b.*, 
      row_number() over (partition by POLICY_NUMBER, TRANSACTION_TYPE, year(EFFECTIVE_DATE), month(EFFECTIVE_DATE) 
           order by TRANSACTION_TYPE -- this will be an arbitrary ordering 
           ) as seqnum 
     from Table_2 b 
    ) b 
    on a.POLICY_NUMBER = b.POLICY_NUMBER and 
     a.POLICY_TRANSACTION_TYPE = b.TRANSACTION_TYPE and 
     year(a.POLICY_START_DATE) = year(b.EFFECTIVE_DATE) and 
     month(a.POLICY_START_DATE) = month(b.EFFECTIVE_DATE) 

Answer 2

select a.POLICY_NUMBER, 
     a.POLICY_TRANSACTION_TYPE, 
     a.POLICY_START_DATE, 
     MAX(b.COMMISSION_RATE) 
from Table_1 as a 
left join Table_2 as b 
    on a.POLICY_NUMBER = b.POLICY_NUMBER and 
     a.POLICY_TRANSACTION_TYPE = b.TRANSACTION_TYPE and 
     year(a.POLICY_START_DATE) = year(b.EFFECTIVE_DATE) and 
     month(a.POLICY_START_DATE) = month(b.EFFECTIVE_DATE) 

group by a.POLICY_NUMBER, 
     a.POLICY_TRANSACTION_TYPE, 
     a.POLICY_START_DATE