如何计算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，请给我建议如何计算mysql中两个日期之间的星期六和星期日的总数

请基于一些样品输入添加您预期的输出。不要只粘贴正在运行的查询。 – 1000111

我希望我们在放置2016-08-01和2016-07-01这两个日期时，它应该在给定日期之间返回所有星期六的星期日 –

您是否有任何表格有所有日期？ – 1000111

答

如果你没有让所有的开始日期和结束日期（含）之间的日期表又那么你就需要采取查询，这将创造所有日期范围（含）之间的所有日期。然后使用MySQLWEEKDAY功能检查日是否为Saturday或Sunday

SELECT 
dateTable.Day, 
DAYNAME(dateTable.Day) AS dayName 
FROM 
( SELECT ADDDATE('2016-07-01', INTERVAL @i:[email protected]+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;

WORKING DEMO

注： WEEKDAY返回日期（平日指数0 =周一，1 =星期二，... 6 =星期日）。

编辑：

如果您只需要count：

SELECT 
COUNT(*) AS total 
FROM 
( SELECT ADDDATE('2016-07-01', INTERVAL @i:[email protected]+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)

Demo

我必须只显示计数不是天 –

请在编辑部分下查看 – 1000111

如何计算mysql中两个日期之间的星期六和星期日的总数

相关推荐