mysql 计算 where 子句中给出的所有 id 并计算剩余的其余部分

Question

我想要一个列表，我可以在其中获取我在 where 子句中给定 ID 的计数，而剩余 ID 的计数是我的查询:

Select cat.name AS name, COUNT(cat.name) AS total
FROM MyAppBundle:Content c INNER JOIN c.category cat
WHERE cat.id IN(11,7,16,1,12,13)
GROUP BY cat.name;

但不知道如何得到剩余的总和，有人可以帮忙吗？

Answer 1

您可以结合使用 SUM 和 CASE 来获得匹配和不匹配的计数:

SELECT cat.name AS name,
    SUM(CASE WHEN ID IN (11,7,16,1,12,13) THEN 1 ELSE 0) AS countMatch,
    SUM(CASE WHEN ID NOT IN (11,7,16,1,12,13) THEN 1 ELSE 0) AS countNotMatch
FROM MyAppBundle:Content c INNER JOIN c.category cat
GROUP BY cat.name