如何计算MySQL中两个日期之间的星期六和星期日的总数 [英] how to calculate total number of saturday and sunday between two dates in mysql

问题描述

SELECT
    floor(
        datediff('2016-08-01','2016-07-01') / 7) * 2 + (
        CASE WHEN
        IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) >=
    IF (weekday('2016-07-02') >= 5, 4,weekday('2016-07-01')) 
    THEN

            IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -
            IF (weekday('2016-07-01') >= 5, 4,weekday('2016-07-01'))
        ELSE
            5 +
            IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -      
            IF (weekday('2016-07-01') >= 5, 4, weekday('2016-07-01'))
        END
    ) weekdays;

当我执行此代码时，它总是返回9我不知道我在哪里做错了，请向我建议哪里做错了.而在给定日期之间的周六至周日之间应该为10，请建议我

when i execute this code it always return 9 i don't know where i am doing mistake please suggest me where am doing wrong. while between Saturday and Sunday between given date should be 10 please suggest me

推荐答案

如果您没有一个表格，其中包含开始日期和结束日期(包括开始日期)之间的所有日期，那么您需要采用一个查询来创建所有您给定日期范围(含)之间的日期优先.然后使用MySQL的WEEKDAY功能检查日期是Saturday还是Sunday

If you don't have a table having all the dates between your start date and end date (inclusive) then you need to adopt a query which will create all the dates between your given date range (inclusive) first. Then use WEEKDAY function of MySQL to check whether the day is Saturday or Sunday

SELECT 
dateTable.Day,
DAYNAME(dateTable.Day) AS dayName
FROM 
(   SELECT ADDDATE('2016-07-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-01', '2016-07-01')

) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)
ORDER BY dateTable.Day;

工作演示

注意: WEEKDAY 返回日期的星期几索引(0 =星期一，1 =星期二，…6 =星期日).

Note: WEEKDAY returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

如果仅需要count:

SELECT 
COUNT(*) AS total
FROM 
(   SELECT ADDDATE('2016-07-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-01', '2016-07-01')

) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)

演示

这篇关于如何计算MySQL中两个日期之间的星期六和星期日的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！