mysql树查询和时间段查询

2023-12-28 17:10:23
本文目录

摘要

在这里插入图片描述

案例1:MySQL树形结构查询

在页面开发过程中,如图一所示的树形控件很常见,而大多数情况下,树形控件中需要显示的数据事先在数据库中通过parent_id的形式配置好,需要的时候再查询对应的数据,但此时所有的查询条件一般又只有一个最顶层的id

这时需要通过这个最顶层的id获取到它的所有子级数据,这种查询在Oracle数据库中可以通过start with t.id=#{id} connect by prior t.id=t.parent_id这种形式实现,MySQL8.1以上的版本也提供了这种形式,但在较低版本中,这种形式还不支持,需要通过其他的方法实现。

这种方法需要实现创建一个函数,用于获取到此顶层id所对应的所有子级数据的id,之后通过find_in_set()函数查询所有数据

在这里插入图片描述

图一:树形控件

主要步骤如下:

  1. 新建函数,用户查询所有子级数据
drop function if exists get_children;
create function get_children(id varchar(64)) returns varchar(1000)
-- 获取所有子级数据的ID,逗号分隔
begin
	declare res varchar(1000);
	declare temp varchar(100);
	
	set res=id;
	select group_concat(test_id) into temp from test where find_in_set(test_id,id)>0;
	
	while temp is not null do 
		set res=concat(res,',',temp);
		select group_concat(test_id) into temp from test where find_in_set(test_id,temp)>0;
	end while;
	
	return res;
end;
  1. sql
select * from test where find_in_set(test_id,get_children('testId'));

案例2:MySQL查询一段时间内的所有日期

另一种比较常见的控件形式是统计图表,在统计图表中经常会遇到按时间统计数据的逻辑,如图二所示,而在数据库层面通常不是每天都有数据,这种情况下查询出的结果中间可能会中断。需要先获取到统计时间内的所有日期,再根据关联查询的形式获取到所有日期的的数据。

在这里插入图片描述

图二:日期统计图

同样的,这种获取一段时间内所有日期的查询,在Oracle数据库中可以通过一行查询轻松实现,但在MySQL中没有内置这种功能。这里我们可以通过使用中间表的形式实现。

Oracle中的查询方式为:

select to_char(to_date('2019-01-01','yyyy-mm-dd')+level-1,'yyyy-mm-dd') temp_date from dual connect by to_date('2019-01-01','yyyy-mm-dd')+level-1 <= to_date('2019-02-01','yyyy-mm-dd');

主要步骤如下:

  1. 创建中间表,并按需求生成数据,如10000条
create table temp_calender(
	id int auto_increment primary key
) comment '日期区间中间表';

drop procedure is exists add_calenders;
create procedure add_calenders() begin
	declare temp int default 1;
	while temp <= 10000 do
		insert into temp_calender(id) values (temp);
		set temp = temp + 1;
	end while;
	commit;
end;
call add_calenders;
  1. sql
select date_format(date_add('2018-01-01',interval id-1 day),'%Y-%m-%d') as temp_day from temp_calender where date_add('2019-01-01',interval id-1 day) <= '2019-02-01' order by temp_day;

在这种实现方式下,中间日期能生成最大的数量取决于中间表temp_calender中数据的数量(此例中为10000),虽然存在不足,但在大多数业务中已经够用了。

文章来源:https://blog.csdn.net/qq_43408971/article/details/135194912
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。