杜郎俊赏

mysql 列值转换和统计

将 user_sample

uid201608201609201610201611
张三iPhonemimiGoogle
李四mimiGoogleGoogle
王五GooglemiiPhoneiPhone

转换为 phone_result

uidiPhonemiGoogle
张三121
李四022
王五211

sql 如下:

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;

标签:笔记
日期:2017-09-13