MySQL sql自连接表删除重复行

sql self-join table remove duplicate lines

提问by Shai Zarzewski

I have the following table:

我有下表：

╔════════╦════════════╗
║ USERID ║ LANGUAGEID ║
╠════════╬════════════╣
║      1 ║          2 ║
║      1 ║          7 ║
║      1 ║          8 ║
║      2 ║         10 ║
║      2 ║          3 ║
╚════════╩════════════╝

now I want to create all the possible pairs of languages for each user which means that I want the result set to be: for user 1: (2,7), (7,8), (2,8)

现在我想为每个用户创建所有可能的语言对，这意味着我希望结果集是：对于用户 1: (2,7), (7,8), (2,8)

for user 2: (10,3)

用户 2: (10,3)

to do so I've done the following query:

为此，我完成了以下查询：

SELECT a.userId , a.LanguageId, b.LanguageId
FROM knownlanguages a, knownlanguages b  
WHERE a.userID=b.userID  
AND a.LanguageId<>b.LanguageId

the result that i'm getting is for user 1: (2,7), (7,8), (2,8) , (7,2), (8,7), (8,2)

我得到的结果是用户 1: (2,7), (7,8), (2,8) , (7,2), (8,7), (8,2)

for user 2: (10,3), (3,10)

用户 2: (10,3), (3,10)

there is no difference for me between (10,3) and (3,10)

(10,3) 和 (3,10) 对我来说没有区别

how can I remove the duplicate lines?

如何删除重复的行？

tnx

tnx

回答by dani herrera

With your identifiers:

使用您的标识符：

SELECT a.userId , a.LanguageId, b.LanguageId
  FROM knownlanguages a inner join knownlanguages b  
    on a.userID=b.userID and a.LanguageId < b.LanguageId

Testing: Fot table:

测试： Fot 表：

create table t ( u int, l int);

insert into t values 
(    1,               2),
(    1,               7),
(    1,               8),
(    2,               10),
(    2,               3);

The query is:

查询是：

select t1.u, t1.l as l1, t2.l as l2
from t t1 inner join t t2
   on t1.u = t2.u and t1.l < t2.l

( Results)

（结果）

回答by John Woo

SELECT  userId,
        LEAST(LANG_ID1, LANG_ID2) ID1,
        GREATEST(LANG_ID1, LANG_ID2) ID2
FROM
    (
      SELECT a.userId, 
             a.LanguageId LANG_ID1, 
             b.LanguageId LANG_ID2
      FROM   knownlanguages a, knownlanguages b  
      WHERE  a.userID=b.userID  AND 
             a.LanguageId <> b.LanguageId
    ) s
GROUP BY userId, ID1, ID2

• SQLFiddle Demo

• SQLFiddle 演示

The output,

输出，

╔════════╦═════╦═════╗
║ USERID ║ ID1 ║ ID2 ║
╠════════╬═════╬═════╣
║      1 ║   2 ║   7 ║
║      1 ║   2 ║   8 ║
║      1 ║   7 ║   8 ║
║      2 ║   3 ║  10 ║
╚════════╩═════╩═════╝

or simply,

或者干脆，

  SELECT a.userId, 
         a.LanguageId LANG_ID1, 
         b.LanguageId LANG_ID2
  FROM   knownlanguages a, knownlanguages b  
  WHERE  a.userID=b.userID  AND 
         a.LanguageId < b.LanguageId