SQL自连接表如何删除重复行

Question

我有以下表格：

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

现在我想为每个用户创建所有可能的语言配对，这意味着我希望结果集如下所示：

对于用户1：(2,7)，(7,8)，(2,8)

对于用户2：(10,3)

为此，我已经执行了以下查询：

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

我得到的结果是：

对于用户1：(2,7)，(7,8)，(2,8)，(7,2)，(8,7)，(8,2)

对于用户2：(10,3)，(3,10)

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

如何去除重复的行？

谢谢

- Shai Zarzewski

---

WHERE a.userID=b.userID AND a.LanguageId < b.LanguageId 可以翻译为：当a的userID等于b的userID且a的LanguageId小于b的LanguageId时。 - wildplasser

Answer 1

使用您的标识符：

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

SQL Fiddle

MySQL 5.6 Schema Setup:

create table t ( u int, l int);

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

查询 1：

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

结果:

| u | l1 | l2 |
|---|----|----|
| 1 |  2 |  7 |
| 1 |  2 |  8 |
| 1 |  7 |  8 |
| 2 |  3 | 10 |

- dani herrera

---

非常干净简洁！只需添加 **a.LanguageId < b.LanguageId**，就可以删除所有重复项。 - JDawg

我认为在使用<>的地方使用<是如此简单，以至于它应该是首先考虑的答案。 - levolutionniste

Answer 2

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演示

输出结果为：

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

或者简单地说，

  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

- John Woo