sql分组统计

原始的数据结构是这样的&＃xff1a;

这是一个信息表&＃xff0c;记录下每个app每天对应什么等级

现在需求是&＃xff1a;统计每天每个等级有多少个app&＃xff1f;

实现的sql如下&＃xff1a;

select count(*) as num,level,date from t_app_level_status group by date,level

结果如下&＃xff1a;

这样有个问题&＃xff0c;不方便在程序下显示&＃xff0c;如果需要把这里的行结构变成列结构&＃xff0c;使用下面的sql&＃xff1a;

select &＃96;date&＃96;, MAX(CASE level WHEN 1 THEN num ELSE 0 END ) as "1", MAX(CASE level WHEN 2 THEN num ELSE 0 END ) as "2", MAX(CASE level WHEN 3 THEN num ELSE 0 END ) as "3" from (select count(*) as num,level,date from t_app_level_status group by date,level) temp group by &＃96;date&＃96;

结果&＃xff1a;