select d.accept_id 工单流水,d.accept_no 编号,d.handle_date 归档时间,l.create_date 评价时间,l.trans_cmd 评价内容 from (
select n.accept_id,n.accept_no,n.handle_date, min(m.create_date) as mdate from
(
--------------上行内容
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
--------------
) m
inner join
(
--------------归档工单
select a.accept_id,a.accept_no,b.handle_date from
(select * from as_work_accept_200903 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200902 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200901 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
) a inner join
(select * from as_work_process_200903 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200902 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200901 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
) b
on a.accept_id=b.accept_id
and a.accept_no=a.service_no
and a.accept_no like '1%'
and a.accept_no>'106'
--------------
) n
on
n.handle_date<=m.create_date
and n.accept_no=m.phone_id
group by n.accept_id,n.accept_no,n.handle_date) d
inner join
(
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd.create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
) l
on
d.mdate=l.create_date
and d.accept_no=l.phone_id
order by d.handle_date
select n.accept_id,n.accept_no,n.handle_date, min(m.create_date) as mdate from
(
--------------上行内容
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
--------------
) m
inner join
(
--------------归档工单
select a.accept_id,a.accept_no,b.handle_date from
(select * from as_work_accept_200903 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200902 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200901 where accept_type='10'and appeal_type<>'003030'and cust_type in('0','1','2','4','5')
) a inner join
(select * from as_work_process_200903 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200902 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200901 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
) b
on a.accept_id=b.accept_id
and a.accept_no=a.service_no
and a.accept_no like '1%'
and a.accept_no>'106'
--------------
) n
on
n.handle_date<=m.create_date
and n.accept_no=m.phone_id
group by n.accept_id,n.accept_no,n.handle_date) d
inner join
(
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd.create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
) l
on
d.mdate=l.create_date
and d.accept_no=l.phone_id
order by d.handle_date
解决方案 »
- 大数据量表关联查询 性能如何优化 请求帮忙!
- PLSQL编码中处理特殊字符的问题(望个位大大指教)
- grant授权问题
- ora-01861的错误
- 簡單問題:Synonyms
- 我的数据库需要恢复,急!
- 如何在sql中得到服务器的ip地址?
- odbc连接对话框在程序运行过程中的出现 是网络不稳定所谓的闪断的原因吗 哪位碰到过类似问题 谢谢excepiton 错误代码为 im006
- 通过odbc列举oracle中的数据库对象,是不是这样的呀???在线等待!!!
- SQL 行转列怎么写?
- NO_DATA_FOUND.Exception 如何触发?
- Oracle sqldeveloper 中的法语(如Peña)导出到csv的时候显示为“Pe帽a”, 如何让它直接显示成Peña ?
select n.accept_id,n.accept_no,n.handle_date, min(m.create_date) as mdate from
(
--------------上行内容
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
--------------
) m
inner join
(
--------------归档工单
select a.accept_id,a.accept_no,b.handle_date from
(select * from as_work_accept_200903 where accept_type='10'and appeal_type <>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200902 where accept_type='10'and appeal_type <>'003030'and cust_type in('0','1','2','4','5')
union all
select * from as_work_accept_200901 where accept_type='10'and appeal_type <>'003030'and cust_type in('0','1','2','4','5')
) a inner join
(select * from as_work_process_200903 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200902 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
union all
select * from as_work_process_200901 where deal_ring='90'and accept_type='10'
and handle_date between to_date('20090330000000','yyyymmddhh24miss') and to_date('20090330235959','yyyymmddhh24miss')
) b
on a.accept_id=b.accept_id
and a.accept_no=a.service_no
and a.accept_no like '1%'
and a.accept_no>'106'
--------------
) n
on
n.handle_date <=m.create_date
and n.accept_no=m.phone_id
group by n.accept_id,n.accept_no,n.handle_date ) d
inner join
(
select phone_id,trans_cmd,create_date from kf_sms_cmd_receive_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
union all
select phone_id,trans_cmd.create_date from kf_sms_cmd_receive_his_200903 where create_date
between to_date('20090330000000','yyyymmddhh24miss')
and to_date('20090330235959','yyyymmddhh24miss')
and trans_cmd in('g','G','G''m','u','j','M','U','J','J','M','U')
and serv_code in ('10086','010086')
and deal_result in ('0', '1')
) l
on
d.mdate=l.create_date
and d.accept_no=l.phone_id
order by d.handle_date
and n.accept_no=m.phone_id 这几个列含义是?
先看看你的执行计划吧.
在sqlplus中.
alter session set events '10046 trace name context forever,level 8'这里执行你的语句:
alter session set events '10046 trace name context off';然后方便的话把*.trc发给我,或者上传到某个地方.