[数据库/时间] MYSQL 数据查询之案例实践篇

1 案例实践之日期时间

CASE:查询当天/昨天数据

# 当天
select * from table where to_days(时间字段) = to_days(now());

# 昨天
select * from table where to_days(now( ) ) - to_days( 时间字段名) <= 1

CASE:查询近N分钟/周/月/年的数据

# 近5分钟
SELECT * FROM table WHERE 时间字段 >= DATE_SUB(now(),INTERVAL 5 MINUTE)

# 近7天
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY) //查询近七天

# 近一月
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 MONTH)//查询近一月

# 近一年
SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) //查询近一年

CASE:查询本周/月/年数据

# 本周数据（周一为第一天）
SELECT * FROM table WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d'), 1) = YEARWEEK(now(),1);

# 本月
select * from table where DATE_FORMAT(时间字段, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

# 本年
select * from table where YEAR(时间字段)=YEAR(now());

CASE:查询上月/年的数据

# 上一月
select * from table where PERIOD_DIFF(date_format(now(),'%Y%m') , date_format(时间字段,'%Y%m')) =1

# 上一年
select * from table where year(时间字段)=year(date_sub(now(),interval 1 year));

CASE:查询某年度的每个月数据报表

select 
	a.date AS 'xData'
	, IFNULL(b.sum, 0) AS 'yData'
from  (
	select 
		DATE_FORMAT(
			adddate(DATE_SUB(CURDATE(), INTERVAL dayofyear(now()) - 1 DAY),
			INTERVAL numlist.id - 1 month), '%m'
		) as date
	from (
		SELECT 
			@xi := @xi + 1 as id
		from (
			SELECT 1 
			UNION 
			SELECT 2 
			UNION 
			SELECT 3
		) xc1
		, (
			SELECT 1 
			UNION 
			SELECT 2 
			UNION SELECT 3 
			UNION SELECT 4
		) xc2
		, (SELECT @xi := 0) xc0
	) as numlist
) a
left join(
	SELECT  
		IFNULL(SUM(income),0) sum
		, DATE_FORMAT(time, '%m') as date
	FROM zq_cnz_hy_income_report ts
	WHERE YEAR(time)=#{year}
	GROUP BY date 
	ORDER BY date
) b
on a.date = b.date 
order by a.date

CASE:查询近一年的每个月数据报表

SELECT v.month AS 'xData',IFNULL(b.COUNT,0) AS 'yData' FROM (
	SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month`
	UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
) v
LEFT JOIN(
	SELECT 
		LEFT(a.time,7) AS 'month',SUM(income) AS COUNT
	FROM zq_cnz_hy_income_report AS a
	LEFT JOIN zq_cnz_hy_user_station zs ON a.station_id=zs.station_id
	WHERE DATE_FORMAT(a.time,'%Y-%m')>DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m')
	GROUP BY MONTH
) AS b
ON v.month = b.month
GROUP BY v.month 
ORDER BY v.month

CASE:查询本月数据报表

select 
	a.date as 'xData'
	, IFNULL(b.sum, 0) AS 'yData'
from  (
	select 
		date 
	from (
		SELECT 
			DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as date
		FROM (
			SELECT 
				@xi:=@xi+1 as xc 
			from 
				(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1,
				(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,
				(SELECT @xi:=0) xc0
		) xcxc
	) x0 
	where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day))
) a 
left join (
	select 
		IFNULL(SUM(income),0) as sum
		, DATE_FORMAT(time,'%Y-%m-%d') as date
	FROM zq_cnz_hy_income_report ts
	group by DATE_FORMAT(time, '%Y-%m-%d')
) b
on a.date =b.date 
order by a.date

CASE:查询近一个月数据报表

SELECT
	b.created AS 'xData'
	, IFNULL(c.sum, 0) AS 'yData'
FROM (
	SELECT
		@cdate := date_add( @cdate, INTERVAL - 1 DAY ) created
	FROM( 
		SELECT 
			@cdate := date_add( CURDATE( ), INTERVAL 1 DAY ) 
		FROM zq_cnz_hy_income_report 
		LIMIT 30 
	) a
) b
LEFT JOIN (
	select 
		SUM(income) as sum
		, DATE_FORMAT(time,'%Y-%m-%d') as date
	FROM zq_cnz_hy_income_report ts
	group by DATE_FORMAT(time, '%Y-%m-%d')
) c ON b.created = date_format( c.date, '%Y-%m-%d')
GROUP BY b.created
ORDER BY b.created;

2 案例实践之综合案例

CASE 查询以指定分隔符拼接的多元素字段的数据表的最大元素值

查询以指定分隔符拼接的多元素字段的数据表的最大元素值

前置条件

MYSQL 5.7 (不支持开窗函数 / with as 等语法特性)

解决方法

select
	MAX(deviceModelCodesSize) as maxDeviceModelCodesSize
from (
	SELECT 
	  id,
	  configName,
	  configVersion,
	  fileUuid,
	  deviceModelCodes,
	  -- 计算元素数量：逗号数量 + 1（若字段为空则返回0）
	  CASE 
	    WHEN deviceModelCodes = '' or devicemodelcodes is null THEN 0  -- 处理空字符串场景
	    ELSE LENGTH( REPLACE(deviceModelCodes, ' ', '') ) - LENGTH(REPLACE( REPLACE(deviceModelCodes, ' ', '') , ',', '')) + 1 
	  END AS deviceModelCodesSize
	FROM ( -- tb_config
		select 1 as id, "XX-001.xml" as configName, 1 as configVersion, "3534rfee5345erf465" as fileUuid, NULL as deviceModelCodes
		union all
		select 1 as id, "XX-001.xml" as configName, 1 as configVersion, "3534rfee5345erf465" as fileUuid, " " as deviceModelCodes
		union all
		select 1 as id, "XX-001.xml" as configName, 1 as configVersion, "3534rfee5345erf465" as fileUuid, "DM1,DM2,DM3,DM9,DM10" as deviceModelCodes
		union all 
		select 2 as id, "XX-002.xml" as configName, 2 as configVersion, "absd-et5345erfert5" as fileUuid, "DM2,DM3" as deviceModelCodes
		union all 
		select 3 as id, "XX-003.xml" as configName, 3 as configVersion, "absd-et5345e-efer" as fileUuid, "DM3,DM5" as deviceModelCodes
	) t1
) t2

t1

id|configName|configVersion|fileUuid          |deviceModelCodes    |deviceModelCodesSize|
--+----------+-------------+------------------+--------------------+--------------------+
 1|XX-001.xml|            1|3534rfee5345erf465|                    |                   0|
 1|XX-001.xml|            1|3534rfee5345erf465|                    |                   0|
 1|XX-001.xml|            1|3534rfee5345erf465|DM1,DM2,DM3,DM9,DM10|                   5|
 2|XX-002.xml|            2|absd-et5345erfert5|DM2,DM3             |                   2|
 3|XX-003.xml|            3|absd-et5345e-efer |DM3,DM5             |                   2|

t2

maxDeviceModelCodesSize|
-----------------------+
                      5|

扩展：针对数据表 t1 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）

t1
id|configName|configVersion|fileUuid |deviceModelCodes |deviceModelCodesSize|
--+----------+-------------+------------------+--------------------+--------------------+
1|XX-001.xml| 1|3534rfee5345erf465| | 0|
1|XX-001.xml| 1|3534rfee5345erf465| | 0|
1|XX-001.xml| 1|3534rfee5345erf465|DM1,DM2,DM3,DM9,DM10| 5|
2|XX-002.xml| 2|absd-et5345erfert5|DM2,DM3 | 2|
3|XX-003.xml| 3|absd-et5345e-efer |DM3,DM5 | 2|

基于 MYSQL 5.7的查询SQL，输出查询结果，形如:
deviceModelCode|configVersion|configName|fileUuid |id||rank|
---------------+-------------+----------+------------------+--+-----+
DM1 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|
DM10 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|
DM2 | 2|XX-002.xml|absd-et5345erfert5| 2| 1|
DM2 | 1|XX-001.xml|3534rfee5345erf465| 1| 2|
DM3 | 3|XX-003.xml|absd-et5345e-efer | 3| 1|
DM3 | 2|XX-002.xml|absd-et5345erfert5| 2| 2|
DM3 | 1|XX-001.xml|3534rfee5345erf465| 1| 3|
DM5 | 3|XX-003.xml|absd-et5345e-efer | 3| 1|
DM9 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|

解决方案

MYSQL 5.7

SELECT
	deviceModelCode
	, configVersion
	, configName
	, fileUuid
	, id
from (
	SELECT 
	    t3.id,
	    t3.configName,
	    t3.configVersion,
	    t3.fileUuid,
	    SUBSTRING_INDEX(SUBSTRING_INDEX(t3.deviceModelCodes, ',', t4.n), ',', -1) AS deviceModelCode
	FROM ( -- tb_config
		select 1 as id, "XX-001.xml" as configName, 1 as configVersion, "3534rfee5345erf465" as fileUuid, "DM1,DM2,DM3,DM9,DM10" as deviceModelCodes
		union all 
		select 2 as id, "XX-002.xml" as configName, 2 as configVersion, "absd-et5345erfert5" as fileUuid, "DM2,DM3" as deviceModelCodes
		union all 
		select 3 as id, "XX-003.xml" as configName, 3 as configVersion, "absd-et5345e-efer" as fileUuid, "DM3,DM5" as deviceModelCodes
	) t3
	CROSS JOIN (
		SELECT 1 AS n 
	    UNION ALL SELECT 2 
	    UNION ALL SELECT 3 
	    UNION ALL SELECT 4
	    UNION ALL SELECT 5
	) t4
	WHERE 
	    t4.n <= LENGTH(t3.deviceModelCodes) - LENGTH(REPLACE(t3.deviceModelCodes, ',', '')) + 1
	ORDER BY t3.id, t4.n
) t5
group by deviceModelCode, configVersion
order by deviceModelCode asc, configVersion desc

================

针对数据表 t5 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）?

t5

deviceModelCode|configVersion|configName|fileUuid |id|
---------------+-------------+----------+------------------+--+
DM1 | 1|XX-001.xml|3534rfee5345erf465| 1|
DM10 | 1|XX-001.xml|3534rfee5345erf465| 1|
DM2 | 2|XX-002.xml|absd-et5345erfert5| 2|
DM2 | 1|XX-001.xml|3534rfee5345erf465| 1|
DM3 | 3|XX-003.xml|absd-et5345e-efer | 3|
DM3 | 2|XX-002.xml|absd-et5345erfert5| 2|
DM3 | 1|XX-001.xml|3534rfee5345erf465| 1|
DM5 | 3|XX-003.xml|absd-et5345e-efer | 3|
DM9 | 1|XX-001.xml|3534rfee5345erf465| 1|

基于 MYSQL 5.7的查询SQL，输出查询结果，形如:
deviceModelCode|configVersion|configName|fileUuid |id||rank|
---------------+-------------+----------+------------------+--+-----+
DM1 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|
DM10 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|
DM2 | 2|XX-002.xml|absd-et5345erfert5| 2| 1|
DM2 | 1|XX-001.xml|3534rfee5345erf465| 1| 2|
DM3 | 3|XX-003.xml|absd-et5345e-efer | 3| 1|
DM3 | 2|XX-002.xml|absd-et5345erfert5| 2| 2|
DM3 | 1|XX-001.xml|3534rfee5345erf465| 1| 3|
DM5 | 3|XX-003.xml|absd-et5345e-efer | 3| 1|
DM9 | 1|XX-001.xml|3534rfee5345erf465| 1| 1|

解决方案

select 
	deviceModelCode, configVersion, configName, fileUuid, id, `rank`
from (
	SELECT 
	    deviceModelCode,
	    configVersion,
	    configName,
	    fileUuid,
	    id,
	    @rank := IF(@current_device = deviceModelCode, @rank + 1, 1) AS `rank`,
	    @current_device := deviceModelCode
	FROM (
		SELECT 
			* 
	    FROM (
			 SELECT
				deviceModelCode
				, configVersion
				, configName
				, fileUuid
				, id
			from (
				SELECT 
				    t1.id,
				    t1.configName,
				    t1.configVersion,
				    t1.fileUuid,
				    SUBSTRING_INDEX(SUBSTRING_INDEX(t1.deviceModelCodes, ',', t2.n), ',', -1) AS deviceModelCode
				FROM ( -- tb_config
					select 1 as id, "XX-001.xml" as configName, 1 as configVersion, "3534rfee5345erf465" as fileUuid, "DM1,DM2,DM3,DM9,DM10" as deviceModelCodes
					union all 
					select 2 as id, "XX-002.xml" as configName, 2 as configVersion, "absd-et5345erfert5" as fileUuid, "DM2,DM3" as deviceModelCodes
					union all 
					select 3 as id, "XX-003.xml" as configName, 3 as configVersion, "absd-et5345e-efer" as fileUuid, "DM3,DM5" as deviceModelCodes
				) t1
				CROSS JOIN ( -- max = 5(按需调整)
					SELECT 1 AS n 
				    UNION ALL SELECT 2 
				    UNION ALL SELECT 3 
				    UNION ALL SELECT 4
				    UNION ALL SELECT 5
				) t2
				WHERE 1=1
				    and t2.n <= LENGTH(t1.deviceModelCodes) - LENGTH(REPLACE(t1.deviceModelCodes, ',', '')) + 1
				ORDER BY t1.id, t2.n
			) t3
			group by deviceModelCode, configVersion
			order by deviceModelCode asc, configVersion desc
	    ) as t4
	    ORDER BY deviceModelCode, configVersion DESC
	) AS sorted
	, ( SELECT @current_device := '', @rank := 0 ) AS vars
	
	ORDER BY deviceModelCode, configVersion desc
) t5
where 1=1
-- 	and `rank` = 1

CASE SQL中的引号问题

情况1：SQL中含2个单引号

主要解决：SQL中有文本需要使用单引号

达成效果：执行SQL脚本后，数据库最终自动保留1个引号 (如下述SQL脚本执行后，数据库中存储的为 `select ... and sys_code = '{{sysCode}}' and device_code = '{{deviceCode}}')

Demo : 待执行的SQL脚本

insert INTO ds_api_version(api_id, api_version, sql_template) 
values ( '100001' , 'v1.0' , '
	SELECT
		device_id
		, signal_name
		, signal_value
	FROM bdp_dwd.dwd_device_signal_ri_d
	WHERE 1 = 1
		and sys_code = ''{{sysCode}}''
		and device_code = ''{{deviceCode}}''
')

Demo : 执行后的数据库存储效果

	SELECT
		device_id
		, signal_name
		, signal_value
	FROM bdp_dwd.dwd_device_signal_ri_d
	WHERE 1 = 1
		and sys_code = '{{sysCode}}'
		and device_code = '{{deviceCode}}'

情况2：SQL中含2个双引号(单引号/双引号/转义的双引号)

达成效果：执行SQL脚本后，数据库最终原样保留2个双引号，且支持自动转换\"转义符 (如下述SQL脚本执行后，数据库中存储的为 `select ... and sys_code = '{{sysCode}}' and device_code = '{{deviceCode}}')

Demo : 待执行的SQL脚本

insert INTO ds_api_version(api_id, api_version, sql_template) 
values ( '100001' , 'v1.0' , '
	SELECT
		device_id
		, signal_name
		, signal_value
	FROM bdp_dwd.dwd_device_signal_ri_d
	WHERE 1 = 1
		and platform_code= ""{{platformCode}}""
		and device_code = \"\"{{deviceCode}}\"\"
')

Demo : 执行后的数据库存储效果

	SELECT
		device_id
		, signal_name
		, signal_value
	FROM bdp_dwd.dwd_device_signal_ri_d
	WHERE 1 = 1
		and platform_code = ""{{platformCode}}""
		and device_code = ""{{deviceCode}}""

CASE 从`deptId`, `parentId`的树节点列表中找出根节点

问题描述

表情况、数据情况。假定有一张表sys_dept表，其内含dept_id 、parent_id、agentId。通过agentId可过滤出1个deptId, parentId的组织的树节点列表

select 
	dept_id 
    , parent_id
    , agent_id 
from sys_dept a
where agent_id = '1'

query result: list_x

dept_id	parent_id	agent_id
100	0	1
101	100	1
103	101	1
200	103	1

查询需求：将list_x中的root节点查询出来。

解决方法

SELECT 
	t2.dept_id
    -- , t2.agent_id
	, t1.lvl
FROM ( 
	SELECT 
		@r AS _id
		, ( SELECT @r := parent_id FROM sys_dept WHERE dept_id = _id ) AS parent_id 
		, @l := @l + 1 AS lvl 
	FROM (
		SELECT 
			@r := ( select dept_id from sys_dept where agent_id = '1' ORDER BY RAND() limit 1 )  -- 目标树中，任意取1个节点ID
		    --  @r := '{deptId}' -- 树中的任意节点ID 
			, @l := 0
	) vars, sys_dept 
	WHERE @r != 0
) t1 
JOIN sys_dept t2 ON t1._id = t2.dept_id 
where 1 = 1 
  -- t2.and agent_id is null -- 添加任意过滤条件
ORDER BY t1.lvl DESC
-- limit 1

食用方式：再在最后加个过滤条件————始终只取第1行查询结果即可。

query result : (由于是从list_x中随机取的1条，所以每次查询的结果行数都会有变化。但始终第1条————root节点，始终会存在的)

参考文献

如何通过sql查找所有父节点和所有子节点(以mysql为例) - 51脚本

Y 推荐文献

X 参考文献

使用mysql查询当天、近一周、近一个月及近一年的数据以及各种报表查询sql - CSDN

posted @ 2024-06-13 14:45 千千寰宇阅读(1085) 评论(0) 收藏举报

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库/时间] MYSQL 数据查询之案例实践篇

1 案例实践之日期时间

CASE:查询当天/昨天数据

CASE:查询近N分钟/周/月/年的数据

CASE:查询本周/月/年数据

CASE:查询上月/年的数据

CASE:查询某年度的每个月数据报表

CASE:查询近一年的每个月数据报表

CASE:查询本月数据报表

CASE:查询近一个月数据报表

2 案例实践之综合案例

CASE 查询以指定分隔符拼接的多元素字段的数据表的最大元素值

查询以指定分隔符拼接的多元素字段的数据表的最大元素值

扩展：针对数据表 t1 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）

针对数据表 t5 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）?

CASE SQL中的引号问题

情况1：SQL中含2个单引号

情况2：SQL中含2个双引号(单引号/双引号/转义的双引号)

CASE 从`deptId`, `parentId`的树节点列表中找出根节点

问题描述

解决方法

参考文献

Y 推荐文献

X 参考文献

公告

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。

[数据库/时间] MYSQL 数据查询之案例实践篇

1 案例实践之日期时间

CASE:查询当天/昨天数据

CASE:查询近N分钟/周/月/年的数据

CASE:查询本周/月/年数据

CASE:查询上月/年的数据

CASE:查询某年度的每个月数据报表

CASE:查询近一年的每个月数据报表

CASE:查询本月数据报表

CASE:查询近一个月数据报表

2 案例实践之综合案例

CASE 查询以指定分隔符拼接的多元素字段的数据表的最大元素值

查询以指定分隔符拼接的多元素字段的数据表的最大元素值

扩展：针对数据表 t1 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）

针对数据表 t5 ，如何按 deviceModelCodes 字段的元素 deviceModelCode 分组，并以 configVersion 做组内的降序排序、和组内编号（从1开始）?

CASE SQL中的引号问题

情况1：SQL中含2个单引号

情况2：SQL中含2个双引号(单引号/双引号/转义的双引号)

CASE 从deptId, parentId的树节点列表中找出根节点

问题描述

解决方法

参考文献

Y 推荐文献

X 参考文献

公告

CASE 从`deptId`, `parentId`的树节点列表中找出根节点