mysql - 优化 mysql 查询以选择值

Question

我有一个包含以下列名称的表:audit_name、audit_choice 和“slno”作为自动递增主键并且不使用输出数据。

audit_name 可以重复，audit_choice 值仅限于某些值，如“通过”、“失败”。

我的查询预期输出是，获取每个“Audit_name”的“通过”计数和“失败”计数。

这是我尝试过的查询，但执行大约 1,000 条记录需要大约 3-4 分钟。

SELECT audit_name,
  (SELECT COUNT(*) AS Passed FROM audit AS p1
     WHERE p1.audit_name=p2.audit_name AND p1.audit_choice="Passed") AS Passed,
  (SELECT COUNT(*) AS Failed FROM audit AS p3
     WHERE p3.audit_name=p2.audit_name AND p2.audit_choice="Failed") AS Failed
  FROM audit AS p2
  GROUP BY audit_name

请建议我如何优化查询。

Answer 1

SELECT  audit_name,
        SUM(audit_choice = 'Passed') AS passed,
        SUM(audit_choice = 'Failed') AS failed
FROM    audit
GROUP BY
        audit_name