» SQL求助,,听说微民网的码农都是特别厉害的。
select
a.vv,a.uv,beijing ,shanghai ,guangzhou ,Shenzhen,year1 ,year2 ,year3 ,year4 ,year5 ,year6,male,female
from (
SELECT sum(a.play_cnt) vv,count(DISTINCT a.uin)uv,
count( case when city_id=-1 then 1 else NULL end) beijing,
count( case when city_id=-12 then 1 else NULL end) shanghai,
count( case when city_id=198 then 1 else NULL end) guangzhou,
count( case when city_id=199 then 1 else NULL end) shenzhen,
count( case when age<=17 then 1 else NULL end) year1,
count( case when age>=18 and age<=22 then 1 else NULL end) year2,
count( case when age>=23 and age<=25 then 1 else NULL end) year3,
count( case when age>=26 and age<=30 then 1 else NULL end) year4,
count( case when age>=31 and age<=40 then 1 else NULL end) year5,
count( case when age>=41 then 1 else NULL end) year6,
count( case when gender=1 then 1 else NULL end) male,
count( case when gender=2 then 1 else NULL end) female
from (
select uin,sum(play_cnt)play_cnt,city_id,age,gender from
cover_play
where imp_date>=20130515 and imp_date<=20130603 and id IN (1,2)
and play_cnt>0 and uin>0
group by uin,age,gender,city_id) a
) a
和这条
select city_id,sum(play_cnt) vv,count(DISTINCT uin)uv from
cover_play
where imp_date>=20130515 and imp_date<=20130603 and id IN (1,2)
and play_cnt>0 and uin>0 and city_id IN(-1,-12,198,199)
group by city_id