sql—在同一表的两列之间查找重复项

ttygqcqt 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(387)

我想在没有id的两列之间找到重复的值。示例：
员工表

-----------------------------------
employee_one    | employee_two    |    
-----------------------------------
JOHN SMITH      | JACK STEVENS    |
MASON LEWIS     | JOHN WALKER     |
ANDREA YOUNG    | MARTINA ROBINSON|
JACK STEVENS    | JOHN SMITH      |
JOHN WALKER     | MASON LEWIS     |
MARTINA ROBINSON| ANDREA YOUNG    |

我想要的结果是：

-----------------------------------
employee_one    | employee_two    |    
-----------------------------------
JOHN SMITH      | JACK STEVENS    |
MASON LEWIS     | JOHN WALKER     |
ANDREA YOUNG    | MARTINA ROBINSON|

或

-----------------------------------
employee_one    | employee_two    |    
-----------------------------------
JACK STEVENS    | JOHN SMITH      |
JOHN WALKER     | MASON LEWIS     |
MARTINA ROBINSON| ANDREA YOUNG    |

我的问题是，我的查询总是找到所有的结果，我得到相同的表。我试过：

SELECT DISTINCT t1.* 
    FROM employees 
    AS t1 LEFT JOIN employees AS t2 ON (t1.employee_one = t2.employee_two AND t1.employee_two = t2.employee_one) 
OR (t1.employee_one = t2.employee_one AND t1.employee_two = t2.employee_two)

但我得到了同样的结果

sql scala DataFrame

来源：https://stackoverflow.com/questions/62596965/finding-duplicates-between-two-columns-same-table

1条答案

按热度按时间

enyaitl31#

每对名称将显示两次，因此使用where子句将输出限制为employee\u one<employee\u two:

select t1.*
from employees t1
where employee_one < employee_two
  and exists (
    select *
    from employees t2
    where t2.employee_two = t1.employee_one
      and t2.employee_one = t1.employee_two)

警告：假设没有employee\u one=employee\u two的行。

赞(0）回复(0）举报 2021-07-24

我来回答

sql—在同一表的两列之间查找重复项

1条答案

相关问题

热门标签

最新问答