MySQL将行拆分为2列并按分组，这可能吗

Question

我得到了一个包含2列的表app和grupo_id

select count(*) total, app, grupo_id from devices d group by app, grupo_id ;

输出：

应用程序71420451211130<1>

Answer 1

使用条件聚合：

SELECT SUM(app = 1) app_on, 
SUM(app = 0) app_off,
grupo_id
FROM devices 
GROUP BY grupo_id;

或：

SELECT SUM(app) app_on, 
SUM(NOT app) app_off,
grupo_id
FROM devices 
GROUP BY grupo_id;

Answer 2

我认为您正在寻找条件聚合

基于您的第一个结果

select grupo_id,
max(case when app = 1 then total else 0 end) appopen,
max(case when app = 0 then total else 0 end) appclosed
from t
group by grupo_id;
+----------+---------+-----------+
| grupo_id | appopen | appclosed |
+----------+---------+-----------+
|        1 |       1 |         3 |
|        2 |       5 |         0 |
|        4 |       7 |         2 |
+----------+---------+-----------+
3 rows in set (0.00 sec)

可以在原始查询中使用此主体。。