【mysql】存储过程实现自然周

自然周

-- 使用前先删除存储过程，因为创建存储过程后，存储过程会一直存在于mysql中
DROP PROCEDURE IF EXISTS test_procedure_insert_week;
-- 标记上面的命令执行结束
#声明;;为结束标志
DELIMITER;;
-- 创建存储过程
CREATE PROCEDURE test_procedure_insert_week()
BEGIN
DECLARE
week_start_day DATE;
DECLARE
year_start DATE;
DECLARE
year_end DATE;
DECLARE
next_year DATE;
DECLARE
week_count INT;
DECLARE
YEAR INT;
DECLARE
week_end_day DATE;
SET
week_start_day = '2016-01-01';
SET
week_count = 1;
SET
YEAR = YEAR(week_start_day);
SET
year_start = '2016-01-01';
WHILE
week_start_day < '2026-01-01'
DO
SET
next_year = STR_TO_DATE(CONCAT((YEAR + 1), '-01-01'), '%Y-%m-%d');
SET
week_start_day = IF(week_start_day >= next_year, next_year, week_start_day);
SET
year_start = IF(week_start_day >= next_year, next_year, year_start);
SET
YEAR = IF(week_start_day >= next_year, YEAR + 1, YEAR);
SET
week_count = IF(week_start_day >= next_year, 1, week_count);
SET
week_end_day = IF(DAYOFWEEK(week_start_day) = 1, week_start_day,
IF(DAYOFWEEK(week_start_day) = 2, DATE_ADD(week_start_day, INTERVAL 6 DAY),
IF(DAYOFWEEK(week_start_day) = 3, DATE_ADD(week_start_day, INTERVAL 5 DAY),
IF(DAYOFWEEK(week_start_day) = 4, DATE_ADD(week_start_day, INTERVAL 4 DAY),
IF(DAYOFWEEK(week_start_day) = 5, DATE_ADD(week_start_day, INTERVAL 3 DAY),
IF(DAYOFWEEK(week_start_day) = 6,
DATE_ADD(week_start_day, INTERVAL 2 DAY),
IF(DAYOFWEEK(week_start_day) = 7,
DATE_ADD(week_start_day, INTERVAL 1 DAY)
, week_start_day)
)
)
)
)
)
);
SET week_end_day = IF(week_end_day >= next_year and week_start_day < next_year, LAST_DAY(week_start_day), week_end_day);
#
set
# week_end_day = IF(week_start_day >= next_year, DATE_ADD(week_start_day, INTERVAL 6 DAY),
#
IF(DATE_ADD(week_start_day, INTERVAL 6 DAY) >= next_year, LAST_DAY(week_start_day),
#
DATE_ADD(week_start_day, INTERVAL 6 DAY))); -- 注意2016-03-01的情况
INSERT INTO `kwan`.`ads_dim_natural_year_week_info` ( financial_year
, financial_year_week
, financial_year_start
, financial_year_end
, week_start_day
, week_end_day)
VALUES ( YEAR
, week_count
, year_start
, DATE_ADD(DATE_ADD(year_start, INTERVAL 1 YEAR), INTERVAL -1 DAY)
, week_start_day
, week_end_day);
SET
week_start_day = DATE_ADD(week_end_day, INTERVAL 1 DAY);
SET
week_count = week_count + 1;
END WHILE;
END;;
-- 调用无参的存储过程，当然也可以设置参数，调用时也需要传递参数
CALL test_procedure_insert_week();
-- 周日为1 周六为7
周2为3
SELECT DAYOFWEEK(NOW());

最后

以上就是孝顺镜子为你收集整理的【mysql】存储过程实现自然周的全部内容，希望文章能够帮你解决【mysql】存储过程实现自然周所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。