我对MySql不够熟悉,不知道这是否会移植,但是这里有一个经过测试和确认的SQL Server解决方案。 The fiddle link is here for your use. 给定开始日期2018年2月20日和2020年2月20日,结果集如下: | 年份|周期开始|期末|周期内天数| | - ------|- ------|- ------|- ------| | 二〇一八年|2018年2月20日|2018年12月31日|三一四| | 二○一九年|2019年1月1日|2019年12月31日|三六五| | 小行星2020|2020年1月1日|2020年2月20日|五十一|
Declare @StartDate date = '2018-02-20', @EndDate date = '2020-02-20';
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)),
Years AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Year
FROM x ones, x tens, x hundreds, x thousands)
SELECT Years.Year,
CASE
WHEN Year(@StartDate) = Years.year THEN @StartDate
ELSE DATEFROMPARTS(years.year, 01, 01)
END AS periodStart,
CASE
WHEN Year(@EndDate) = Years.year THEN @EndDate
ELSE DATEFROMPARTS(years.year, 12, 31)
END AS periodEnd,
DATEDIFF(day,
CASE
WHEN Year(@StartDate) = Years.year THEN @StartDate
ELSE DATEFROMPARTS(years.year, 01, 01)
END,
CASE
WHEN Year(@EndDate) = Years.year THEN @EndDate
ELSE DATEFROMPARTS(years.year, 12, 31)
END
) + 1 AS DaysInPeriod
FROM Years
WHERE Years.Year >= Year(@StartDate)
AND Years.Year <= Year(@EndDate)
WITH RECURSIVE dates AS
(
SELECT min(start_date) as start_date, DATE_FORMAT(min(start_date),'%Y-12-31') as last_day FROM mytable
UNION ALL
SELECT DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01'),
DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-12-31')
FROM dates
WHERE DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01') <= (SELECT MAX(end_date) FROM mytable)
),
cte2 as (
SELECT d.start_date as start_day, if(YEAR(d.start_date) = YEAR(m.end_date), m.end_date, d.last_day) as last_day
FROM dates d, mytable m
)
select *, DATEDIFF(last_day, start_day)+1 as total_days
from cte2;
3条答案
按热度按时间0s7z1bwu1#
我对MySql不够熟悉,不知道这是否会移植,但是这里有一个经过测试和确认的SQL Server解决方案。
The fiddle link is here for your use.
给定开始日期2018年2月20日和2020年2月20日,结果集如下:
| 年份|周期开始|期末|周期内天数|
| - ------|- ------|- ------|- ------|
| 二〇一八年|2018年2月20日|2018年12月31日|三一四|
| 二○一九年|2019年1月1日|2019年12月31日|三六五|
| 小行星2020|2020年1月1日|2020年2月20日|五十一|
cqoc49vn2#
使用
WITH RECURSIVE
创建日期范围,然后我们可以使用DATEDIFF
轻松计算每年的天数Demo here
blpfk2vs3#
您正在查找DATEDIFF函数。
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff
DATEDIFF()返回expr 1-expr 2,表示为从一个日期到另一个日期的天数。expr 1和expr 2是日期或日期时间表达式。
您可以自由指定例如“2019-01-01”或“2020-01-01”作为DATEDIFF的输入参数。
如果希望SELECT在几年内循环并报告每年的天数,您可能会发现在日历报告表中存储几个1月1日日期非常方便。