如何不使用聚合操作实现特定条件下的重复值SQL查询

没问题！要替代你现有的聚合查询，我们可以用窗口函数或者自连接两种思路，都能实现选出c.string = 'Some string'对应出现次数大于1的a.value，而且完全不用GROUP BY和HAVING这类聚合操作。

方法1：窗口函数（推荐，简洁高效） #

现在大部分主流数据库（比如MySQL 8+、PostgreSQL、SQL Server）都支持窗口函数，这种写法逻辑清晰，性能也更好：

SELECT DISTINCT a.value
FROM (
    SELECT 
        a.value,
        -- 按a.value分组计算每组的记录数，不用GROUP BY
        COUNT(*) OVER (PARTITION BY a.value) AS record_count
    FROM table1 c 
    JOIN table2 a ON c.value2 = a.value_2 
    JOIN table3 o ON c.value3 = o.value_3 AND o.value4 = 1990 
    WHERE c.string = 'Some string'
) AS subquery
-- 筛选出记录数大于1的value
WHERE record_count > 1;

解释 #

• 子查询里用COUNT(*) OVER (PARTITION BY a.value)给每条记录打上对应a.value的总记录数标签
• 外层用DISTINCT去重（因为同一个a.value会有多个匹配的记录），只保留总记录数大于1的a.value

方法2：自连接（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用自连接的方式，通过匹配同a.value下的不同记录来实现：

SELECT DISTINCT a1.value
FROM table1 c1
JOIN table2 a1 ON c1.value2 = a1.value_2
JOIN table3 o1 ON c1.value3 = o1.value_3 AND o1.value4 = 1990
-- 自连接table1，找到同string、同value2（对应同一个a.value）的另一条不同记录
JOIN table1 c2 
    ON c1.string = c2.string 
    AND c1.value2 = c2.value2
    AND c1.id <> c2.id  -- 这里替换成table1的主键/唯一标识字段，避免同一条记录自匹配
JOIN table3 o2 ON c2.value3 = o2.value_3 AND o2.value4 = 1990
WHERE c1.string = 'Some string';

解释 #

• 通过自连接table1，找到属于同一个a.value（通过c1.value2 = c2.value2关联）且是不同记录（通过主键不等判断）的条目
• 只要能找到这样的配对，就说明该a.value对应的记录数至少为2，最后用DISTINCT去重得到结果

小提示 #

• 优先选窗口函数的写法，自连接会产生较多中间数据，数据量大时性能不如窗口函数
• 自连接里的c1.id <> c2.id必须替换成你实际表中的主键或唯一非空字段，否则会出现错误匹配

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