MySQL统计数据室用户总评论数与已完成评论数的SQL优化问题

Let's break down what's going wrong with your current query and fix it to get the exact results you're expecting.

The Issue with Your Current Query #

Your line COUNT(b.date_completed IS NOT NULL) AS completed isn't behaving as intended for two key reasons:

• COUNT() counts non-null values, and b.date_completed IS NOT NULL returns a boolean (TRUE or FALSE). In SQL, these boolean values are always treated as non-null—so even when a user has no completed reviews, it counts FALSE as 1 instead of 0.
• For users with existing reviews, every row returns either TRUE or FALSE, so COUNT() ends up tallying all rows, which matches the total review count instead of just completed ones.

The Corrected SQL Query #

Instead of COUNT(), use SUM() paired with a conditional CASE statement. This will accurately count only completed reviews, returning 0 for users with none:

SELECT 
  a.user_id, 
  a.email, 
  COUNT(b.review_id) AS num_reviews, 
  SUM(CASE WHEN b.date_completed IS NOT NULL THEN 1 ELSE 0 END) AS completed
FROM users a 
LEFT JOIN reviews b ON a.user_id = b.created_by 
WHERE a.dataroom_id = 44 
GROUP BY a.user_id, a.email  -- Added a.email for compatibility with strict SQL modes
ORDER BY num_reviews DESC

Why This Works #

• CASE WHEN b.date_completed IS NOT NULL THEN 1 ELSE 0 END: For each review, this returns 1 if the review is completed, 0 if it's not (or 0 if there's no review at all, thanks to the LEFT JOIN).
• SUM() adds up all those 1s and 0s, giving you the exact number of completed reviews per user. Users with no completed reviews will correctly show 0.
• Note: I added a.email to the GROUP BY clause—this is required in most modern SQL databases (like PostgreSQL, MySQL with ONLY_FULL_GROUP_BY enabled) to avoid errors, since a.email is a non-aggregated column in the SELECT list.

Example Output #

This query will produce results matching your desired format:

╔═════════╦═══════════════════════════════════════╗ 
║ User_id ║ Email         ║ # Reviews ║ # Completed ║ 
╠═════════╬═══════════════╬═══════════╬═════════════╣ 
║ 1       ║ test0@e.com   ║ 4         ║ 2           ║ 
║ 2       ║ test1@e.com   ║ 1         ║ 0           ║ 
║ 3       ║ test2@e.com   ║ 10        ║ 5           ║ 
║ 4       ║ test3@e.com   ║ 5         ║ 3           ║ 

内容的提问来源于stack exchange，提问作者Barney Vaughan