SQL-查找两列相同的所有实例 [英] SQL - find all instances where two columns are the same
本文介绍了SQL-查找两列相同的所有实例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
因此，我有一个简单的表，其中包含与特定博客post有关的user中的comments.
So I have a simple table that holds comments from a user that pertain to a specific blog post.
id | user | post_id | comment
----------------------------------------------------------
0 | john@test.com | 1001 | great article
1 | bob@test.com | 1001 | nice post
2 | john@test.com | 1002 | I agree
3 | john@test.com | 1001 | thats cool
4 | bob@test.com | 1002 | thanks for sharing
5 | bob@test.com | 1002 | really helpful
6 | steve@test.com | 1001 | spam post about pills
我想获得用户在同一帖子上发表两次评论的所有实例(表示相同的user和相同的post_id).在这种情况下，我会返回:
I want to get all instances where a user commented on the same post twice (meaning same user and same post_id). In this case I would return:
id | user | post_id | comment
----------------------------------------------------------
0 | john@test.com | 1001 | great article
3 | john@test.com | 1001 | thats cool
4 | bob@test.com | 1002 | thanks for sharing
5 | bob@test.com | 1002 | really helpful
我以为DISTINCT是我所需要的，但这只给了我唯一的行.
I thought DISTINCT was what I needed but that just gives me unique rows.
推荐答案
您可以使用GROUP BY和HAVING查找具有多个条目的user和post_id对:
You can use GROUP BY and HAVING to find pairs of user and post_id that have multiple entries:
SELECT a.*
FROM table_name a
JOIN (SELECT user, post_id
FROM table_name
GROUP BY user, post_id
HAVING COUNT(id) > 1
) b
ON a.user = b.user
AND a.post_id = b.post_id
这篇关于SQL-查找两列相同的所有实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
