如何修正SQL查询以筛选重复AppID且CreatedBy为Auto的记录？

原始表数据 #

需求说明 #

需要筛选出AppID对应的CreatedBy为"Auto"的记录数大于1的所有相关记录，预期结果如下：

原始查询（未得到预期结果） #

select name, appId, createdby 
from <table> 
where appId in (
    select appId from <table> 
    group by appId 
    having count(*) > 1
) and createdby ='Auto' 
order by appId desc;

问题分析与修正方案 #

原始查询的子查询仅统计了所有记录数大于1的AppID，但这些AppID可能同时包含Auto和Manual类型的记录（比如AppID 123、789），导致结果中混入了不符合要求的记录。

要解决这个问题，需要让子查询只统计CreatedBy为Auto的记录中重复次数大于1的AppID，以下是两种可行的修正方案：

方案一：修改子查询过滤条件 #

select name, appId, createdby 
from <table> 
where appId in (
    select appId from <table> 
    where createdby = 'Auto'  -- 先筛选Auto记录再统计重复
    group by appId 
    having count(*) > 1
) and createdby = 'Auto' 
order by appId desc;

方案二：使用窗口函数（更直观） #

select name, appId, createdby
from (
    select 
        name, appId, createdby,
        count(*) over (partition by appId) as auto_record_count
    from <table>
    where createdby = 'Auto'
) t
where auto_record_count > 1
order by appId desc;

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