棘手的查询:获取一个列的计数与两个条件的另一个不同的列 - Tricky query: getting Count of one column with 2 conditions for another distinct column

Tricky query: getting Count of one column with 2 conditions for another distinct column

我正在努力解决一个具有挑战性的查询。

我有2个表:posts, answers

posts有ID, PID, nameanswers有ID, PID, approved

ID为用户ID, PID为帖子ID。

我想做的是列出所有的帖子名称(posts.name)和有多少独特的人(ID s)回复了他们的帖子。诀窍是，我只想计数被批准的回复(answers.approved = 'Y')，而我不想计数创建帖子的人，所以(post.ID != answers.ID)。

所以结果应该显示如下:

Name       People with Approved Replies
Name 1                               10
Name 2                                7

我当前的代码是一个混乱和错误的方法。它只给出了一个结果，而且回复的数量太多了。但这是我目前的内容:

$results = $dbh->prepare("select COUNT(DISTINCT answers.ID) AS reply,
 posts.ID,    
 posts.PID,
 posts.name,
 answers.PID,
 answers.approved
FROM answers
LEFT JOIN posts ON answers.PID=posts.PID
WHERE (answers.approved = 'Y') LIMIT 10
GROUP BY answer.ID");
$results->execute();
$row = $results->fetchAll(PDO::FETCH_ASSOC);

我认为这个查询应该做你想要的

SELECT 
    p.name,
    p.id,
    p.pid,
    (SELECT COUNT(DISTINCT id) 
     FROM answers 
     WHERE  answers.pid = p.pid 
     AND    answers.approved = 'Y' 
     AND    answers.id != p.id) as reply
FROM posts p

这将选择帖子名称，id，用户id和不同的批准答案的计数