工作中用的两条查询语句,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