mysql按工作日查询统计优化_工作日计算问题思路和实现

项目中目前已有一周表存储了一年中所有的假日，给定查询起始日期和结束日期，推导出查询时间段内工作日是多少。为了简化这个问题，需要下面几个假设。 1. 不考虑周六周日，将其视作普通工作日 2. 假日没有交叠情况，不会出现10月1日到7日是国庆节，其中又有

项目中目前已有一周表存储了一年中所有的假日，给定查询起始日期和结束日期，推导出查询时间段内工作日是多少。为了简化这个问题，需要下面几个假设。

1. 不考虑周六周日，将其视作普通工作日

2. 假日没有交叠情况，不会出现10月1日到7日是国庆节，其中又有一个其它的节日

给出假日表的设计，某个假日都有起始时间和结束时间，这里只取月日，这样就能表示每一年的假日。

CREATE TABLE [dbo].[holiday](

[begin_time] [varchar](50) NULL,

[end_time] [varchar](50) NULL

) ON [PRIMARY]

GO

插入测试数据，例如插入国庆节的假日

给定查询时间段为从2014-09-30至2014-10-08，这期间的工作日

declare @query_begin datetime --查询起始时间

declare @query_end datetime --查询结束时间

declare @year1 int

declare @year2 int

declare @yeartemp int

declare @total_holidays int

set @query_begin = '2014-09-01'

set @query_end = '2015-01-31'

set @year1 = YEAR(@query_begin)

set @year2 = YEAR(@query_end)

--存储所有的含有年月日的假期

IF object_id('tempdb..#temp') is not null

BEGIN

drop table #temp

END

CREATE table #temp

(

begin_time date,

end_time date,

)

insert into #temp

select convert(varchar(4),@year1)+'-'+begin_time, convert(varchar(4),@year1)+'-'+end_time

from holiday

--这里主要考虑查询时间段跨年的情况

set @yeartemp=@year1+1

while @yeartemp<=@year2

begin

insert into #temp

select convert(varchar(4),@yeartemp)+'-'+begin_time, convert(varchar(4),@yeartemp)+'-'+end_time

from holiday

set @yeartemp=@yeartemp+1

end

--去掉和查询时间段没有一点交集的假日段

delete from #temp

where end_time@query_end

select @total_holidays= SUM(DATEDIFF(dd,begin_time,end_time)+1)

from

(

select case when begin_time@query_end then @query_end else end_time end as end_time from #temp

) t

select DATEDIFF(DD,@query_begin,@query_end)+1-@total_holidays

drop table #temp

执行该脚本就可以得到结果是2，符合预期。下面给出一些特殊测试用例，验证脚本是否能正确计算工作日。

1. 查询时间为2014-10-05至2014-10-08

结果：1

2. 查询时间为2014-09-30至2014-10-07

结果：1

3. 增加一条假日，例如是教师节，查询时间段为2014-09-01至2014-10-08

结果：30

4. 在增加一条假日记录，元旦，查询时间段为2014-09-01至2015-01-31

现在holiday表的记录为：

如果手动去算就是：30+31+30+31+31-7-1-1=144

实际结果：144

本文原创发布php中文网，转载请注明出处，感谢您的尊重！