--查询某日期区间,未退款且已支付成功的支付记录
SELECT service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(
    SELECT order_id  FROM ctoc_order A2 WHERE 1=1
    and A2.order_id=A1.service_order
    AND (Order_Status =1 OR Order_Status=2)
    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd')
)
and payment_status=0
group by service_order
having sum(PAYMENT_AMOUNT)>0

订单表:ctoc_order
id:order_id
状态:Order_Status

支付表:PAYMENT
订单表外键;service_order
金额:PAYMENT_AMOUNT

--求加速,求优化```

SELECT service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(
    SELECT order_id  FROM ctoc_order A2 WHERE 1=1
    and A2.order_id=A1.service_order
    AND (Order_Status =1 OR Order_Status=2)
    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd')
)
and payment_status=0
group by service_order
having sum(PAYMENT_AMOUNT)>0

既然你过滤了sum(PAYMENT_AMOUNT)>0
是不是开始panment_amount>0的条件就可以写在where那里呢，而不是group by后判断

------解决方案--------------------
1.使用hint，改变or的执行计划。
2.你的date应该可以直接比较吧，不使用trunc，例如数据库中日期 ’2012-1-2 01：01：20‘ 判断它是否在1月付款、退款，不用做时间处理的。
SQL code
SELECT /*+USE_CONCAT*/ service_order,sum(PAYMENT_AMOUNT) FROM PAYMENT A1 WHERE EXISTS(
    SELECT order_id  FROM ctoc_order A2 WHERE 1=1
    and A2.order_id=A1.service_order
    AND (Order_Status =1 OR Order_Status=2)
    AND trunc(ORDER_CREATE_TIME) between to_date('2012-05-01','yyyy-MM-dd') and to_date('2012-06-01','yyyy-MM-dd')
)
and payment_status=0
group by service_order
having sum(PAYMENT_AMOUNT)>0