ORACLE常用sql

生效时间  2016-12-1
失效时间：2026-12-31
新建目标号码群需要重启引擎
1成功0失败
================================
  tb_activity_info_dictionary  活动字典表
  tb_dictionary  字典表可以查品牌 
  activity   活动
  test_activity  活动测试
  activity_register  活动办理
  Tb_Sms_Upload_Province 上行表
  Tb_Sms_down_Province  下行表
  往期上行活动量  放入  Tb_Sms_Upload_Province_bak
================================
建立客户号码群的命名规则是，号码群编码倒数第3个数字代表第几张号码表，比如2就是代表dest_mobile_2，
这样有利于看懂号码存放在哪张号码表
====================================================
--处理速度
select to_char(part_date,'yyyymmdd hh24:mi'),count(*) from activity_register
where upper(input)='SYH' and to_char(part_date,'yyyyMMdd')='20180113'
group by to_char(part_date,'yyyymmdd hh24:mi')
order by to_char(part_date,'yyyymmdd hh24:mi') desc
--上行速度
select to_char(upload_time,'yyyymmdd hh24:mi'),count(*) from Tb_Sms_Upload_Province
where upper(content)='SYH' and to_char(upload_time,'yyyymmdd')='20180121'
group by to_char(upload_time,'yyyymmdd hh24:mi')
order by to_char(upload_time,'yyyymmdd hh24:mi') desc
--监控活动办理量   //0失败  1成功   2办理中
select upper(input),status,count(*) from activity_register
where ( upper(input) = 'SLL')
and to_char(PART_DATE,'yyyyMMdd')='20180201'
group by upper(input),status  order by upper(input)
--短厅失败
select remark,count(*) from activity_register a
where a.activity_id='4370CDDD-6BDF-4C12-8A22-5B4DCDF85B05' and a.status='0'
and to_char(a.part_Date,'yyyyMMdd')='20180201' and remark not like '执行代码块异常%' and remark not like '回复超时%' 
group by remark

触发量  就是总量
=====================================================
周四导流量数据          
select mobile,input,status,PART_DATE,ext1,remark from activity_register
where ( upper(input) like 'THB0%'  )
and to_char(PART_DATE,'yyyyMM')='201803'
order by PART_DATE asc
=======================================================
    
    



===========================================================
某个指令上行量   
select count(0) from Tb_Sms_Upload_Province where upload_time>trunc(sysdate) and content='SLL'

某指令某个时间段上行
select to_char(upload_time,'yyyymmdd hh24'),count(*) from Tb_Sms_Upload_Province
where upper(content)='SLL' and to_char(upload_time,'yyyymmdd')='20180201'
group by to_char(upload_time,'yyyymmdd hh24')
order by to_char(upload_time,'yyyymmdd hh24') desc

查看某指令每分钟上行
select to_char(upload_time,'yyyymmdd hh24:mi'),count(*) from Tb_Sms_Upload_Province
where upper(content)='SLL' and to_char(upload_time,'yyyymmdd')='20180201'
group by to_char(upload_time,'yyyymmdd hh24:mi')
order by to_char(upload_time,'yyyymmdd hh24:mi') desc   降序
  
===========================================================
ecop错误查询   productorder   productorder    ccqrysubsprods   这三个是错误

select interface_name,respcode,count(*) from hyecop.tb_ecop_interface_log
where to_char(create_time,'yyyyMMdd')='20180201'
group by interface_name,respcode

查询ecop报文
select * from hyecop.tb_ecop_interface_log
where interface_name='productorder' and respcode='1000050005' and to_char(create_time,'yyyyMM')='201801'
order by create_time desc

create_time>trunc(sysdate)

respcode 0是正常    98开头的是正常失败返回   111和1000开头的是异常返回

查看190返回的报文
select * from hyecop.tb_ecop_interface_log where resip='10.252.36.190' and create_time>sysdate-1/2

查看是否同单提交
select * from hyecop.tb_ecop_interface_log where resip='10.252.39.205 'and operator_id='P0KJHYZQ' and request_xml like '%JYPT762.180110817643.0%' and create_time>sysdate-1


===============================================================
===============================================================
清空零时表     新建号码群的时候需要重启引擎
delete from mobiletemp3

导入目标号码  

每次你整理好号码数据文本后，放进这个文件夹，修改下文本‘control’里的

打开CMD输入
Sqlldr hysms/hysms@hysms control =C:\Guide\control.txt
就行了



清空目标号码表
delete from DEST_MOBILE_2 s where s.group_id ='201702221441000201';


插入目标号码 
alter session enable parallel dml;
insert into DEST_MOBILE_2 nologging (id,MOBILE,GROUP_ID,MOBILE_LAST) 
select seq_temp_id.nextval, a.mobile, '201702221441000201', substr(a.mobile,10,11) from mobiletemp3 a ;
commit;   
 
============================================================
某活动某号码累计办理情况 
select mobile,to_char(part_date,'yyyyMMdd'),count(*) from activity_register
where activity_id='0D1EA06C-F3A9-42D2-BF01-AC12B3E0EE62' and status='1'
group by mobile,to_char(part_date,'yyyyMMdd')
having count(*)>1
order by mobile,to_char(part_date,'yyyyMMdd')
=============================================================
THB一月情况  流量室黄文艺
select mobile,input,status,PART_DATE,ext1,remark from activity_register
where ( upper(input) like 'THB0%')
and to_char(PART_DATE,'yyyyMM')='201801'
--and part_date >to_date('20180120','yyyyMMdd')
--and ext1 like '预登记%' and status='1'
order by PART_DATE asc

===========================================================
  插入活动字典表
insert into tb_activity_info_dictionary(id,name,info_s,info_i)
values(SYS_GUID(),'SLL','2018013110~2018013124',50000)
===============================================================
新建目标号码群
select * from DEST_MOBILE_GROUP
select * from DEST_MOBILE_1
--1、先在页面建立新的目标号码群

--2、根据目标号群的ID，修改目标号码群的ID和目标群的表编号
update DEST_MOBILE_GROUP set ID='201802071729000213',MOBILE_TABLE_NAME='2' where ID='201802071729000211';



===================================================================

操作员权限不足    需要在CRM侧，对产品编码进行渠道授权   


===================================================================
cjb办理情况 

select mobile,input,status,PART_DATE,ext1,remark from activity_register
where ( upper(input) like 'CJB') order by PART_DATE asc

删除测试数据
delete from activity_register where ( upper(input)like'CJB') and to_char(PART_DATE,'yyyymmdd')='20180208' 

=====================================================================
查看数据库死锁   
select sess.sid,   
    sess.serial#,  
    lo.oracle_username,   
    lo.os_user_name,   
    ao.object_name,   
    lo.locked_mode,  
        SESS.machine  
    from v$locked_object lo,   
    dba_objects ao,   
    v$session sess   
where ao.object_id = lo.object_id and lo.session_id = sess.sid;   
=====================================================================
 select
d.activity_name 活动名称,
b.b1 量
from 
(select distinct activity_id from ACTIVITY_REGISTER) a,
(select distinct(ACTIVITY.id),count(ACTIVITY.id) b1 
from ACTIVITY_REGISTER,ACTIVITY where 
ACTIVITY_REGISTER.ACTIVITY_ID=ACTIVITY.ID 
and Activity_Register.finish_date > to_date('20180315','YYYYMMDD')--可修改时间
group by ACTIVITY.id) b,
ACTIVITY d
where b.id=a.activity_id 
and a.activity_id=d.id
order by b.b1 desc
================================================================
编辑某条信息
 select a.* ,a.rowid from activity_register a  where mobile='13650697614'


===============================================================
查询目标号码 
select * from DEST_MOBILE_2 s where s.group_id ='201702221441000208' and mobile='17820221414'
================================================================
  办理详细信息加品牌
  select ar.mobile,td.description,ar.input,ar.status,ar.PART_DATE,ar.ext1,ar.remark from activity_register ar,tb_dictionary td  where ar.brand_child=td.code
and ( upper(input) like 'SYH')
order by PART_DATE DESC


===========================================================
  统计办理成功多少  失败多少  
select upper(input),status,count(*) from activity_register
where ( upper(input) = 'SLL')
and to_char(PART_DATE,'yyyyMMdd')>'20180327'
group by upper(input),status  order by upper(input)

================================================
几点几分后的数据
to_char(PART_DATE,'yyyymmdd hh24:mi')>'20180401 13:35' 

=================================================
 活动字典
  insert into tb_activity_info_dictionary(id,name,info_s,info_i)
values(SYS_GUID(),'HSJ58','2018041010~2018063024',50000)