读书:《Oracle查询优化改写》后有感。
以emp表为例,我们需要求得hiredate的最大值与最小值之间的工作天数。
首先,我们需要求出max和min的hiredate:
SQL> select max(t.hiredate) as maxdate, min(t.hiredate) as mindate from emp t;
MAXDATE MINDATE
----------- -----------
1987/5/23 1980/12/17
SQL>
其次,就是最关键的了: 要将min到max之间的日期全部展示出来,这里需要借助level,level的深度就是max到min之间的天数(加不加1,看各人,反正我是没加):
select t1.mindate + (level - 1) as datestep
from (select max(t.hiredate) as maxdate, min(t.hiredate) as mindate
from emp t) t1
connect by level <= t1.maxdate - t1.mindate
结果:
(这里没有展示完全)
再然后根据to_char函数,将日期类型转换为星期,然后过滤即可:
SQL> select sum(case
2 when to_char(t2.datestep, 'D') not in ('1', '7') then
3 1
4 else
5 0
6 end) as workdays
7 from (select t1.mindate + (level - 1) as datestep
8 from (select max(t.hiredate) as maxdate, min(t.hiredate) as mindate
9 from emp t) t1
10 connect by level <= t1.maxdate - t1.mindate) t2;
WORKDAYS
----------
1678
SQL>