SQL计数偏差为2

Question

我有这条sql语句，它列出了所有系统的id en名称，这些系统具有特定的报表条目“警告”，并且没有出现在列出该警告的解释的表中。

SELECT
  systems.id,
  systems.name
FROM
  systems
  LEFT JOIN reports ON (reports.system_id = systems.id)
  LEFT JOIN explanation_system_standards ON (reports.standard_id = explanation_system_standards.standard_id)
WHERE
  reports.status = 'warning' AND reports.datum = (SELECT MAX(datum) FROM reports) AND explanation_system_standards.dismissed_at IS NULL AND NOT EXISTS
  (SELECT reports.standard_id
  FROM explanation_system_standards
  WHERE reports.standard_id = explanation_system_standards.standard_id AND reports.system_id = explanation_system_standards.system_id)
GROUP BY systems.id

此查询返回准确的输出。在这种情况下，没有出现在explanation_system_standards表中的状态“警告”的报表条目有6个。

psql -d bidlqa_development -f fl.sql | grep testserver.com

 396 | testserver.com
 396 | testserver.com
 396 | testserver.com
 396 | testserver.com
 396 | testserver.com
 396 | testserver.com

但是，当我试图通过计数添加发生的次数时，每个系统的结果都是+2。

SELECT
  systems.id,
  systems.name,
  COUNT (systems.id)
FROM
  systems
  LEFT JOIN reports ON (reports.system_id = systems.id)
  LEFT JOIN explanation_system_standards ON (reports.standard_id = explanation_system_standards.standard_id)
WHERE
  reports.status = 'warning' AND reports.datum = (SELECT MAX(datum) FROM reports) AND explanation_system_standards.dismissed_at IS NULL AND NOT EXISTS
  (SELECT reports.standard_id
  FROM explanation_system_standards
  WHERE reports.standard_id = explanation_system_standards.standard_id AND reports.system_id = explanation_system_standards.system_id)
GROUP BY systems.id

psql -d bidlqa_development -f fl.sql | grep testserver.com
 396 | testserver.com            |     8

它添加2的原因是由于explanation_system_standards.standard_id，表中还有两个使用相同关联的系统。有谁知道我怎样才能更正这个查询，让我根据每个系统的基数来计算？

编辑：

我修复了它，而不是在system.id上计数，我需要一个计数(不同的reports.standard_id)

Answer 1

从你的数量上看是不同的；

SELECT
      systems.id,
      systems.name,
      COUNT (distinct systems.id)
    FROM
      systems
      LEFT JOIN reports ON (reports.system_id = systems.id)
      LEFT JOIN explanation_system_standards ON (reports.standard_id = explanation_system_standards.standard_id)
    WHERE
      reports.status = 'warning' AND reports.datum = (SELECT MAX(datum) FROM reports) AND explanation_system_standards.dismissed_at IS NULL AND NOT EXISTS
      (SELECT reports.standard_id
      FROM explanation_system_standards
      WHERE reports.standard_id = explanation_system_standards.standard_id AND reports.system_id = explanation_system_standards.system_id)
    GROUP BY systems.id