如何编写SQL查询按无序唯一值对筛选表中数据？

假设有如下表格 myTable：

执行以下查询时：

select * from  myTable
where (col1,col2) 
in(select col1, col2 from myTable group by col1,col2
having count(*) >= 4 and count(*) <= 100000 order by count(*) desc)

会遗漏12345-6789这个值对，因为它的出现形式分为两种，统计结果如下：

尝试用LEAST/GREATEST合并统计的查询：

SELECT CONCAT(LEAST(col1, col2), ',', GREATEST(col1, col2)) pair, 
       COUNT(*) count
FROM myTable
GROUP BY pair
having count(*) >= 4 and count(*) <= 100000

输出结果为：

但这种输出无法直接用于外层SELECT的IN条件中。需要编写SQL实现：筛选出所有属于“不考虑顺序的col1与col2值对，且该对总出现次数在4到100000之间”的记录，最终得到如下结果：

补充：目前能想到的是拆分流程，先用上述查询得到结果，再在代码中拆分值构建查询，但希望找到更优的纯SQL方案。

可以通过以下几种纯SQL方式实现需求：

方法1：关联子查询匹配无序值对 #

利用LEAST和GREATEST在子查询中统计无序值对的总数，外层查询通过匹配无序对的两个元素来筛选记录：

SELECT t.*
FROM myTable t
JOIN (
    SELECT 
        LEAST(col1, col2) AS min_val,
        GREATEST(col1, col2) AS max_val,
        COUNT(*) AS cnt
    FROM myTable
    GROUP BY min_val, max_val
    HAVING cnt BETWEEN 4 AND 100000
) AS pairs 
ON (t.col1 = pairs.min_val AND t.col2 = pairs.max_val) 
OR (t.col1 = pairs.max_val AND t.col2 = pairs.min_val);

这个方法直接通过关联查询匹配无序值对，不需要拼接字符串，性能更优，也避免了字符串拆分的问题。

方法2：用EXISTS子查询判断 #

如果更倾向于使用EXISTS而非JOIN，可以这样写：

SELECT *
FROM myTable t
WHERE EXISTS (
    SELECT 1
    FROM myTable
    WHERE 
        (LEAST(col1, col2) = LEAST(t.col1, t.col2)) 
        AND (GREATEST(col1, col2) = GREATEST(t.col1, t.col2))
    GROUP BY LEAST(col1, col2), GREATEST(col1, col2)
    HAVING COUNT(*) BETWEEN 4 AND 100000
);

该查询对每条记录，检查其对应的无序值对总出现次数是否符合条件。

方法3：预计算无序值对后匹配（适合兼容低版本SQL） #

如果数据库不支持在JOIN的ON子句中使用OR，可以先预计算所有符合条件的无序值对的两种排列，再用IN条件匹配：

SELECT *
FROM myTable
WHERE (col1, col2) IN (
    SELECT min_val, max_val
    FROM (
        SELECT 
            LEAST(col1, col2) AS min_val,
            GREATEST(col1, col2) AS max_val,
            COUNT(*) AS cnt
        FROM myTable
        GROUP BY min_val, max_val
        HAVING cnt BETWEEN 4 AND 100000
    ) AS valid_pairs
    UNION ALL
    SELECT max_val, min_val
    FROM (
        SELECT 
            LEAST(col1, col2) AS min_val,
            GREATEST(col1, col2) AS max_val,
            COUNT(*) AS cnt
        FROM myTable
        GROUP BY min_val, max_val
        HAVING cnt BETWEEN 4 AND 100000
    ) AS valid_pairs
);

这种方式通过UNION ALL生成无序值对的两种顺序，再用IN条件匹配，逻辑直观但性能略逊于前两种方法。

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