SQL问题：筛选重复ID且对应value>30的所有行

原查询的核心问题 #

原SQL语句create table test as select * from A where ID in (select ID from A where value >= 30);仅实现了筛选所有属于“存在value≥30记录”的ID的行，完全未处理“ID存在重复”这个关键条件。这会导致两种不符合预期的结果：

• 包含ID仅出现一次但value≥30的行
• 未对ID的重复情况做校验，无法保证返回的行属于重复ID的集合

修正后的查询写法 #

方法1：窗口函数实现（推荐，适配多数现代数据库） #

通过窗口函数按ID分组统计出现次数，同时判断该ID是否存在value>30的记录：

create table test as
select *
from (
    select 
        *,
        COUNT(*) OVER (PARTITION BY ID) as id_count,
        MAX(value) OVER (PARTITION BY ID) as max_value
    from A
) t
where id_count > 1 and max_value > 30;
quit;

方法2：子查询筛选目标ID后关联 #

先找出满足“重复出现”且“存在value>30记录”的ID集合，再关联原表获取对应所有行：

create table test as
select A.*
from A
join (
    select ID
    from A
    group by ID
    having COUNT(*) > 1 and MAX(value) > 30
) filtered_ids on A.ID = filtered_ids.ID;
quit;

逻辑说明 #

两种写法均严格匹配需求：

1. 先锁定同时满足ID重复出现和该ID下至少有一条记录value>30的ID集合
2. 返回这些ID对应的所有行（若需求仅需该ID下value>30的行，可在最终筛选中追加value>30条件）

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