mysql 列值与行值转换和统计

drop table if exists tmp;
drop table if exists phone_result;

create table tmp as select uid, `201608` as brand, `201608` as month from `user_sample`;
insert into tmp select uid, `201609` as brand, `201609` as month from `user_sample`;
insert into tmp select uid, `201610` as brand, `201610` as month from `user_sample`;
insert into tmp select uid, `201611` as brand, `201611` as month from `user_sample`;

create table phone_result as select distinct uid,
count(case brand when 'iPhone' then 1 end) as 'iPhone',
count(case brand when 'mi' then 1 end) as 'mi',
count(case brand when 'Google' then 1 end) as 'Google' from tmp group by uid;

drop table tmp;