工作中用的两条查询语句,LEFT JOIN多张表的

----4G手机 left join 三张表
select a.IMSI,
Mobileid IMEI,
substr(Mobileid，0, 6) 终端类型,
上行流量,
空口时间,
业务时长,
IP包数,
下行流量,
并发服务数
from ((select IMSI,
sum(userullen) / 1024 / 1024 上行流量,
sum(userdllen) / 1024 / 1024 下行流量,
sum(timelen) 业务时长,
sum(packetnumber) IP包数,
sum(fid) 并发服务数
from apptypeinfo
where datetime >=
to_date('2013-9-2 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
and datetime <
to_date('2013-9-3 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
and 1 = 1 -- 基站查询条件.
and 2 = 2 -- 业务查询条件.
and vlanid = 0
and userid is not null
and rownum <= 10
group by imsi
order by 上行流量 desc) a
left join
(select GTPV2MESSAGE.Mobileid, GTPV2MESSAGE.imsi from GTPV2MESSAGE
where datetime >=
to_date('2013-9-2 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
and datetime <
to_date('2013-9-3 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
) b
on a.imsi = b.imsi
left join
(select sum(time_um.delaytime) 空口时间, imsi
from time_um
where bdatetime >=
to_date('2013-9-2 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
and edatetime <
to_date('2013-9-3 0:00:00', 'YYYY-MM-DD HH24:MI:SS')
group by time_um.imsi) c on a.imsi = c.imsi)
order by 上行流量 desc


----mifi终端 left join 2张表
select 所属MIFI,
MAC地址,
终端类型,
上行流量,
空口时间,
业务时长,
IP包数,
下行流量,
并发服务数
from ((select MIFIIP 所属MIFI,
userid MAC地址,
mactype 终端类型,
sum(userullen) / 1024 / 1024 上行流量,
sum(userdllen) / 1024 / 1024 下行流量,
sum(timelen) 业务时长,
sum(packetnumber) IP包数,
sum(fid) 并发服务数,
imsi
from apptypeinfo
where datetime >= to_date('2013-9-2 0:00:00','YYYY-MM-DD HH24:MI:SS') and datetime <to_date('2013-9-3 0:00:00','YYYY-MM-DD HH24:MI:SS')
and 1 = 1 -- 基站查询条件.
and 2 = 2 -- 业务查询条件.
and vlanid != 0
and userid is not null
and rownum <= 10
group by imsi, MIFIIP, userid, mactype
order by 上行流量 desc
) a left join
(select sum(time_um.delaytime) 空口时间, imsi
from time_um
where bdatetime >= to_date('2013-9-2 0:00:00','YYYY-MM-DD HH24:MI:SS') and edatetime <to_date('2013-9-3 0:00:00','YYYY-MM-DD HH24:MI:SS')
group by time_um.imsi) b on a.imsi = b.imsi) order by 上行流量 desc