【sql】笔记大屏展示数据查询

常见一个小功能，配置十几个的时候就需要整理一下了。数据查询都是要查询：近7天、近30天、近半年、近一年，写个大量语句也就很麻烦，倒不如统一整理起来。

需求效果

如果是【day_to_7 day_to_30】需要格式化年 DATE_FORMAT(last_handle_time, ‘%m/%d’) AS count_by_date
如果是【moth_to_6 moth_to_12 】需要格式化年 DATE_FORMAT(last_handle_time, ‘%Y/%m’) AS count_by_date
下次你想要的哪个阶段数据就这里切换 day_to_7 day_to_30 moth_to_6 moth_to_12
在这里插入图片描述

优化写法

with day_to_7 as (
固定近7天,无需变动
), day_to_30 as (
固定近30天,无需变动
), moth_to_6 as (
固定近半年,无需变动
), moth_to_12 as (
固定近1年,无需变动
), temp as (
业务数据
) select 查询关联

with day_to_7 as (
	-- begin 固定近7天,无需变动
	SELECT  DATE_FORMAT(CURDATE(), '%m/%d') AS item
    UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '%m/%d') AS item
	-- end 固定近7天,无需变动
), day_to_30 as (
	-- end 固定近30天,无需变动
	select DATE_FORMAT(date_add(curdate(), interval(cast(help_topic_id as signed) - 29) day), '%m/%d') AS item
	from mysql.help_topic where help_topic_id < 30 order by help_topic_id
	-- end 固定近30天,无需变动
), moth_to_6 as (
	-- end 固定近6个月,无需变动
	SELECT date_format( date_sub( curdate( ), INTERVAL t.count MONTH ), '%Y/%m' ) AS item FROM ( SELECT t.c AS count 
		FROM (
			SELECT 0 AS c UNION
			SELECT 1 AS c UNION
			SELECT 2 AS c UNION
			SELECT 3 AS c UNION
			SELECT 4 AS c UNION
			SELECT 5 AS c 
			) t 
		) AS t
	-- end 固定近6个月,无需变动
), moth_to_12 as (
	-- end 固定近12个月,无需变动
	SELECT date_format( date_sub( curdate( ), INTERVAL t.count MONTH ), '%Y/%m' ) AS item FROM ( SELECT t.c AS count 
		FROM (
			SELECT 0 AS c UNION
			SELECT 1 AS c UNION
			SELECT 2 AS c UNION
			SELECT 3 AS c UNION
			SELECT 4 AS c UNION
			SELECT 5 AS c UNION
			SELECT 6 AS c UNION
			SELECT 7 AS c UNION
			SELECT 8 AS c UNION
			SELECT 9 AS c UNION
			SELECT 10 AS c UNION
			SELECT 11 AS c 
			) t 
		) AS t
	-- end 固定近12个月,无需变动
), temp as (
	-- begin 你的业务表 TODO
	select 
		COUNT(if(last_handle_code = 200, 1, null)) as succes,
		COUNT(if(last_handle_code = 500, 1, null)) as error,
	DATE_FORMAT(last_handle_time, '%m/%d') AS count_by_date 
	-- 如果是【moth_to_6 moth_to_12 】需要格式化年 DATE_FORMAT(last_handle_time, '%Y/%m') AS count_by_date 
	from job_info where 1=1 and last_handle_time is not null GROUP BY count_by_date
	-- end 你的业务表
) select d.item as date, IFNULL(succes,0) as succes,IFNULL(error,0) as error
-- 下次你想要的哪个阶段数据就这里切换 day_to_7 day_to_30 moth_to_6 moth_to_12 
from day_to_7 d
left join temp on temp.count_by_date = d.item;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

其他写法

近7天变更情况

-- 近7天变更情况
SELECT d.item as date, IFNULL(`succes`,0) as `succes`,IFNULL(`error`,0) as `error` from (
-- begin 固定近7天,无需变动
SELECT  DATE_FORMAT(CURDATE(), '%m/%d') AS item
    UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 DAY), '%m/%d') AS item
    UNION ALL
    SELECT  DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 DAY), '%m/%d') AS item
-- end 固定近7天,无需变动
)  d
left join (
-- begin 你的业务表 TODO
select 
	COUNT(if(last_handle_code = 200, 1, null)) as `succes`,
	COUNT(if(last_handle_code = 500, 1, null)) as `error`,
DATE_FORMAT(last_handle_time, '%m/%d') AS count_by_date 
from job_info where 1=1 and last_handle_time is not null GROUP BY count_by_date
-- end 你的业务表
) temp on temp.count_by_date = d.item;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

近30天变更情况

-- 近30天变更情况
SELECT d.item as date, IFNULL(`succes`,0) as `succes`,IFNULL(`error`,0) as `error` from (
select DATE_FORMAT(date_add(curdate(), interval(cast(help_topic_id as signed) - 29) day), '%m/%d') AS item
from mysql.help_topic
where help_topic_id < 30
order by help_topic_id
)  d
left join (
-- begin 你的业务表 TODO
select 
	COUNT(if(last_handle_code = 200, 1, null)) as `succes`,
	COUNT(if(last_handle_code = 500, 1, null)) as `error`,
DATE_FORMAT(last_handle_time, '%m/%d') AS count_by_date 
from job_info where 1=1 and last_handle_time is not null GROUP BY count_by_date
-- end 你的业务表
) temp on temp.count_by_date = d.item;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

近半年变更情况

-- 近半年变更情况
SELECT d.item as date, IFNULL(`succes`,0) as `succes`,IFNULL(`error`,0) as `error` from (
SELECT
	date_format( date_sub( curdate( ), INTERVAL t.count MONTH ), '%Y/%m' ) AS item 
FROM
	(
	SELECT t.c AS count 
	FROM
		(
		SELECT 0 AS c UNION
		SELECT 1 AS c UNION
		SELECT 2 AS c UNION
		SELECT 3 AS c UNION
		SELECT 4 AS c UNION
		SELECT 5 AS c 
		) t 
	) AS t
)  d
left join (
-- begin 你的业务表 TODO
select 
	COUNT(if(last_handle_code = 200, 1, null)) as `succes`,
	COUNT(if(last_handle_code = 500, 1, null)) as `error`,
DATE_FORMAT(last_handle_time, '%Y/%m') AS count_by_date 
from job_info where 1=1 and last_handle_time is not null GROUP BY count_by_date
-- end 你的业务表
) temp on temp.count_by_date = d.item;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

近一年变更情况

-- 近一年变更情况
SELECT d.item as date, IFNULL(`succes`,0) as `succes`,IFNULL(`error`,0) as `error` from (
SELECT
	date_format(date_sub(curdate(), INTERVAL t.count MONTH ), '%Y/%m' ) AS item 
FROM
	(
	SELECT t.c AS count 
	FROM
		(
		SELECT 0 AS c UNION
		SELECT 1 AS c UNION
		SELECT 2 AS c UNION
		SELECT 3 AS c UNION
		SELECT 4 AS c UNION
		SELECT 5 AS c UNION
		SELECT 6 AS c UNION
		SELECT 7 AS c UNION
		SELECT 8 AS c UNION
		SELECT 9 AS c UNION
		SELECT 10 AS c UNION
		SELECT 11 AS c 
		) t 
	) AS t
)  d
left join (
-- begin 你的业务表 TODO
select 
	COUNT(if(last_handle_code = 200, 1, null)) as `succes`,
	COUNT(if(last_handle_code = 500, 1, null)) as `error`,
DATE_FORMAT(last_handle_time, '%Y/%m') AS count_by_date 
from job_info where 1=1 and job_type in (1404,1406,1408) and last_handle_time is not null GROUP BY count_by_date
-- end 你的业务表
) temp on temp.count_by_date = d.item;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

参考数据

INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (500, ‘2022-06-08 09:35:11’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (500, ‘2022-09-08 09:35:11’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-18 09:33:10’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-18 09:33:26’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-03-18 09:33:38’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-18 09:33:07’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-18 09:33:01’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-09-08 09:33:00’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-12-18 09:33:06’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-10 10:01:36’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-18 16:31:53’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-18 16:53:54’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-18 09:32:59’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-18 16:57:20’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-18 16:59:07’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-05-18 09:32:58’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-19 15:44:01’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-12 16:10:21’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-16 17:24:15’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-26 15:37:36’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-15 17:05:27’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-07-19 14:54:32’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-17 13:38:49’);
INSERT INTO job_info(last_handle_code, last_handle_time) VALUES (200, ‘2022-08-16 16:30:51’);

相关阅读:
Unity中程序集dll
一篇文章快速教你如何搭建数据驱动自动化测试框架？
网络安全笔记 -- 文件操作（文件包含漏洞）
NVIDIA CUDA Win10安装步骤
 Web 性能优化：TLS
通过Xshell7连接云服务Linux系统级上传文件
 开源开放 | DeepOnto: 基于深度学习和语言模型的本体工程Python软件包
 【JavaScript】String对象知识全解
 计算机专硕变简单，只考一门数据结构！陕西理工大学计算机考研改考
 5. 【非递归版】先序、中序、后序遍历 + 求数的深度（用层序遍历实现）
原文地址：https://blog.csdn.net/u010638673/article/details/126767500