SQL高级嵌套

写了很多复杂的SQL脚本和存储过程，每次都需要花很长的时间去熟悉mysql的对应函数使用，所以很麻烦，记下来，以便后续少走弯路，提供自己和各位网友参考。

注意：

这里要非常留意，MYSQL单机环境下和MYSQL集群环境下（这里我用的MyCat数据中间件）出来的效果不一样，可能会检索出来多条记录，比如，不同的数据库执行各自的存储过程都会有一条或多条记录，最终返回给上层应用2倍的数据查询集；

以下语句为“落脚点分析”查询相关的脚本，条件：

a. 按每一天进行分组

b. 从每天的抓取记录中获取最早的一条和最晚的一条

c. 将一天抓取的最早一条和最晚一条合并为一条（一天当中出行时间最早一条和回来时间段最晚一条）

 

思路实现如下：

（1）多语句嵌套

select temp.df, temp.alarmTime from
(
	select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName, a.wifi 
	from t_wifi_alarm a 
	where a.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
	order by df
) temp where temp.df='2019-04-10' and temp.alarmTime = min(temp.alarmTime);

（2）查询分组最小

方式一：通过最小值嵌套查
select b.df, b.alarmTime from
(
	select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName 
	from t_wifi_alarm a 
	where a.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
	order by df
) b where b.df='2019-04-10' and b.alarmTime = 
(
	select min(d.alarmTime) 
	from t_wifi_alarm d
	where d.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(d.alarmTime,'%H:%i:%S') >='08:10:10'
);


方式二：通过升序查第一条

select b.df, b.alarmTime from
(
	select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName 
	from t_wifi_alarm a 
	where a.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
	order by df
) b where b.df='2019-04-10' 
order by b.alarmTime ASC
limit 1;

（3）查询分组最大

方式一：通过最大值嵌套查
select b.df, b.alarmTime from
(
	select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName 
	from t_wifi_alarm a 
	where a.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
	order by df
) b where b.df='2019-04-10' and b.alarmTime = 
(
	select max(x.alarmTime) 
	from t_wifi_alarm x
	where x.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(x.alarmTime,'%H:%i:%S') >='08:10:10'
)
#limit 1;

方式二：通过降序查第一条
select b.df, b.alarmTime from
(
	select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName 
	from t_wifi_alarm a 
	where a.wifi='65-05-14-0f-84-07'
	and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
	order by df
) b where b.df='2019-04-10'
order by b.alarmTime desc 
limit 1

（3）多条记录合并

select m.df, m.alarmTime, n.df, n.alarmTime from
(
	select c.df, c.alarmTime from 
	(
		select b.df, b.alarmTime from
		(
			select a.id, DATE_FORMAT(a.alarmTime,'%Y-%m-%d') as df, a.alarmTime, a.deviceName 
			from t_wifi_alarm a 
			where a.wifi='65-05-14-0f-84-07'
			and DATE_FORMAT(a.alarmTime,'%H:%i:%S') >='08:10:10'
			order by df
		) b where b.df='2019-04-10' 
		order by b.alarmTime asc
		limit 1
	) c
	order by c.alarmTime
	limit 1
) m join 
(
	select f.df, f.alarmTime from 
	(
		select e.df, e.alarmTime from
		(
			select d.id, DATE_FORMAT(d.alarmTime,'%Y-%m-%d') as df, d.alarmTime, d.deviceName 
			from t_wifi_alarm d 
			where d.wifi='65-05-14-0f-84-07'
			and DATE_FORMAT(d.alarmTime,'%H:%i:%S') >='08:10:10'
			order by df
		) e where e.df='2019-04-10'
		order by e.alarmTime desc 
		limit 1
	) f
	order by f.alarmTime desc
	limit 1
) n on m.df=n.df;

（4）单机存储过程实现思路

这里仅仅有思路，没有具体实现

DELIMITER ||

drop procedure if exists query_first_and_last_wifi_in_one_day ||

create procedure query_first_and_last_wifi_in_one_day (in start_date_time varchar(32), in end_date_time varchar(32), in start_time_in_one_day varchar(16), in end_time_in_one_day varchar(16), in wifi varchar(32))
begin     
	
		-- 参数声明
		declare p_view_name varchar(255) default ''; 
		declare p_start_day varchar(32) default '';  
		declare p_end_day varchar(32) default ''; 
		declare p_cur_day varchar(32) default ''; 

		-- 临时视图名
		set p_view_name = CONCAT('wifi_', DATE_FORMAT(a.alarmTime,'%Y-%m-%d_%H_%i_%S'));

		set p_start_day = DATE_FORMAT(start_date_time,'%Y-%m-%d');
		set p_cur_day = DATE_FORMAT(start_date_time,'%Y-%m-%d');
		set p_end_day = DATE_FORMAT(end_date_time,'%Y-%m-%d');
	
  	-- 创建查询结果视图
    set @sql = CONCAT(' create view ', p_view_name, 
										  ' as select DATE_FORMAT(a.alarmTime,\'%Y-%m-%d\') as df, a.alarmTime, a.deviceName from t_wifi_alarm a where a.wifi=\'' , wifi, 
											'\' and a.alarmTime >=\'', start_date_time, 
											'\' and a.alarmTime <=\'', end_date_time, '\''); 
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
		DEALLOCATE PREPARE stmt;

		-- 遍历所有天
    while p_cur_day <= p_end_day do

			-- 选择第一次出现时间-满足出行时间
			-- select time from view where time ='xxx';
			set @sql=CONCAT('select alarmTime, deviceName from ', p_view_name ,' where alarmTime = \'', p_cur_day, '\' order by alarmTime asc limit 1');
			-- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (',p_description,'))');
			-- 打印sql变量
			select @sql;
			-- 准备sql语句
			PREPARE stmt from @sql;
			-- 执行sql语句
			EXECUTE stmt;

			-- 选择最后一次出现时间-满足回家区间
			-- select time from view where time ='xxx';
			set @sql=CONCAT('select alarmTime, deviceName from ', p_view_name ,' where alarmTime = \'', p_cur_day, '\' order by alarmTime desc limit 1');
			-- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (',p_description,'))');
			-- 打印sql变量
			select @sql;
			-- 准备sql语句
			PREPARE stmt from @sql;
			-- 执行sql语句
			EXECUTE stmt;
			-- 释放资源
			DEALLOCATE PREPARE stmt;

			-- 合并一条记录，见上合并步骤

			-- 递增变量
			set p_cur_day = DATE_ADD(p_cur_day, interval 1 day); 

    end while; 


		-- 删除视图
		set @sql = CONCAT(' drop view if exists ', p_view_name); 
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	
end ||

-- 恢复语句中断符
DELIMITER ;

（5）数据库集群

集群环境下，存储过程是无法实现的，思路有如下

a、编程实现，将部分结果放内存，然后合并成多条记录返回---大数据情况下，普通内存无法做到

b、大数据Hadoop的MapReduce和Spark实现

 

快来成为我的朋友或合作伙伴，一起交流，一起进步！
QQ群：961179337
微信：lixiang6153
邮箱：lixx2048@163.com
公众号：IT技术快餐
更多资料等你来拿！