你那两个列是varchar2数据类型吗,另外apply_date的值完全是标准的YYYY-MM-DD数据类型吗? 如果是的话,我觉得可以在表上建这么一个函数索引: create index idx_a on a(nvl(apply_date,receive_date)) 然后用字符串比较来查询:SELECT nvl(apply_date,receive_date) tm, area FROM a WHERE nvl(apply_date,receive_date) BETWEEN '2014-01-01' AND '2014-08-05'
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');
如果是的话,我觉得可以在表上建这么一个函数索引: create index idx_a on a(nvl(apply_date,receive_date))
然后用字符串比较来查询:SELECT nvl(apply_date,receive_date) tm, area
FROM a
WHERE nvl(apply_date,receive_date) BETWEEN '2014-01-01' AND '2014-08-05'
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');