高效部分DISTINCT ON：获取ID唯一且validity相同行的查询方案

嘿，针对你提到的meds表场景——需要找出ID字段重复但validity（有效期）取值完全相同的所有行（也就是同一药品的不同变体：ID相同、部分属性不同但有效期一致的记录），这里有几种高效的实现方案，优先推荐窗口函数写法，它在大多数现代数据库中性能最优：

方案1：窗口函数（推荐，高效易读） #

这是目前性能最好的写法，仅需扫描一次表，支持MySQL 8+、PostgreSQL、SQL Server、Oracle等所有现代数据库：

SELECT id, validity, [替换为你需要返回的其他字段]
FROM (
    SELECT 
        *,
        -- 按ID+有效期分组，计算每组的记录数
        COUNT(*) OVER (PARTITION BY id, validity) AS duplicate_count
    FROM meds
) AS subquery
-- 筛选出分组内记录数>1的行（即存在重复的ID+有效期组合）
WHERE duplicate_count > 1;

为什么高效？ #

窗口函数通过一次表扫描完成分组计数，避免了自连接带来的两次表扫描，在数据量较大时性能差距非常明显。而且逻辑清晰，后续维护也方便。

方案2：自连接（兼容旧版数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用自连接实现，但性能略逊于窗口函数：

SELECT DISTINCT m1.*
FROM meds m1
JOIN meds m2 
    ON m1.id = m2.id 
    AND m1.validity = m2.validity
    AND m1.[主键字段] != m2.[主键字段]; -- 替换为表的唯一标识（比如主键ID），避免匹配自身

注意事项 #

• 必须用唯一标识字段排除自身匹配，否则会把所有行都返回
• DISTINCT用来避免同一行被多次返回

性能优化小技巧 #

• 给id和validity建立联合索引，能大幅加速分组或连接操作：

  CREATE INDEX idx_id_validity ON meds(id, validity);
  

• 尽量只返回需要的字段，不要用SELECT *，减少数据传输开销
• 如果只需要先排查哪些ID+有效期组合存在重复，可以用更轻量的写法：

  SELECT id, validity
  FROM meds
  GROUP BY id, validity
  HAVING COUNT(*) > 1;
  

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