select t.*
from (select rownum as rn,
a.emp_id,
a.service_date,
a.customer_id,
a.service_type
from (select s.PROCESSER_ID as emp_id,
to_char(s.done_date, 'yy-mm-dd') as service_date,
s.customer_id,
s.customer_tax_id,
decode(s.service_type_id, 1, '维护', 4, '回访') as service_type
from EMP_SERVICE2014 s
where s.service_type_id in (1, 4)
and s.processer_id = 216491
UNION all
select p.fee as emp_id,
to_char(p.visit_end_date, 'yy-mm-dd') as service_date,
p.customer_id,
p.customer_tax_id,
'回访' as service_type
from EMP_PV2014 p
where p.fee = 216491
and not exists
(select 1
from emp_service2014 s
where p.customer_id = s.customer_id
and to_char(p.visit_end_date, 'yyyy-mm-dd') =
to_char(s.done_date, 'yyyy-mm-dd')
and s.service_type_id in (1, 4))) a
where rownum < 50) t
where t.rn > 10
from (select rownum as rn,
a.emp_id,
a.service_date,
a.customer_id,
a.service_type
from (select s.PROCESSER_ID as emp_id,
to_char(s.done_date, 'yy-mm-dd') as service_date,
s.customer_id,
s.customer_tax_id,
decode(s.service_type_id, 1, '维护', 4, '回访') as service_type
from EMP_SERVICE2014 s
where s.service_type_id in (1, 4)
and s.processer_id = 216491
UNION all
select p.fee as emp_id,
to_char(p.visit_end_date, 'yy-mm-dd') as service_date,
p.customer_id,
p.customer_tax_id,
'回访' as service_type
from EMP_PV2014 p
where p.fee = 216491
and not exists
(select 1
from emp_service2014 s
where p.customer_id = s.customer_id
and to_char(p.visit_end_date, 'yyyy-mm-dd') =
to_char(s.done_date, 'yyyy-mm-dd')
and s.service_type_id in (1, 4))) a
where rownum < 50) t
where t.rn > 10
解决方案 »
- 数据库导出
- 需要记录1000个房间,每天的温度(上午,下午,晚上各一次),是建立一个表好呢,还是每个房间建立一个表?
- oracle 数据库 某名奇妙丢失数据
- Oracle 如何在SQL窗口中多次调用一个存储过程
- 滁州e8-11套餐88元每月(2M) 想用正则表达式匹配 后面的这个88元档正则表示式怎么写?
- (急急急急急急急急急急急急急)Oracle11G连接超时
- 请问如何通过Oracle来查询到服务器的硬盘使用信息?
- 请高手指点秘经---先谢了!
- 不能在WIN2K下安装Oracle,50分求教。
- 求助 这类查询该如何建索引,原查询要5秒多,有点慢了
- 关于oracle中间表以及clob的问题
- Oracle怎么让判断语句不执行。
select语句执行时,where子句的执行早于select 子句
执行 where rn<50时,rownum as rn重命名还未执行,因此报错,找不到rn