sql - 从结果中消除重复组合

Question

我有一个这样的选择结果:

from_loc    | to_loc   |
-------------------------
 A          |  B
------------------------
 B          |  C
------------------------
 B          |  A
------------------------  

如何从表中消除重复项，这意味着出现 A 到 B 和 B 到 A 表示重复。

我正在尝试创建这样的结果，在尝试了几种方法后我无法解决这个问题......

from_loc    | to_loc   |
-------------------------
 A          |  B
------------------------
 B          |  C
------------------------

谁能给我一些提示或引用我怎样才能达到这种结果？

Answer 1

我没有测试这个解决方案，但它可以有更好的性能(更少的逻辑读取):

DECLARE @MyTable TABLE
(
    from_loc VARCHAR(100) NOT NULL,
    to_loc   VARCHAR(100) NOT NULL
);
INSERT  @MyTable (from_loc, to_loc) VALUES  ('A', 'B');
INSERT  @MyTable (from_loc, to_loc) VALUES  ('B', 'C');
INSERT  @MyTable (from_loc, to_loc) VALUES  ('B', 'A');

SELECT  DISTINCT src.from_loc_new, src.to_loc_new
FROM
(
SELECT  CASE WHEN x.from_loc <= x.to_loc THEN x.from_loc ELSE x.to_loc END AS from_loc_new,
        CASE WHEN x.from_loc <= x.to_loc THEN x.to_loc ELSE x.from_loc END AS to_loc_new
FROM    @MyTable x
) src
-- You could also test these query hints to see if there is a better performance
-- OPTION (HASH GROUP) 
-- or 
-- OPTION (ORDER GROUP);