|
|
马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？注册
×
问题
给定两个日期，求它们之间（包括这两个日期本身）有多少个“工作”日。例如，如果1月10日是星期一，1月11日是星期二，由于这两个日期是典型的工作日，所以两个日期之间的工作日数是2。对于这个问题，“工作日”定义为非周六/周日的日子。
解决方案
下面的例子计算BLAKE 和JONES 的HIREDATE（聘用日期）之间的工作日数。要确定两个日期之间的工作日数，可以使用基干表，对两个日期（其中包括起始日期和结束日期）之间的每一天都返回一行。此后，计算工作日数只是数一下返回的日期中非周六/周日的数目。
注意： 如果也想去掉假期，则可以创建一个HOLIDAYS表。然后引入NOT IN前缀，就能够从解决方案中去掉HOLIDAYS 中列出的日子。
DB2
使用基干表T500，能够生成两个日期之间包含的行数（表示天数），然后，对非周末的所有日期计数。使用DAYNAME 函数可返回每个日期是星期几。例如：
1 select sum(case when dayname(jones_hd+t500.id day -1 day)
2 in ( 'Saturday','Sunday' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
MySQL
使用基干表T500能够生成两个日期之间包含的行数（天数），然后，对非周末的所有日期计数。使用DATE_ADD函数可以给每个日期增加天数；使用DATE_FORMAT函数判定每个日期是星期几：
1 select sum(case when date_format(
2 date_add(jones_hd,
3 interval t500.id-1 DAY),'%a')
4 in ( 'Sat','Sun' )
5 then 0 else 1
6 end) as days
7 from (
8 select max(case when ename = 'BLAKE'
9 then hiredate
10 end) as blake_hd,
11 max(case when ename = 'JONES'
12 then hiredate
13 end) as jones_hd
14 from emp
15 where ename in ( 'BLAKE','JONES' )
16 ) x,
17 t500
18 where t500.id <= datediff(blake_hd,jones_hd)+1
Oracle
使用基干表T500能够生成两个日期之间包含的行数（天数），然后，对非周末的所有日期计数。使用TO_CHAR函数确定每个日期是星期几：
1 select sum(case when to_char(jones_hd+t500.id-1,'DY')
2 in ( 'SAT','SUN' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
PostgreSQL
使用基干表T500能够生成两个日期之间包含的行数（天数），然后，对非周末的所有日期计数。使用TO_CHAR函数确定每个日期是星期几：
1 select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))
2 in ( 'SATURDAY','SUNDAY' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= blake_hd-jones_hd+1
SQL Server
使用基干表T500生成两个日期之间包含的行数（天数），然后，对非周末的所有日期计数。使用DATENAME函数确定每个日期是星期几：
1 select sum(case when datename(dw,jones_hd+t500.id-1)
2 in ( 'SATURDAY','SUNDAY' )
3 then 0 else 1
4 end) as days
5 from (
6 select max(case when ename = 'BLAKE'
7 then hiredate
8 end) as blake_hd,
9 max(case when ename = 'JONES'
10 then hiredate
11 end) as jones_hd
12 from emp
13 where ename in ( 'BLAKE','JONES' )
14 ) x,
15 t500
16 where t500.id <= datediff(day,jones_hd-blake_hd)+1
讨论
尽管每个RDBMS都需要使用不同的内置函数确定日期名，对每个系统来说，总体解决方案都相同。可以把解决方案分成下面两个步骤：
1. 返回起始日期和结束日期之间的天数（二者均包含在内）；
2. 计数除周末以外共有多少天（即行数）。
内联视图X 完成第一步操作。如果检查内联视图X，会注意到，它使用了聚集函数MAX，该函数用于删除NULL。如果对MAX的用法不是很清楚，下面这个例子会有助于读者理解。以下显示了未使用MAX时内联视图X的结果：
select case when ename = 'BLAKE'
then hiredate
end as blake_hd,
case when ename = 'JONES'
then hiredate
end as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
02-APR-1981
01-MAY-1981
如果不使用MAX，会返回两行。而使用MAX，只返回一行，且去掉了NULL：
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
BLAKE_HD JONES_HD
----------- -----------
01-MAY-1981 02-APR-1981
上述两个日期之间的天数（其中包括两个日期本身）是30。既然两个日期处于一行，那么，下一步就要对这30天的每一天分别生成一行记录。要返回30天（行），需使用表T500。由于表T500中的每个ID值都比它的前一个值大1，在两个日期中较早的一个（JONES_HD）上分别加上T500中各行的ID，就可以生成从JONES_HD日期开始直到BLAKE_HD（包括）的连续工作日。其结果如下所示（使用Oracle语法）：
select x.*, t500.*, jones_hd+t500.id-1
from (
select max(case when ename = 'BLAKE'
then hiredate
end) as blake_hd,
max(case when ename = 'JONES'
then hiredate
end) as jones_hd
from emp
where ename in ( 'BLAKE','JONES' )
) x,
t500
where t500.id <= blake_hd-jones_hd+1
BLAKE_HD JONES_HD ID JONES_HD+T5
----------- ----------- ---------- -----------
01-MAY-1981 02-APR-1981 1 02-APR-1981
01-MAY-1981 02-APR-1981 2 03-APR-1981
01-MAY-1981 02-APR-1981 3 04-APR-1981
01-MAY-1981 02-APR-1981 4 05-APR-1981
01-MAY-1981 02-APR-1981 5 06-APR-1981
01-MAY-1981 02-APR-1981 6 07-APR-1981
01-MAY-1981 02-APR-1981 7 08-APR-1981
01-MAY-1981 02-APR-1981 8 09-APR-1981
01-MAY-1981 02-APR-1981 9 10-APR-1981
01-MAY-1981 02-APR-1981 10 11-APR-1981
01-MAY-1981 02-APR-1981 11 12-APR-1981
01-MAY-1981 02-APR-1981 12 13-APR-1981
01-MAY-1981 02-APR-1981 13 14-APR-1981
01-MAY-1981 02-APR-1981 14 15-APR-1981
01-MAY-1981 02-APR-1981 15 16-APR-1981
01-MAY-1981 02-APR-1981 16 17-APR-1981
01-MAY-1981 02-APR-1981 17 18-APR-1981
01-MAY-1981 02-APR-1981 18 19-APR-1981
01-MAY-1981 02-APR-1981 19 20-APR-1981
01-MAY-1981 02-APR-1981 20 21-APR-1981
01-MAY-1981 02-APR-1981 21 22-APR-1981
01-MAY-1981 02-APR-1981 22 23-APR-1981
01-MAY-1981 02-APR-1981 23 24-APR-1981
01-MAY-1981 02-APR-1981 24 25-APR-1981
01-MAY-1981 02-APR-1981 25 26-APR-1981
01-MAY-1981 02-APR-1981 26 27-APR-1981
01-MAY-1981 02-APR-1981 27 28-APR-1981
01-MAY-1981 02-APR-1981 28 29-APR-1981
01-MAY-1981 02-APR-1981 29 30-APR-1981
01-MAY-1981 02-APR-1981 30 01-MAY-1981
检查WHERE子句会注意到，对BLAKE_HD和JONES_HD之差进行了加1操作，生成了30行（否则的话，将生成29行）。另外，还会看到，对外部查询的SELECT列表中的T500.ID进行了减1操作，这是由于ID的起始值为1，而且，对JONES_HD进行加1操作，会导致从最终结果中减掉JONES_HD。
一旦生成了结果集需要的行数，可以使用CASE表达式“标记”返回的每一天是工作日还是周末（工作日返回1，周末返回0）。最后一步，使用求和函数SUM计算1的个数，以得到最终答案。 |
|
|Archiver|小黑屋|DBA论坛中国
( 鲁ICP备20017503号-2 )
IP卡
狗仔卡
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡