mysql树查询和时间段查询

本文目录

摘要

案例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;

2. 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;

2. 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），虽然存在不足，但在大多数业务中已经够用了。