sql自联接表删除重复的行 [英] sql self-join table remove duplicate lines

问题描述

我有下表:

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

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

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)

对于用户2:(10,3)

for user 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

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

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

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

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

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

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

如何删除重复的行?

tnx

推荐答案

使用您的标识符:

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

测试:FOT表:

create table t ( u int, l int);

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

查询是:

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

(结果)

这篇关于sql自联接表删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！