如何查找mysql中日期范围内每个月的天数？

问题描述：

我有一个表中的数据，如何找到天每月从这个数量：如何查找mysql中日期范围内每个月的天数？

serial  |  date_from  |  date_from 
    001   |  2012-12-20  |  2013-01-25 
    002   |  2012-12-20  |  2013-01-25 
    003   |  2012-12-20  |  2013-01-25 
    001   |  2013-01-26  |  2013-04-26

第一行所需的结果：

Serial  | days |  month 
    001   | 11 |  December, 2012 
    001   | 25 |  January, 2012

我怎样才能在mysql中做到这一点？

这是sql服务器还是mysql？它们不是一回事，根据您使用的是哪个DBMS，答案会有所不同。 – 2014-10-06 16:45:41

它是一个MySQL数据库。 – 2014-10-06 19:38:27

答

我有一个工具叫表calendar存储所有日期，一个是可能遇到的（这是一个令人沮丧的小表）...

DROP TABLE IF EXISTS my_table; 

CREATE TABLE my_table 
(serial  INT NOT NULL AUTO_INCREMENT PRIMARY KEY 
,date_from DATE NOT NULL 
,date_to DATE NULL 
); 

INSERT INTO my_table VALUES 
(001,'2012-12-20','2013-01-25'), 
(002,'2012-12-20','2013-01-25'), 
(003,'2012-12-20','2013-01-25'), 
(004,'2013-01-26','2013-04-26'); 


SELECT serial 
    , DATE_FORMAT(dt,'%Y%m') 
    , COUNT(*) total 
    FROM calendar c 
    JOIN my_table x 
    ON c.dt > x.date_from 
    AND c.dt <= date_to 
GROUP 
    BY serial 
    , DATE_FORMAT(dt,'%Y%m'); 

+--------+------------------------+-------+ 
| serial | DATE_FORMAT(dt,'%Y%m') | total | 
+--------+------------------------+-------+ 
|  1 | 201212     | 11 | 
|  1 | 201301     | 25 | 
|  2 | 201212     | 11 | 
|  2 | 201301     | 25 | 
|  3 | 201212     | 11 | 
|  3 | 201301     | 25 | 
|  4 | 201301     |  5 | 
|  4 | 201302     | 28 | 
|  4 | 201303     | 31 | 
|  4 | 201304     | 26 | 
+--------+------------------------+-------+

...或者类似的东西

是一个日历表来拯救!!这个链接有一个日历表的真棒解释：http：//www.brianshowalter.com/calendar_tables – 2014-10-07 18:33:25

答

这是一个有点棘手，你需要另一行源加入。我会用内嵌视图为例，否则，可能来自表..

SELECT t.service, expr, DATE_FORMAT(m._month,'%Y-%m') 
    FROM (SELECT '2012-12-01' AS _month 
      UNION ALL SELECT '2013-01-01' 
      UNION ALL SELECT '2013-02-01' 
      UNION ALL SELECT '2013-03-01' 
      UNION ALL SELECT '2013-04-01' 
     ) m 
    JOIN mytable t 
    ON ... 

WHERE ...

棘手的部分是被获取JOIN权限的表达式（检查日期范围与月份的任何重叠），并获取表达式以返回天数。

像这样的东西应该返回指定的结果：

SELECT d.serial 
    , DATEDIFF(LEAST(d.date_to+INTERVAL 1 DAY,m._month+INTERVAL 1 MONTH) 
       ,GREATEST(d.date_from,m._month) 
     ) AS `days` 
    , DATE_FORMAT(m._month,'%M, %Y') AS `month` 
    -- , d.date_from 
    -- , d.date_to 
    -- , m._month 
    FROM daterange d 
    JOIN (SELECT '2012-11-01' AS _month 
      UNION ALL SELECT '2012-12-01' 
      UNION ALL SELECT '2013-01-01' 
      UNION ALL SELECT '2013-02-01' 
      UNION ALL SELECT '2013-03-01' 
      UNION ALL SELECT '2013-04-01' 
      UNION ALL SELECT '2013-05-01' 
     ) m 
    ON m._month >= DATE_FORMAT(d.date_from,'%Y-%m-01') 
    AND m._month <= DATE_FORMAT(d.date_to ,'%Y-%m-01') 
ORDER BY d.serial, m._month

答

我会计算下个月的第一天，然后计算两个日期之间的差异。

SELECT Serial, 
    DATEDIFF(DATE_FORMAT(date_from + INTERVAL 1 MONTH, '%Y-%m-01'), date_from)-1 AS `days`, 
    DATE_FORMAT(date_from, '%M, %Y') AS `month` 
FROM your_table_name

如何查找mysql中日期范围内每个月的天数？

相关推荐