mysql - sql查询更改维度表

Question

我有一个表:这与用户登录频率的日志相同

╔════════════╦═══════╦═════════════╗
║ month-year ║ user  ║ count_login ║
╠════════════╬═══════╬═════════════╣
║ 01-2016    ║ admin ║          12 ║
║ 01-2016    ║ user1 ║          10 ║
║ 02-2016    ║ admin ║           5 ║
║ 03-2016    ║ admin ║          15 ║
╚════════════╩═══════╩═════════════╝

可能会更改为

╔════════════╦═══════╦═══════╗
║ month-year ║ admin ║ user1 ║
╠════════════╬═══════╬═══════╣
║ 01-2016    ║    12 ║    10 ║
║ 02-2016    ║     5 ║     0 ║
║ 03-2016    ║    15 ║     0 ║
╚════════════╩═══════╩═══════╝

如果可能的话，我可以使用什么查询？

Answer 1

使用条件聚合来执行此操作

select `month-year`, 
       sum(case when user = 'admin' then count_login else 0 end) as admin,
       sum(case when user = 'user1' then count_login else 0 end) as user1
From yourtable
Group by `month-year`

检查动态旋转的答案:MySQL pivot row into dynamic number of columns