select fmonth,sum(decode(factionid,1,fcount,0)) as factionid1,sum(decode(factionid,2,fcount,0)) as factionid2 from
(
select fmonth,factionid,count(*) as fcount from
(
select a.*,to_char(ftime,'yyyymm') as fmonth from sm_dzuserinfo_log a,
(
select fmobile,to_char(ftime,'yyyymm') as fmonth,max(ftime) as flasttime from sm_dzuserinfo_log
group by fmobile,to_char(ftime,'yyyymm')
) b
where a.fmobile = b.fmobile and to_char(a.ftime,'yyyymm')=b.fmonth and a.ftime=b.flasttime
)
group by factionid,fmonth
order by fmonth,factionid
)
group by fmonth
(
select fmonth,factionid,count(*) as fcount from
(
select a.*,to_char(ftime,'yyyymm') as fmonth from sm_dzuserinfo_log a,
(
select fmobile,to_char(ftime,'yyyymm') as fmonth,max(ftime) as flasttime from sm_dzuserinfo_log
group by fmobile,to_char(ftime,'yyyymm')
) b
where a.fmobile = b.fmobile and to_char(a.ftime,'yyyymm')=b.fmonth and a.ftime=b.flasttime
)
group by factionid,fmonth
order by fmonth,factionid
)
group by fmonth