本文展示了一个复杂的SQL查询案例，通过多表联接、子查询及分组聚合操作，实现了按月统计不同阵营ID的计数。此查询涉及表连接、条件筛选、字段映射等关键技术点。
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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
