SQL查询如何仅对COUNT统计字段应用WHERE条件不影响其他字段

核心问题分析 #

你当前把资源过滤条件写在全局WHERE子句中，会将FULL JOIN生成的无匹配资源的房间空行直接过滤，同时你用FULL JOIN无法保证优先输出全量房间列表，才会导致非预期结果。

修正后的SQL语句 #

SELECT
  l.name AS room_name,
  l.room_code,
  NULLIF(COUNT(res.aid), 0) AS nofixeditems,
  NULLIF(COUNT(m.asset_id), 0) AS noinmaintenance,
  rc.actiondate AS last_checked,
  CONCAT(u.firstname, ' ', u.surname) AS checked_by
FROM
  locations l
  LEFT JOIN resource_locations rl ON rl.location_id_fixed = l.locationid::bigint
  LEFT JOIN resources res ON res.aid = rl.resource_id 
    AND res.archived IS NOT TRUE 
    AND res.hide_from_room_checklist IS NOT TRUE
  LEFT JOIN room_checklist rc ON rc.assetid = res.aid AND rc.locationid = l.locationid
  LEFT JOIN users u ON u.uid = rc.actionuserid
  LEFT JOIN maintenance.maintenance_log m ON res.aid = m.asset_id
GROUP BY
  l.name,
  l.room_code,
  rc.actiondate,
  checked_by

改动说明 #

• 调整连接逻辑：将locations设为主表，用LEFT JOIN关联其余所有表，确保全量房间列表完整输出，不会因为无关联数据被过滤
• 筛选条件迁移：原来全局WHERE中的资源过滤、关联匹配条件全部移到对应表的JOIN ON子句中，仅过滤关联的子表数据，不会影响主表的房间输出
• 统计逻辑适配：用NULLIF包裹COUNT结果，无符合统计条件的数据时自动返回NULL而非0，匹配你的需求

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