如何找到相同单词和相同主题的副本？

9udxz4iz 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(233)

我有一张像下面这样的table。表名为“test”。

+----+----------+----------+
| id | word     | topic    |
+----+----------+----------+
|  1 | plus     | math     |
|  2 | minus    | math     |
|  3 | multiple | math     |
|  4 | minus    | math     |
|  5 | cpu      | computer |
|  6 | click    | computer |
|  7 | monitor  | computer |
|  8 | cpu      | computer |
|  9 | multiple | computer |
+----+----------+----------+

如何找到相同主题的重复单词？
我想要下面这样的结果。

+----+----------+----------+
 | id | word     | topic    |
 +----+----------+----------+
 |  2 | minus    | math     |
 |  4 | minus    | math     |
 |  5 | cpu      | computer |
 |  8 | cpu      | computer |
 +----+----------+----------+

mysql

来源：https://stackoverflow.com/questions/52994773/how-can-i-find-duplicate-with-same-word-and-same-topic

3条答案

按热度按时间

vpfxa7rd1#

如果您不需要为每个 id ，您可以执行以下操作：

select word, topic, group_concat(id)
from t
group by word, topic
having count(*) > 1

聚合可能非常昂贵，因此如果您确实需要原始行， exists 是一个选项：

select t.*
from t
where exists (select 1
              from t t2
              where t2.word = t.word and t2.topic = t.topic and t2.id <> t.id
             );

为了提高性能，您需要一个索引 (word, topic, id) .

赞(0）回复(0）举报 2021-06-18

vc6uscn92#

SELECT DISTINCT x.* 
          FROM test x 
          JOIN test y 
            ON y.id <> x.id 
           AND y.word = x.word 
           AND y.topic = x.topic;

赞(0）回复(0）举报 2021-06-18

62o28rlo3#

select *
from your_table
where word in
(
    select word
    from your_table
    group by word, topic
    having count(*) > 1
)

赞(0）回复(0）举报 2021-06-18

我来回答

如何找到相同单词和相同主题的副本？

3条答案

相关问题

热门标签

最新问答