使用DISTINCT后仍出现重复记录？SQL查询问题求助

原始表结构 #

entity_id|effective_date|value|
A        |2023-09-09    |234  |
A        |2023-09-06    |345  |
B        |2023-09-02    |341  |
C        |2023-09-01    |347  |

需求 #

获取所有唯一entity_id，以及其对应的**最大effective_date**和该日期下的value，最终每个entity_id仅保留一条记录。

当前使用的SQL #

select distinct entity_id, value, max(effective_date) start_date
from refdata.investment_raw ir
where attribute_id  = 232
  and entity_id in (select invest.val as investment_id  
                    from refdata.ved soi
                    inner join refdata.ved invest
                      on soi.entity_id = invest.entity_id 
                     and current_date  between invest.start_date and invest.end_date
                     and invest.attribute_code = 'IssuerId'
                     and soi.attribute_code = 'SO'
                     and  soi.val in ('1','2')
                     and current_date between soi.start_date and soi.end_date)
group by entity_id, value 

问题现象 #

查询结果中entity_id出现重复（比如A有两条记录），不符合每个entity_id仅一条的预期。

当前查询的问题 #

1. GROUP BY逻辑错误：你把value也加入了GROUP BY子句，这意味着同一个entity_id只要value不同，就会被分成独立的分组。比如A的两条记录value分别是234和345，会被分成两组，每组都计算出自己的max(effective_date)，最终导致A出现两条结果。
2. DISTINCT完全多余：GROUP BY本身已经会对分组后的结果去重，加DISTINCT根本解决不了分组逻辑错误带来的重复问题，属于无效操作。

正确解决方案 #

方法一：使用窗口函数（推荐，通用型强） #

利用ROW_NUMBER()窗口函数，给每个entity_id的记录按effective_date降序排名，取排名为1的记录，就是最新日期对应的那条：

select entity_id, effective_date, value
from (
    select 
        entity_id, 
        effective_date, 
        value,
        row_number() over(partition by entity_id order by effective_date desc) as rn
    from refdata.investment_raw ir
    where attribute_id  = 232
      and entity_id in (select invest.val as investment_id  
                        from refdata.ved soi
                        inner join refdata.ved invest
                          on soi.entity_id = invest.entity_id 
                         and current_date  between invest.start_date and invest.end_date
                         and invest.attribute_code = 'IssuerId'
                         and soi.attribute_code = 'SO'
                         and  soi.val in ('1','2')
                         and current_date between soi.start_date and soi.end_date)
) t
where rn = 1;

逻辑说明：

• partition by entity_id：按entity_id分组，每组内单独计算排名
• order by effective_date desc：组内按生效日期从新到旧排序
• rn=1：只保留每组的第一条（最新日期）记录

方法二：子查询关联（兼容老版本数据库） #

先通过子查询获取每个entity_id的最大effective_date，再关联原表拿到对应的value：

select ir.entity_id, ir.effective_date, ir.value
from refdata.investment_raw ir
inner join (
    select entity_id, max(effective_date) as max_date
    from refdata.investment_raw
    where attribute_id = 232
      and entity_id in (select invest.val as investment_id  
                        from refdata.ved soi
                        inner join refdata.ved invest
                          on soi.entity_id = invest.entity_id 
                         and current_date  between invest.start_date and invest.end_date
                         and invest.attribute_code = 'IssuerId'
                         and soi.attribute_code = 'SO'
                         and  soi.val in ('1','2')
                         and current_date between soi.start_date and soi.end_date)
    group by entity_id
) t on ir.entity_id = t.entity_id and ir.effective_date = t.max_date
where ir.attribute_id = 232;

逻辑说明：

1. 子查询先算出每个entity_id的最大生效日期
2. 用entity_id和最大日期关联原表，精准匹配到对应的value记录

内容的提问来源于stack exchange，提问作者Pratik