SQL Query来计算来自另一个查询的出现次数？

Question

我有一些代码输出到html表中的数据。我正在尝试创建一个列，显示特定问题的答案总数（由intQAID定义）。由于$ result是在同一个php脚本中定义的查询，我如何运行第二个查询来运行'mysql_num_rows'来计算问题的答案数量？

$result = mysql_query("SELECT * FROM tblQA WHERE cCategory = 'Football' AND (TO_DAYS(CURDATE()) - TO_DAYS(dPostDateTime) < 1) ORDER BY dPostDateTime DESC, intQAID DESC"); 


while($row = mysql_fetch_array($result)) 
{ 
echo "<tr>"; 
echo "<td>" . $row['intQAID'] . "</td>"; 
echo "<td>" . $row['cUsername'] . "</td>"; 
echo "<td>" . $row['dPostDateTime'] . "</td>"; 
echo "<td>" . Num of Responses . "</td>"; 
echo "<td><a href=answer.php?id=" . $row['intQAID'] . ">" . $row['cBody'] . "</a></td>"; 
echo "</tr>"; 
} 
echo "</table>"; 

Answer 1

不知道你的结构，这是做一些猜测工作。

SELECT *, (SELECT count(*) 
      FROM answers_table at 
      WHERE at.intQAID = tqa1.intQAID) as answercount 
FROM tblQA tqa1 
WHERE cCategory = 'Football' 
    AND (TO_DAYS(CURDATE()) - TO_DAYS(dPostDateTime) < 1) 
ORDER BY dPostDateTime DESC, intQAID DESC"); 

然后，您只需引用要显示它的$row['answercount']。

Answer 2

是否有使用SELECT *的理由？这是一个不使用子选择的查询。加入第二个表格，选择所需的列并将它们放在GROUP BY子句中，并替换*。

SELECT a, b, COUNT(fk_question_id) AS responseCount FROM tQuestions 
LEFT JOIN tAnswers 
    ON fk_question_id = question_id 
GROUP BY a, b 

更新：使用左连接，并计算外键的出现次数以找到没有答案的结果。