如何获取具有重复列的行数？

let's say that the following is my table

让我们说以下是我的表

id | name | topic | reply
...
1 | user1 | 00001 | yes              ## replied on topic 00001
2 | user2 | 00002 | yes              ** replied on topic 00002
3 | user1 | 00001 | yes              ## replied on topic 00001
4 | user5 | 00001 | no
5 | user1 | 00001 | yes              ## replied on topic 00001
6 | user1 | 00002 | no
7 | user2 | 00002 | yes              ** replied on topic 00002
8 | user3 | 00001 | no
9 | user4 | 00002 | yes

Imagine that this is a topic table. Only user1 and user reply on a single topic more than once (topic 00001 and 00002) How can I select the number of user that reply on a topic more than once, which should return 2 from 5

想象一下，这是一个主题表。只有user1和user多次回复一个主题（主题00001和00002）如何选择多次回复某个主题的用户数，该用户应从5回复2

2 个解决方案

#1

This will give you the number of users that replied more than once on a topic:

这将为您提供在主题上多次回复的用户数：

SELECT COUNT(DISTINCT name)
FROM (
    SELECT name
    FROM  yourtable
    WHERE reply = 'yes'
    GROUP BY name, topic
    HAVING COUNT(*) > 1
) T1

#2

First you need to work out the number of replies per user per topic and then count up the number of distinct users that have more than one reply for any topic.

首先，您需要计算每个主题的每个用户的回复数量，然后计算对任何主题有多个回复的不同用户的数量。

SELECT COUNT(DISTINCT name) FROM (
    SELECT name, topic, count(*) replies
    FROM table
    WHERE reply = 'yes'
    GROUP BY name, topic
) a
WHERE replies > 1

#1