基于两列分组的子查询返回重复行的SQL问题求助

问题描述 #

原有SQL语句通过单字段ManufacturePartNBR分组，筛选出ItemStatus不为'Inactive'且该字段值重复的所有记录。现在需要修改为按ManufacturePartNBR和ManufactureNM两个字段的组合分组查找重复记录，但尝试的语句无法正常执行。

原语句（单列分组） #

select *
from "*original_item_master" i
where "ItemStatus" != 'Inactive'  and 
 "ManufacturePartNBR" in (
    select "ManufacturePartNBR"
    from "*original_item_master" i
    group by "ManufacturePartNBR"
    having count (*) > 1 )
order by "ManufacturePartNBR" asc

尝试的错误语句 #

select *
from "*original_item_master" i
where "ItemStatus" != 'Inactive'  and 
  "ManufacturePartNBR" , "ManufactureNM" in (
    select "ManufacturePartNBR", "ManufactureNM" 
    from "*original_item_master" i
    group by "ManufacturePartNBR", "ManufactureNM" 
    having count (*) > 1 )
order by "ManufacturePartNBR" asc 

错误原因 #

尝试语句中的多列IN写法不符合标准SQL语法（仅少数数据库支持无括号的多列IN，主流数据库要求必须将多列用括号包裹）。

解决方案 #

方案1：修正多列IN的语法（兼容PostgreSQL、MySQL 8.0+等）

将多列条件用括号包裹，符合标准SQL的多列IN写法：

select *
from "*original_item_master" i
where "ItemStatus" != 'Inactive'
  and ("ManufacturePartNBR", "ManufactureNM") in (
    select "ManufacturePartNBR", "ManufactureNM"
    from "*original_item_master"
    group by "ManufacturePartNBR", "ManufactureNM"
    having count(*) > 1
)
order by "ManufacturePartNBR" asc

方案2：使用EXISTS子查询（兼容性最强，支持所有主流数据库）

如果你的数据库不支持多列IN，可以用EXISTS关联子查询实现相同逻辑：

select *
from "*original_item_master" i
where "ItemStatus" != 'Inactive'
  and exists (
    select 1
    from "*original_item_master" j
    where j."ManufacturePartNBR" = i."ManufacturePartNBR"
      and j."ManufactureNM" = i."ManufactureNM"
    group by j."ManufacturePartNBR", j."ManufactureNM"
    having count(*) > 1
)
order by "ManufacturePartNBR" asc

方案3：使用窗口函数（性能更优，支持SQL Server、PostgreSQL、MySQL 8.0+等）

通过窗口函数直接计算每条记录所属组合的重复次数，再筛选符合条件的记录：

select *
from (
    select *,
           count(*) over (partition by "ManufacturePartNBR", "ManufactureNM") as duplicate_count
    from "*original_item_master"
    where "ItemStatus" != 'Inactive'
) t
where duplicate_count > 1
order by "ManufacturePartNBR" asc

注：此方案会先筛选活跃记录，再统计重复组合；若需要先统计全表的重复组合再筛选活跃记录，可将WHERE条件移到外层。

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