81,092
社区成员
发帖
与我相关
我的任务
分享
select
'stat_time' = '2011-04',
'fixed_net' = count(distinct case certify_user_type when 1 then user_id else null end ) ,
'school_net' = count(distinct case certify_user_type when 3 then user_id else null end ),
'differ_net' = count(distinct case certify_user_type when 4 then user_id else null end ),
'wlan_net' = count(distinct case certify_user_type when 5 then user_id else null end ),
'other_net' = count(distinct case certify_user_type when 0 then user_id else null end ),
'oneType_user' = count(distinct case when user_type_id=1 and certify_user_type=2 then user_id else null end ) ,
'twoType_user' = count(distinct case when user_type_id=2 and certify_user_type=2 then user_id else null end ),
'threeType_user' = count(distinct case when user_type_id=3 and certify_user_type=2 then user_id else null end ),
'other_user' = count(distinct case when user_type_id=99 and certify_user_type=2 then user_id else null end ),
'local_rom' = count(distinct case romflag when 0 then user_id else null end ),
'out_rom' = count(distinct case romflag when 1 then user_id else null end ),
'in_rom' = count(distinct case romflag when 2 then user_id else null end ),
'nation_out_rom' = count(distinct case romflag when 3 then user_id else null end ),
'nation_in_rom' = count(distinct case romflag when 5 then user_id else null end )
from wlan_auth_user_vlan_2011_04 a
select
'stat_time' = '2011-04',
'fixed_net' = sum(case certify_user_type when 1 then 1 else 0 end ) ,
'school_net' = sum(case certify_user_type when 3 then 1 else 0 end ),
'differ_net' = sum(case certify_user_type when 4 then 1 else 0 end ),
'wlan_net' = sum(case certify_user_type when 5 then 1 else 0 end ),
'other_net' = sum(case certify_user_type when 0 then 1 else 0 end ),
'oneType_user' = sum(case when user_type_id=1 and certify_user_type=2 then 1 else 0 end ) ,
'twoType_user' = sum(case when user_type_id=2 and certify_user_type=2 then 1 else 0 end ),
'threeType_user' = sum(case when user_type_id=3 and certify_user_type=2 then 1 else 0 end ),
'other_user' = sum(case when user_type_id=99 and certify_user_type=2 then 1 else 0 end ),
'local_rom' = sum(case romflag when 0 then 1 else 0 end ),
'out_rom' = sum(case romflag when 1 then 1 else 0 end ),
'in_rom' = sum(case romflag when 2 then 1 else 0 end ),
'nation_out_rom' = sum(case romflag when 3 then 1 else 0 end ),
'nation_in_rom' = sum(case romflag when 5 then 1 else 0 end )
from (select distinct user_id, user_type_id, certify_user_type, romflag
from wlan_auth_user_vlan_2011_04) a --把distinct统一在一起而不是每个都去distinct
select '2011-04' stat_time,
case certify_user_type when 1 then sum(cnt) else 0 end fixed_net,
case certify_user_type when 3 then sum(cnt) else 0 end school_net,
case certify_user_type when 4 then sum(cnt) else 0 end differ_net,
case certify_user_type when 5 then sum(cnt) else 0 end wlan_net,
case certify_user_type when 0 then sum(cnt) else 0 end other_net,
case when user_type_id=1 and certify_user_type=2 then sum(cnt) else 0 end oneType_user
......
from
(
select certify_user_type,user_type_id,romflag,count(distinct userid) cnt
from wlan_auth_user_vlan_2011_04 a group by certify_user_type,user_type_id,romflag
) t
SELECT SUM(MID(t.birthDate,1,2)) FROM test_user t GROUP BY t.age