oracle两个日期间工作日,ORACLE确定两个日期之间的工作日数目
SQL> select ename,hiredate from emp;ENAME HIREDATE---------- ---------------SMITH 17-DEC-80ALLEN 20-FEB-81WARD 22-FEB-81JONES 02-APR-81MARTIN 28-SEP-81BLAKE 01-MA...
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.
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献5条内容
所有评论(0)