MySQL中不带UNION和GROUP BY的COUNT的SQL枢轴输出结果集

Question

我正在处理几百万行，所以我不会使用UNION来显示我想要的查询。出于设计目的，我需要以某种方式返回此查询以自动上传饼状图。

查询:

SELECT
COUNT(IF( b IS NULL, id , NULL)) AS 'not_assigned',
COUNT(IF(b IS NOT NULL, id, NULL)) AS 'assigned'
FROM table
WHERE 
OverType = "abc"
AND Type = "def"
AND Sub_Type = "ghi"
AND Date BETWEEN "2022-12-01" AND "2022-12-25"
AND Client LIKE '%john%';

结果集:

not_assigned    assigned
1000            500

所以我想把输出转换成这样:

Count
not_assigned    1000
assigned        500

MySQL 5.0有什么建议吗?

Answer 1

您可以通过CASE表达式进行聚合:

SELECT
CASE WHEN b IS NULL THEN 'not_assigned' ELSE 'assigned' END AS category,
COUNT(*) AS cnt
FROM yourTable
WHERE 
OverType = 'abc' AND
Type = 'def'
Sub_Type = 'ghi' AND
Date BETWEEN '2022-12-01' AND '2022-12-25' AND
Client LIKE '%john%'
GROUP BY 1;