对与MySQL中的多个条件匹配的行进行计数

如果状态只能是 active 或 inactive 我建议采用不同的方法:

SELECT
    sum(if(status_a='active',1,0)) AS status_a_active,
    sum(if(status_a='inactive',1,0)) AS status_a_inactive,
    sum(if(status_b='active',1,0)) AS status_b_active,
    sum(if(status_b='inactive',1,0)) AS status_b_inactive
FROM table

否则,您需要使用 UNION 方法,但我会做得有点不同。首先,你可以使用 UNION ALL ,因为您不需要删除结果中的重复项。我也会用 GROUP BY 只有一次这样:

SELECT status_group, count(id)
FROM (
    SELECT CONCAT('status_a.', status_a) AS status_group, id FROM table
    UNION ALL
    SELECT CONCAT('status_b.', status_b) AS status_group, id FROM table
) a
GROUP BY status_group

我有一个使用工会的解决方案。如图所示:

SELECT 'status_a.active' AS status_group, COUNT(*) AS count FROM `test` WHERE status_a = 'active'
UNION
SELECT 'status_a.inactive' AS status_group, COUNT(*) AS count FROM `test` WHERE status_a = 'inactive'
UNION
SELECT 'status_b.active' AS status_group, COUNT(*) AS count FROM `test` WHERE status_b = 'active'
UNION
SELECT 'status_b.inactive' AS status_group, COUNT(*) AS count FROM `test` WHERE status_b = 'inactive'

基本上,它查询每个条件的状态“A”或状态“B”是否处于活动状态。我们得到了四个这样的查询,我们将联合应用于所有查询。

我想,我必须在不久前移动我的评论,这也是一个比硬件更短的解决方案。

SELECT CONCAT('status_a.', status_a) AS stat, COUNT(id) FROM base GROUP BY stat 
UNION
SELECT CONCAT('status_b.', status_b) AS stat, COUNT(id) FROM base GROUP BY stat