出于举例的目的,我们使用静态@start和@end日期,但实际上您可以将它们替换为列名称,并且所有这些值都将按每行重新计算。
SET @start = '2012-09-30';
SET @end = '2012-11-03';
SELECT
@raw_days := DATEDIFF(@end, @start)+1 'raw_days',
@full_weeks := FLOOR(@raw_days/7) 'full_weeks',
@odd_days := @raw_days - @full_weeks * 7 'odd_days',
@wday_start := DAYOFWEEK(@start) 'wday_start',
@wday_end := DAYOFWEEK(@end) 'wday_end',
@weekend_intrusion := @wday_start + @odd_days 'weekend_intrusion',
@extra_weekends :=
IF(@wday_start = 1, IF(@odd_days = 0, 0, 1),
IF(@weekend_intrusion > 7, 2,
IF(@weekend_intrusion > 6, 1, 0)
)
) 'extra_weekends',
@total_weekends := @full_weeks * 2 + @extra_weekends 'total_weekends',
@total_workdays := @raw_days - @total_weekends 'total_workdays'
的IF陈述归结为:
如果一周上周日开始,并没有“奇”天,再没有多余的周末。如果是奇怪的日子,那么只能有一个周末,因为它不可能延伸到周六,因为这将是一个“完整的”星期。
否则,我们看看一周的剩余部分是否延续过去的星期天。如果是这样,请添加2个周末。否则,如果部分转到星期六,则增加1个周末日。否则为0
输出:
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| raw_days | full_weeks | odd_days | wday_start | wday_end | weekend_intrusion | extra_weekends | total_weekends | total_workdays |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+
| 34 | 4 | 6 | 1 | 6 | 7 | 1 | 9 | 25 |
+----------+------------+----------+------------+----------+-------------------+----------------+----------------+----------------+