SQL - 查找两列相同的所有实例

Question

所以我有一个简单的表格，其中包含 comments来自 user属于特定博客的 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 )。在这种情况下，我会返回:

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是我需要的，但这只是给了我独特的行。

Answer 1

您可以使用 GROUP BY和 HAVING找到对 user和 post_id有多个条目:

  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