仅使用JOIN改写无方子查询的SQL语句，解决结果值异常问题

Got it, let's break down why your initial JOIN-only attempt gave wonky results first. When you directly join questions to answers and question_views without pre-aggregating, you're creating a Cartesian product between the answer rows and view rows for each question. For example, if a question has 3 answers and 5 views, you end up with 15 combined rows—and a plain COUNT() would count those duplicates, inflating your numbers.

Here's the correct way to rewrite your query using only JOINs (no subqueries) while avoiding duplicate counts:

SELECT 
    q.vQuestionTitle,
    COUNT(DISTINCT a.iAnswerId) AS NumberOfAnswers,
    COUNT(DISTINCT qv.iViewId) AS NumberOfViews
FROM questions q
INNER JOIN answers a ON a.iQuestionid = q.iQuestionid
INNER JOIN question_views qv ON qv.iQuestionId = q.iQuestionid
WHERE q.vQuestionTitle LIKE '%how%'
GROUP BY q.iQuestionid, q.vQuestionTitle;

Key Notes: #

• COUNT(DISTINCT) is critical: This ensures we only count each unique answer and each unique view once, even though the JOIN creates duplicate pairings. You'll need to replace iAnswerId and iViewId with the actual unique primary key columns from your answers and question_views tables (if your column names are different).
• Group by the question's unique ID: Including q.iQuestionid in the GROUP BY clause prevents edge cases where two different questions might have identical vQuestionTitle values from being merged into a single row.

If your question_views table doesn't have a single unique primary key (e.g., it tracks views by user and timestamp), you can use a combination of columns for the distinct count instead:

COUNT(DISTINCT qv.iUserId, qv.view_timestamp) AS NumberOfViews

内容的提问来源于stack exchange，提问作者Sasi Sajja