筛选含特定name的positions的district时，如何统计其全部positions数？

我明白你的需求了——你想筛选出包含特定名称职位的行政区（districts），同时要统计每个行政区的全部职位（positions）数量，而不是仅符合筛选条件的数量。你的现有SQL问题出在两个地方：

1. 关联条件可能写反了（你说districts和positions是一对多关系，应该是positions.district_id = districts.id，而不是positions.id = districts.position_id，后者是多对一逻辑）；
2. WHERE子句直接过滤了positions表，导致统计范围被限制在符合条件的职位里，而非全部。

下面提供两种可行的解决方案：

方法一：使用EXISTS筛选+LEFT JOIN统计总数 #

这个方法先通过EXISTS子句找出存在符合条件职位的行政区，再关联所有职位来统计总数：

SELECT 
  districts.*, 
  COUNT(positions_to_count.id) AS positions_count 
FROM "districts"
-- 筛选出至少有一个符合名称条件职位的行政区
WHERE EXISTS (
  SELECT 1 
  FROM positions 
  WHERE positions.district_id = districts.id 
    AND positions.name IN ($1, $2)
)
-- 关联该行政区的所有职位，用于统计总数
LEFT JOIN positions AS positions_to_count 
  ON positions_to_count.district_id = districts.id
GROUP BY districts.id 
ORDER BY positions_count DESC, "districts"."name" ASC

方法二：先统计总数再筛选行政区 #

这个方法先通过子查询统计每个行政区的总职位数，再筛选出符合条件的行政区：

SELECT 
  d.*, 
  pc.positions_count
FROM districts d
-- 关联预先统计好的各行政区总职位数
JOIN (
  SELECT district_id, COUNT(id) AS positions_count 
  FROM positions 
  GROUP BY district_id
) pc ON d.id = pc.district_id
-- 筛选出存在符合名称条件职位的行政区
WHERE EXISTS (
  SELECT 1 
  FROM positions p
  WHERE p.district_id = d.id 
    AND p.name IN ($1, $2)
)
ORDER BY pc.positions_count DESC, d.name ASC

为什么你的原SQL不行？ #

你的两次关联写法里，WHERE ("positions"."name" IN ($1, $2))会把所有没有符合条件职位的行政区过滤掉，同时因为关联条件写反了，统计的范围始终被限制在符合筛选条件的职位中，自然无法得到总数量。

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