mysql - SQL self join中如何让相同的字段不出现两次？

Question

我想在故事中进行 self 加入并结交一对顾客，但我不希望同一对出现两次，我正在使用此代码

  SELECT DISTINCT A.customer_num AS ' num1' 
                , B.customer_num AS 'num2' 
                , A.customer_name AS 'name1' 
                , B.customer_name AS 'name2' 
                , A.city AS 'city' 
             FROM tbl_customer A 
                , tbl_customer B
            WHERE A.customer_num <> B.customer_num
              AND A.city = B.city  ;

它给了我记录，但是这对重复了，这就是我得到的结果 My Result例如第 1 对和第 6 对与我得到的相同我正在使用 MySQL Workbench。

Answer 1

由于您的 where自连接的条件是对称(即 A.customer_num <> B.customer_num )每对将在输出中出现两次。 A 是哪条记录并不重要或 B ，因此将条件切换为不对称 将解决问题。

当你这样做时，切换到使用 ANSI 连接；你不再需要 DISTINCT ，也是:

SELECT
    A.customer_num AS 'num1'
,   B.customer_num AS 'num2'
,   A.customer_name AS 'name1'
,   B.customer_name AS 'name2' ,
A.city AS 'city' 
FROM tbl_customer A -- It does not matter if you use < or > below
JOIN tbl_customer B ON A.customer_num > B.customer_num AND A.city = B.city