mysql根据时间格式生成x轴

步骤

1、根据日期范围遍历生成每天日期的view

其中a,b,c,d分别为个十百千的位数，下面是一个生成一个 个十百千的0到9的一个笛卡尔积
结果为9999/365≈27 一个时间为前22年到后5年的一个时间范围日期

create or replace
algorithm = UNDEFINED view `date_range_view` as
select
    ((curdate() + interval 5 year) - interval (((`a`.`a` + (10 * `b`.`a`)) + (100 * `c`.`a`)) + (1000 * `d`.`a`)) day) as `Date`
from
    (((((
    select
        0 as `a`)
union all
select
    1 as `1`
union all
select
    2 as `2`
union all
select
    3 as `3`
union all
select
    4 as `4`
union all
select
    5 as `5`
union all
select
    6 as `6`
union all
select
    7 as `7`
union all
select
    8 as `8`
union all
select
    9 as `9`) `a`
join (
    select
        0 as `a`
union all
    select
        1 as `1`
union all
    select
        2 as `2`
union all
    select
        3 as `3`
union all
    select
        4 as `4`
union all
    select
        5 as `5`
union all
    select
        6 as `6`
union all
    select
        7 as `7`
union all
    select
        8 as `8`
union all
    select
        9 as `9`) `b`)
join (
    select
        0 as `a`
union all
    select
        1 as `1`
union all
    select
        2 as `2`
union all
    select
        3 as `3`
union all
    select
        4 as `4`
union all
    select
        5 as `5`
union all
    select
        6 as `6`
union all
    select
        7 as `7`
union all
    select
        8 as `8`
union all
    select
        9 as `9`) `c`)
join (
    select
        0 as `a`
union all
    select
        1 as `1`
union all
    select
        2 as `2`
union all
    select
        3 as `3`
union all
    select
        4 as `4`
union all
    select
        5 as `5`
union all
    select
        6 as `6`
union all
    select
        7 as `7`
union all
    select
        8 as `8`
union all
    select
        9 as `9`) `d`);

2、根据日期格式拼接日期

SELECT
	T1.DATE_VALUE,
	T1 DATE_ALIAS,
	`其他需要展示的数据`
FROM
	(
	SELECT
		DATE_FORMAT(DATE, EMPTY(:DATE_TYPE, '%Y-%m')) DATE_VALUE,
		CASE WHEN EMPTY(:DATE_TYPE,
		'%Y-%m') = '%Y-%m-%d' THEN CONCAT(DATE_FORMAT(DATE, '%d'), '日')
		WHEN EMPTY(:DATE_TYPE,
		'%Y-%m') = '%x-%v' THEN CONCAT(DATE_FORMAT(DATE, '%v'), '周')
		ELSE CONCAT(DATE_FORMAT(DATE, '%c'), '月') END AS DATE_ALIAS
	FROM
		DATE_RANGE_VIEW
	WHERE
		1 = 1
		AND DATE_FORMAT(DATE, EMPTY(:DATE_TYPE, '%Y-%m')) BETWEEN DATE_FORMAT(EMPTY(:START_DATE, DATE_FORMAT(ADDDATE(LAST_DAY(ADDDATE(CURDATE() , INTERVAL -1 YEAR)), INTERVAL 1 DAY), '%Y-%m-%d')), EMPTY(:DATE_TYPE, '%Y-%m')) AND DATE_FORMAT(EMPTY(:END_DATE, DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d')), EMPTY(:DATE_TYPE, '%Y-%m'))
	GROUP BY
		DATE_FORMAT(DATE, EMPTY(:DATE_TYPE, '%Y-%m'))
	ORDER BY
		DATE_VALUE ) T1
LEFT JOIN (`需要串联的表` ) T2 ON
	T1.DATE_VALUE = T2.DATE_VALUE

3、获取时间所在的月的周

SET @d=NOW();
SELECT (DAY(@d)+WEEKDAY(@d-INTERVAL DAY(@d) DAY)) DIV 7 ;

https://blog.csdn.net/weixin_29384119/article/details/113688415 WEEKDAY函数解析