如何在SQL Server中提取表中匹配的(x,y)与(y,x)对角行？

可行方案 #

方案一：用CASE表达式替代Oracle的LEAST/GREATEST函数 #

SQL Server没有内置的LEAST和GREATEST函数，可通过CASE表达式实现相同逻辑，直接改写原Oracle查询的核心逻辑：

SELECT 
    CASE WHEN x < y THEN x ELSE y END AS x,
    CASE WHEN x > y THEN x ELSE y END AS y
FROM mytable
GROUP BY 
    CASE WHEN x < y THEN x ELSE y END,
    CASE WHEN x > y THEN x ELSE y END
HAVING COUNT(*) = 2
ORDER BY x ASC;

该语句将每行的x和y转换为「小值在前、大值在后」的统一格式，分组后统计出现次数为2的记录，即可得到所有存在对角匹配对（如(2,24)与(24,2)）以及自身重复两次的行（如(13,13)），与期望输出完全一致。

方案二：用EXISTS子查询直接匹配反向行 #

通过子查询检查当前行是否存在对应的反向行，再用DISTINCT去重得到结果：

SELECT DISTINCT
    CASE WHEN t1.x < t1.y THEN t1.x ELSE t1.y END AS x,
    CASE WHEN t1.x > t1.y THEN t1.x ELSE t1.y END AS y
FROM mytable t1
WHERE EXISTS (
    SELECT 1 
    FROM mytable t2 
    WHERE t2.x = t1.y AND t2.y = t1.x
)
ORDER BY x ASC;

这种方式逻辑直观，适合理解反向匹配的需求，同样能输出目标结果。

方案三：使用LAG()函数实现（进阶方式） #

LAG()函数用于获取同组内前一行的数据，结合分组排序可实现需求，逻辑相对复杂：

WITH sorted_data AS (
    SELECT 
        x, y,
        -- 生成统一的配对标识
        CASE WHEN x < y THEN x ELSE y END AS pair_min,
        CASE WHEN x > y THEN x ELSE y END AS pair_max,
        -- 获取同组内前一行的x和y值
        LAG(x) OVER (PARTITION BY pair_min, pair_max ORDER BY x) AS prev_x,
        LAG(y) OVER (PARTITION BY pair_min, pair_max ORDER BY x) AS prev_y
    FROM mytable
)
SELECT DISTINCT pair_min AS x, pair_max AS y
FROM sorted_data
-- 匹配反向行或重复的自身行
WHERE (prev_x = y AND prev_y = x) OR (x = y AND prev_x = x AND prev_y = y)
ORDER BY x ASC;

该方法先按统一配对格式分组，再用LAG()检查相邻行是否为反向对或重复行，最后去重得到结果。

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