用Mysql语句实现报表,写一个函数,给定三个参数,年份,月份,该月份的第几周,计算出该周的起止日期。如果该月1号不在周一,则返回1号所在周几到当前周日结束。如果该月最后一天不在周日,则显示最后一天对应的周几
时间: 2024-05-25 16:17:36 浏览: 17
。以下是MySQL函数的实现:
DELIMITER //
CREATE FUNCTION get_week_range(year INT, month INT, week_num INT) RETURNS VARCHAR(100)
BEGIN
DECLARE first_day DATE;
DECLARE last_day DATE;
DECLARE start_day DATE;
DECLARE end_day DATE;
DECLARE week_start INT;
DECLARE week_end INT;
SET first_day = CONCAT(year, '-', month, '-01');
SET last_day = LAST_DAY(first_day);
SET week_start = (WEEKDAY(first_day) + 1) % 7;
SET week_end = 6 - WEEKDAY(last_day);
IF week_num = 1 THEN
SET start_day = first_day + INTERVAL week_start DAY;
SET end_day = start_day + INTERVAL 6 DAY;
ELSEIF week_num = WEEKOFMONTH(last_day) THEN
SET end_day = last_day - INTERVAL week_end DAY;
SET start_day = end_day - INTERVAL 6 DAY;
ELSE
SET start_day = first_day + INTERVAL (week_num - 2) * 7 + week_start DAY;
SET end_day = start_day + INTERVAL 6 DAY;
END IF;
RETURN CONCAT(start_day, ' - ', end_day);
END//
DELIMITER ;
使用方法:
SELECT get_week_range(2021, 9, 2); -- 返回 2021-09-06 - 2021-09-12
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)