mysql - 将两个不同的 WHERE 子句/条件合并为一个查询

Question

我有三个表:users、accounts 和scores。每个查询实际上都给了我想要的结果:

-- This will return all user ids with a count of "calculated" scores
SELECT u.id AS user_id, count(1) AS total FROM scores s
  INNER JOIN accounts a ON s.account_id = a.id
  INNER JOIN user u ON a.user_id = u.id
WHERE s.status = 'CALCULATED'
GROUP BY user_id;

-- This will return all user ids with a count of non-calculated scores
SELECT u.id AS user_id, count(1) AS failures FROM scores s
  INNER JOIN accounts a ON s.account_id = a.id
  INNER JOIN user u ON a.user_id = u.id
WHERE s.status <> 'CALCULATED'
GROUP BY user_id;

但我想返回类似这样的内容:用户 id、总计、失败...全部在一个查询中!

Answer 1

这可以通过条件聚合来完成。 SUM 中的条件根据满足的条件返回 1 或 0。

SELECT u.id AS user_id,
SUM(s.status='CALCULATED'),
SUM(s.status<>'CALCULATED') AS total 
FROM scores s
INNER JOIN accounts a ON s.account_id = a.id
INNER JOIN user u ON a.user_id = u.id
GROUP BY u.id;