php - count 子句输出数据库中的所有记录 - mysql

Question

我一直在尝试不同的组合，但我似乎无法让它发挥作用。我有内部联接表，我想计算记录中找到的 QA 问题的数量，并输出那些仅包含 QA 问题的记录，我该怎么做？

SELECT d.department, m.mo_number, m.part_number, c.category,
COUNT(CASE WHEN c.category = 'QA ISSUE' THEN category END) as qa_issue,
SUM(CASE WHEN c.category = 'QA ISSUE' THEN time_spent END) as time_spent
FROM       master as m
INNER JOIN category as c ON c.cat_id = m.cat_id
INNER JOIN department as d ON d.dept_id = m.dept_id
WHERE m.date_created >= DATE_SUB(now(), INTERVAL 50 DAY) AND
d.department = 'Electronics'
GROUP BY m.mo_number
ORDER BY 1

Answer 1

要按聚合过滤结果，请使用 GROUP BY 子句之后出现的 HAVING 子句。请注意，这不能替代 WHERE 子句(它选择要聚合的行)。

SELECT
      d.department
    , m.mo_number
    , m.part_number
    , c.category
    , COUNT(*) AS qa_issue
    , SUM(time_spent) AS time_spent
FROM master AS m
      INNER JOIN category AS c ON c.cat_id = m.cat_id
      INNER JOIN department AS d ON d.dept_id = m.dept_id
WHERE m.date_created >= DATE_SUB(now(), INTERVAL 50 DAY)
      AND d.department = 'Electronics'
      AND c.category = 'QA ISSUE'
GROUP BY
      d.department
    , m.mo_number
    , m.part_number
    , c.category

HAVING COUNT(*) = 1

ORDER BY
      d.department

我还向 where 子句添加了一个条件，并将所有非聚合列添加到 GROUP BY 子句中 - 我建议您始终这样做。