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

使用Conditional Aggregate进行此操作

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