SQL查询

1 查询总的  手机号，特服号码，发送时间，短信内容，业务类型，业务代码，状态报告，状态报告接收时间

select decode(t.gatewayid,1,'12306移动',2, ' 12306联通',3, '12306电信', 4,'95306移动',5,' 95306联通',6,'95306电信',17,'10690862移动',18,' 10690862联通',19,'10690862电信') as 运营商网关,
       t.desmsisdn as 手机号,t.sendcode as 特服号码,to_char(t.sendtime, 'yyyy-mm-dd hh24:mi:ss') as 发送时间,t.message as 短信内容,b.businessname as 业务类型,t.businesscode as 业务代码,
       (case
         when r.statusreport = 'DELIVRD' then
          '成功'
         when r.statusreport != 'DELIVRD' then
          '失败:' || r.statusreport
       end) as 状态报告,to_char(r.receivetime, 'yyyy-mm-dd hh24:mi:ss') as 状态报告接收时间
  from (select * from ms_mtmessage_a partition(part_20180417)
        union all
        select * from ms_mtmessage_b partition(part_20180417)) t,
       (select * from ms_statusreport_a partition(part_20180417)
        union all
        select * from ms_statusreport_b partition(part_20180417)) r,ms_businesscode b
 where t.businesscode = b.businesscode
   and t.mtmessageid = r.mtmessageid

   and t.desmsisdn = '186156535**'

2 以时间秒统计发送个数  统计MT ms_mtmessage_a，ms_mtmessage_b

select to_char(sendtime,'yyyy-mm-dd hh24:mi:ss'),count(*) from (select * from ms_mtmessage_a partition(part_20180417) union all select * from ms_mtmessage_b partition(part_20180417))

group by to_char(sendtime,'yyyy-mm-dd hh24:mi:ss')

order by count(*) desc

3 统计当月移动联通电信 发送量接收量

select sum(sendsuctotal),sum(receivetotal) ,round(sum(receivetotal)/sum(sendsuctotal)*100)||'%'
from ms_statdays where statdate>=20180201 and statdate<=20180228
  and gatewayid in (17,18,19) order by statdate asc

4 在两个表ms_mtmessage_b，ms_statusreport_b中查询手机号
select mt.mtmessageid,   
       mt.gatewayid,     
       mt.sendcode,      
       mt.message,  
       mt.desmsisdn,     
       sr.statusreport   
  from ms_mtmessage_b partition(part_20180404) mt,     ms_statusreport_b partition(part_20180404) sr
 where mt.mtmessageid = sr.mtmessageid
   and mt.desmsisdn = '136813504**'






查询两个表ms_mtmessage_a，ms_statusreport_a中状态报告
select mt.mtmessageid,
       mt.gatewayid,
       mt.message,
       mt.desmsisdn,
       sr.statusreport
  from ms_mtmessage_a partition(part_20180404) mt
 inner join ms_statusreport_a partition(part_20180404) sr
    on mt.mtmessageid = sr.mtmessageid
 where mt.desmsisdn = '136813504**'




单表查询手机号验证码 ms_momessage_a
select mo.GATEWAYID,mo.SRCMSISDN,mo.SENDCODE,mo.MESSAGE 
from ms_momessage_a partition(part_20180410)mo
where mo.SRCMSISDN = '189114071**'




两表查询手机号验证码去掉重复ms_momessage_a  ms_momessage_b
select distinct mo.GATEWAYID,mo.SRCMSISDN,mo.SENDCODE,mo.MESSAGE 
from ms_momessage_a partition(part_20180410)mo ,ms_momessage_b partition(part_20180410) mb
where  mo.SRCMSISDN = '189114071**'




查询两个表列相同的字段 from ms_momessage_a  ms_momessage_b
select gatewayid 网关ID,
       srcmsisdn 原手机号,
       sendcode  发送号码，messagesize 消息长度,
       message   内容
  from (select *
          from ms_momessage_a partition(part_20180410)
        union all
        select *
          from ms_momessage_b partition(part_20180410)
        )
  where srcmsisdn = '189114071**'