我一直在尝试不同的组合,但我似乎无法让它工作。我有内部连接表,我想计算在记录中找到的 QA ISSUE 的数量,并输出仅包含 QA ISSUE 的记录,我该怎么做?
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
要按聚合过滤结果,请使用 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 子句中 - 我建议您始终这样做。