关于SQL优化的问题

[yoyoshadow]

select DATE
  from (SELECT DATE
          FROM TABLE_EXC
         WHERE OFFE_VAR = 'S1'
           AND DATA_PAR = 3
           AND DATE <= to_date('2011-02-21', 'yyyy-mm-dd')
           AND WEIGH_PRI <> 0
         ORDER BY DATE DESC)
 where rownum = 1;

select DATE
  from (SELECT DATE
          FROM TABLE_EXC
         WHERE OFFE_VAR = 'S1'
           AND DATA_PAR = 3
           AND DATE <= to_date('2011-02-22', 'yyyy-mm-dd')
           AND WEIGH_PRI <> 0
         ORDER BY DATE DESC)
 where rownum = 1;

select DATE
  from (SELECT DATE
          FROM TABLE_EXC
         WHERE OFFE_VAR = 'S1'
           AND DATA_PAR = 3
           AND DATE <= to_date('2011-02-23', 'yyyy-mm-dd')
           AND WEIGH_PRI <> 0
         ORDER BY DATE DESC)
 where rownum = 1;

请问怎么把这3条sql整合成一条语句，能求出3个纪录来

[oO寒枫Oo]

select t.DATE from TABLE_EXC t
where t.OFFE_VAR = 'S1' AND t.DATA_PAR = 3 and t.WEIGH_PRI <> 0
and not exists(select 1 from TABLE_EXC where trunc(DATE,'dd')=trunc(t.DATE,'dd')
and DATE>t.DATE and OFFE_VAR = 'S1' AND DATA_PAR = 3 and WEIGH_PRI <> 0
)
and t.DATE>=to_date('2011-02-20', 'yyyy-mm-dd') and t.DATE<to_date('2011-02-23', 'yyyy-mm-dd') 

[cosio]

union all

[oO寒枫Oo]

或者

select t.date
from 
(
select DATE,row_number() over(partition by trunc(DATE,'dd') order by DATE desc) as rn
from TABLE_EXC
where FFE_VAR = 'S1' AND DATA_PAR = 3 and WEIGH_PRI <> 0 
and DATE>=to_date('2011-02-20', 'yyyy-mm-dd') and DATE<to_date('2011-02-23', 'yyyy-mm-dd') 
)t
where t.rn=1

[xpingping]

[Quote=引用 2 楼 cosio 的回复:]
SQL code
union all
[/Quote]
支持2楼
TABLE_EXC只有'2011-02-21'或者还有更加小的日期时 查处的结果是：3条'2011-02-21'
TABLE_EXC只有'2011-02-22'时，其他小于'2011-02-22'的日期时，查处的结果是，2条'2011-02-22'，一条是其他小于'2011-02-22'的日期中最大的一条
等等情况……

[xiaobn_cn]

-- 通过SQL建立一个子查询，然后与原表进行交叉后，分组求最大值即可。
SELECT MAX(A.DATE)
  FROM TABLE_EXC A,
       (SELECT to_date('2011-02-21', 'yyyy-mm-dd') DT FROM DUAL UNION ALL
        SELECT to_date('2011-02-21', 'yyyy-mm-dd') DT FROM DUAL UNION ALL
        SELECT to_date('2011-02-21', 'yyyy-mm-dd') DT FROM DUAL
       ) B
 WHERE OFFE_VAR = 'S1'
   AND DATA_PAR = 3
   AND WEIGH_PRI <> 0
   AND A.DATE <= B.DT
GROUP BY B.DT;

[jym2002]

[Quote=引用 4 楼 xpingping 的回复:]
引用 2 楼 cosio 的回复:
SQL code
union all

支持2楼
TABLE_EXC只有'2011-02-21'或者还有更加小的日期时 查处的结果是：3条'2011-02-21'
TABLE_EXC只有'2011-02-22'时，其他小于'2011-02-22'的日期时，查处的结果是，2条'2011-02-22'，一条是其他小于'2011-02-22'的日期中最大的……
[/Quote]
瞎支持，2L的和楼主的需求是不等价的，比如
-----------------------------------
TABLE_EXC中最大data是'2011-02-10'的话，2L的查询是没有结果的

[jym2002]

[Quote=引用楼主 yoyoshadow 的回复:]
SQL code
select DATE
from (SELECT DATE
FROM TABLE_EXC
WHERE OFFE_VAR = 'S1'
AND DATA_PAR = 3
AND DATE <= to_date('2011-02-21', 'yyyy-mm-dd')
……
[/Quote]
关键还是看你这个表的大小以及索引、数据分布情况

[xpingping]

[Quote=引用 6 楼 jym2002 的回复:]
引用 4 楼 xpingping 的回复:
引用 2 楼 cosio 的回复:
SQL code
union all

支持2楼
TABLE_EXC只有'2011-02-21'或者还有更加小的日期时 查处的结果是：3条'2011-02-21'
TABLE_EXC只有'2011-02-22'时，其他小于'2011-02-22'的日期时，查处的结果是，2条'2011-02-22'，一条是……
[/Quote]
为什么查不出，不是三条是'2011-02-10'吗？

[hupeng213]

楼主取三个日期是想做什么用？
给出来的示例即没sum也没count

SELECT to_date('2011-02-21', 'YYYY-MM-DD') + ROWNUM - 1 service_date
FROM dual
CONNECT BY ROWNUM <= (to_date('2011-02-23', 'YYYY-MM-DD') -
to_date('2011-02-21', 'YYYY-MM-DD') + 1)