MySQL如何在子查询中引用外层当前行post_id主键进行统计

问题解答 #

你标注的current_row_post_id位置直接填写posts_reacts.post_id即可，修正后的完整查询语句如下：

SELECT 
  post_id, 
  title, 
  description, 
  (SELECT COUNT(*) FROM posts_reacts WHERE react_type_id = 1 AND posts.post_id = posts_reacts.post_id) AS total_love, 
  (SELECT COUNT(*) FROM posts_reacts WHERE react_type_id = 2 AND posts.post_id = posts_reacts.post_id) AS total_tanks 
FROM posts;

原理解释 #

这是典型的关联子查询用法：外层查询遍历posts表的每一行时，会把当前行的post_id值带入到内层子查询中，和posts_reacts表的post_id字段做匹配，统计对应反应类型的记录数。

更优写法推荐 #

上述写法会对posts_reacts表进行两次扫描，数据量较大时性能偏低，你可以改用左连接+条件聚合的写法，仅需扫描一次关联表即可得到结果：

SELECT 
  p.post_id, 
  p.title, 
  p.description,
  COUNT(CASE WHEN pr.react_type_id = 1 THEN 1 END) AS total_love,
  COUNT(CASE WHEN pr.react_type_id = 2 THEN 1 END) AS total_tanks
FROM posts p
LEFT JOIN posts_reacts pr ON p.post_id = pr.post_id
GROUP BY p.post_id, p.title, p.description;

如果你的MySQL版本支持8.0+，还可以用更简洁的IF函数替代CASE语句：

SELECT 
  p.post_id, 
  p.title, 
  p.description,
  COUNT(IF(pr.react_type_id = 1, 1, NULL)) AS total_love,
  COUNT(IF(pr.react_type_id = 2, 1, NULL)) AS total_tanks
FROM posts p
LEFT JOIN posts_reacts pr ON p.post_id = pr.post_id
GROUP BY p.post_id, p.title, p.description;

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