SELECT * FROM TB T1
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T1.字段A=T2.字段B
AND T1.字段B=T2.字段A AND T1.字段A>T2.字段A)
;WITH cte(A,B) AS
(
SELECT 1,2 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,2
)
SELECT DISTINCT *
FROM cte a INNER JOIN cte b ON a.A = b.B AND a.B = b.A AND a.A >= b.A
;WITH CTE AS(
SELECT 字段A,字段B
,ROW_NUMBER()OVER(PARTITION BY
CASE WHEN 字段A>字段B THEN 字段A ELSE 字段B END
,CASE WHEN 字段A>字段B THEN 字段B ELSE 字段A END
ORDER BY 字段A)RN
FROM TB
)
SELECT 字段A,字段B FROM CTE WHERE RN=1
-- 借 楼上几位的数据
;WITH cte(A,B) AS
(
SELECT 1,2 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 2,2
)
select
distinct
case when A > B then A else B end A ,
case when A > B then B else A end B
from cte
A B
----------- -----------
2 1
2 2
(2 行受影响)
