ORACLE确定两个日期之间的工作日数目

来源：互联网发布：python 支付宝接口编辑：程序博客网时间：2024/06/03 00:32

SQL> select ename,hiredate from emp;

ENAME      HIREDATE
---------- ---------------
SMITH      17-DEC-80
ALLEN      20-FEB-81
WARD       22-FEB-81
JONES      02-APR-81
MARTIN     28-SEP-81
BLAKE      01-MAY-81
CLARK      09-JUN-81
SCOTT      19-APR-87
KING       17-NOV-81
TURNER     08-SEP-81
ADAMS      23-MAY-87

ENAME      HIREDATE
---------- ---------------
JAMES      03-DEC-81
FORD       03-DEC-81
MILLER     23-JAN-82

14 rows selected.

现在我们想知道WARD和JONES两名员工的HIREDATE之间相差多少个工作日。

SQL> select sum(case when to_char(jones_hd+t100.id-1,'DY') in ('Sat','Sun') then
0 else 1 end) as days
2 from
3 (
4 select
5 max(case when ename='WARD' then hiredate end) as ward_hd,
6 max(case when ename='JONES' then hiredate end) as jones_hd
7 from emp
8 where ename in('WARD','JONES')
9 ) x,t100
10 where t100.id<=jones_hd-ward_hd+1;

DAYS
----------
40

可能有人会想为什么这里要加个max()函数，其实目的就是为了去除NULL，下面做两个查询看看。

SQL> select
2 (case when ename='WARD' then hiredate end) as ward_hd,
3 (case when ename='JONES' then hiredate end) as jones_hd
4 from emp
5 where ename in('WARD','JONES');

WARD_HD JONES_HD
--------------- ---------------
22-FEB-81
02-APR-81

SQL> select
2 max(case when ename='WARD' then hiredate end) as ward_hd,
3 max(case when ename='JONES' then hiredate end) as jones_hd
4 from emp
5 where ename in('WARD','JONES');

WARD_HD JONES_HD
--------------- ---------------
22-FEB-81 02-APR-81

上述两个日期之间的天数是40。既然现在两个日期处于一行，那么，下一步就是要对这40天的每一天分别生成一行记录，这就需要使用到基干表t100。由于表t100中的每个id值都比上一个值大1，在两个日期中较早的一个(WARD_HD)上分别加上t100中各行的id，就可以生成从WARD_HD到JONES_HD(包括)的连续日期。

SQL> select x.*,t100.*,jones_hd+t100.id-1
2 from
3 (
4 select
5 max(case when ename='WARD' then hiredate end) as ward_hd,
6 max(case when ename='JONES' then hiredate end) as jones_hd
7 from emp
8 where ename in('WARD','JONES')
9 ) x,t100
10 where t100.id<=jones_hd-ward_hd+1;

WARD_HD         JONES_HD                ID JONES_HD+T100.I
--------------- --------------- ---------- ---------------
22-FEB-81       02-APR-81                1 02-APR-81
22-FEB-81       02-APR-81                2 03-APR-81
22-FEB-81       02-APR-81                3 04-APR-81
22-FEB-81       02-APR-81                4 05-APR-81
22-FEB-81       02-APR-81                5 06-APR-81
22-FEB-81       02-APR-81                6 07-APR-81
22-FEB-81       02-APR-81                7 08-APR-81
22-FEB-81       02-APR-81                8 09-APR-81
22-FEB-81       02-APR-81                9 10-APR-81
22-FEB-81       02-APR-81               10 11-APR-81
22-FEB-81       02-APR-81               11 12-APR-81

WARD_HD         JONES_HD                ID JONES_HD+T100.I
--------------- --------------- ---------- ---------------
22-FEB-81       02-APR-81               12 13-APR-81
22-FEB-81       02-APR-81               13 14-APR-81
22-FEB-81       02-APR-81               14 15-APR-81
22-FEB-81       02-APR-81               15 16-APR-81
22-FEB-81       02-APR-81               16 17-APR-81
22-FEB-81       02-APR-81               17 18-APR-81
22-FEB-81       02-APR-81               18 19-APR-81
22-FEB-81       02-APR-81               19 20-APR-81
22-FEB-81       02-APR-81               20 21-APR-81
22-FEB-81       02-APR-81               21 22-APR-81
22-FEB-81       02-APR-81               22 23-APR-81

WARD_HD         JONES_HD                ID JONES_HD+T100.I
--------------- --------------- ---------- ---------------
22-FEB-81       02-APR-81               23 24-APR-81
22-FEB-81       02-APR-81               24 25-APR-81
22-FEB-81       02-APR-81               25 26-APR-81
22-FEB-81       02-APR-81               26 27-APR-81
22-FEB-81       02-APR-81               27 28-APR-81
22-FEB-81       02-APR-81               28 29-APR-81
22-FEB-81       02-APR-81               29 30-APR-81
22-FEB-81       02-APR-81               30 01-MAY-81
22-FEB-81       02-APR-81               31 02-MAY-81
22-FEB-81       02-APR-81               32 03-MAY-81
22-FEB-81       02-APR-81               33 04-MAY-81

WARD_HD         JONES_HD                ID JONES_HD+T100.I
--------------- --------------- ---------- ---------------
22-FEB-81       02-APR-81               34 05-MAY-81
22-FEB-81       02-APR-81               35 06-MAY-81
22-FEB-81       02-APR-81               36 07-MAY-81
22-FEB-81       02-APR-81               37 08-MAY-81
22-FEB-81       02-APR-81               38 09-MAY-81
22-FEB-81       02-APR-81               39 10-MAY-81
22-FEB-81       02-APR-81               40 11-MAY-81

40 rows selected.

t100表的内容如下

SQL> desc t100;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NUMBER

SQL> select * from t100;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11

        ID
----------
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22

        ID
----------
        23
        24
        25
        26
        27
        28
        29
        30
        31
        32
        33

        ID
----------
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44

        ID
----------
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55

        ID
----------
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66

        ID
----------
        67
        68
        69
        70
        71
        72
        73
        74
        75
        76
        77

        ID
----------
        78
        79
        80
        81
        82
        83
        84
        85
        86
        87
        88

        ID
----------
        89
        90
        91
        92
        93
        94
        95
        96
        97
        98
        99

ID
----------
100

100 rows selected.