MySql - 如何获取工作日？

Question

我想获取两个日期之间一周的工作日并将它们输出到如下表中:

|2014-07-21|Monday   |
|2014-07-22|Tuesday  |
|2014-07-23|Wednesday|
|2014-07-24|Thursday |
|2014-07-25|Friday   |

这是我迄今为止在互联网上找到的结果所尝试的。

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `interval_between`(startdate Date, enddate Date, interval_size VARCHAR(10), interval_value INT)
BEGIN
    DECLARE thisDate Date;
    SET thisDate = startdate;
    CREATE TEMPORARY TABLE IF NOT EXISTS time_intervals (       
        interval_from Date      
    );

    DELETE FROM time_intervals;

    REPEAT
        INSERT INTO time_intervals SELECT  thisDate;
        SELECT
            CASE interval_size
                WHEN 'MICROSECOND' THEN TIMESTAMPadd(MICROSECOND, interval_value, thisDate)
                WHEN 'SECOND'      THEN TIMESTAMPadd(SECOND, interval_value, thisDate)
                WHEN 'MINUTE'      THEN TIMESTAMPadd(MINUTE, interval_value, thisDate)
                WHEN 'HOUR'        THEN TIMESTAMPadd(HOUR, interval_value, thisDate)
                WHEN 'DAY'         THEN TIMESTAMPadd(DAY, interval_value, thisDate)
                WHEN 'WEEK'        THEN TIMESTAMPadd(WEEK, interval_value, thisDate)
                WHEN 'MONTH'       THEN TIMESTAMPadd(MONTH, interval_value, thisDate)
                WHEN 'YEAR'        THEN TIMESTAMPadd(YEAR, interval_value, thisDate)
            END INTO thisDate;
    UNTIL thisDate >= enddate
    END REPEAT; 
END

Answer 1

假设您想要 2 个日期(含)之间的天数列表，则以下内容即可实现，最大范围为 1000 天。

这将 3 个子查询相互连接起来，将它们视为单位、十和百，并从中计算数字 0 到 999。当天数小于 2 之间的天数时，将其添加到开始日期日期。然后使用 DAYOFWEEK 排除星期六和星期日。

SELECT DATE_ADD('2014-07-21', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 DAY) AS aDate
FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
WHERE DATEDIFF('2014-08-25', '2014-07-21') >= units.i + tens.i * 10 + hundreds.i * 100
HAVING DAYOFWEEK(aDate) BETWEEN 2 AND 6