有如下一张表需要查询某段时间内的数据,需要根据apply_date和receive_date来判断是否是在时间段内，如果apply_date有的话就用apply_date判断，如果没有值就用receive_date来判断，目前有弄出来，但是感觉时间用太长了,麻烦大家帮忙看下，谢谢了！
id apply_date receive_date area
1 2014-01-01 a
2 2014/05/06 b
3 2014-03-09 c
4 2014-05-01 2014-05-03 d
5 3
目前的写法是
select tm,area from
(select (case when apply_date is null or apply_date ='null' then receive_date else apply_date end) as tm, area from A)
where to_date(tm,'YYYY-MM-DD HH24:MI:SS') between to_date('2014-01-01','YYYY-MM-DD HH24:MI:SS') and to_date('2014-08-05','YYYY-MM-DD HH24:MI:SS')
------解决思路----------------------
select receive_date, apply_date, area
from A
where apply_date between '2014-01-01' and '2014-08-05'
or apply_date between '2014/01/01' and '2014/08/05'
or ((apply_date is null or apply_date = 'null') and
receive_date between '2014-01-01' and '2014-08-05')
可以建apply_date,receive_date,area组合索引
------解决思路----------------------
WITH T AS
(SELECT 1 ID, '2014-01-01' APPLY_DATE, NULL RECEIVE_DATE, 'a' AREA
FROM DUAL
UNION ALL
SELECT 2, '2014/05/06', NULL, 'b'
FROM DUAL
UNION ALL
SELECT 3, NULL, '2014-03-09', 'c'
FROM DUAL
UNION ALL
SELECT 4, '2014-05-01', '2014-05-03', 'd'
FROM DUAL
UNION ALL
SELECT 5, NULL, NULL, '3'
FROM DUAL)
SELECT TM1, AREA
FROM (SELECT TO_DATE(TM, 'yyyy-mm-dd') TM1, AREA
FROM (SELECT NVL(APPLY_DATE, RECEIVE_DATE) TM, AREA
FROM T
WHERE APPLY_DATE IS NOT NULL
OR RECEIVE_DATE IS NOT NULL) TMP)
WHERE TM1 BETWEEN TO_DATE('20140101', 'yyyymmdd') AND
TO_DATE('20140805', 'yyyymmdd');