Mysql存储过程，查询一段时间的数据。

2016-09-06 09:55:52 837 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MySQL中间件（一）：sql基础、mysql体系结构、crud指令、高级查询 4.2.1 SQL语句，索引，视图，存储过程，触发器阅读详情

有一个这样的表表名是history_detail，字段有ID,ASN,PR_NAME,DATE,OPERATOR,RESULT,PSN,REMARK.
需要通过查询指定时间段中的PR_NAME一共有多少条,

如：2015-1-1 2015-1-2

数据时间每三分钟计算一下有多少条指定PR_NAME的数据量

如：2015-1-1 00:00:00 2015-1-1 00:03:00 (已经有的数据时间，不是定时查询什么的)

需要实现的结果一个Mysql存储过程传入3个参数（process,startTime,endTime）
需要得到每个时间段对应的数据量比如：查询一个小时的数据，一共分为20个点，每个点有对应查询出来的数据，
我要传到web端做曲线图。。
MYSQL能这么查询么查询出每个时间点对应的数据量
如：00:00:00 - 00:03:00 工序为xx的数量有xx个
00:03:00 - 00:06:00 工序为xx的数量有xx个（一个时间段的相同工序，用那个循环）

各位大大们- - ......先谢谢你们了........

...全文

HFSS与AI融合：智能算法如何重塑天线设计流程本文探讨了HFSS与AI融合如何通过智能算法革新天线设计流程。在5G/6G通信时代，AI技术如机器学习和遗传算法显著提升了高频电磁场仿真效率，将传统设计周期从数周缩短至数小时，并优化了天线性能。文章详细介绍了AI与HFSS的集成策略、关键技术突破及典型应用案例，展示了智能算法在天线设计中的巨大潜力。阅读详情

qq_34088321

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

中国风 2016-09-13

打赏
举报

少一个位置，再测一下

DELIMITER //

drop procedure  if exists selectCount;//

 CREATE PROCEDURE selectCount(p VARCHAR(20),n DATETIME,o DATETIME )

BEGIN

DECLARE a INT ;

DECLARE b DATETIME;

SET a =0;



SET a = (SELECT TIMESTAMPDIFF(MINUTE,n,o));

IF a <= 3*60 THEN 

INSERT INTO tb1 (s1, s2)

SELECT COUNT(a.DATE),(n + INTERVAL b.help_topic_id*3 MINUTE) 

FROM history_detail as a RIGHT JOIN mysql.help_topic as b  

ON   a.DATE BETWEEN n+INTERVAL (b.help_topic_id-1)*3 MINUTE AND n + INTERVAL b.help_topic_id*3 MINUTE  AND a.result='合格' AND a.pr_name=p

where  b.help_topic_id>=1 AND n+INTERVAL (b.help_topic_id-1)*3 MINUTE < o

group by  b.help_topic_id;



ELSEIF a>3*60 AND a <= 72*60 THEN

INSERT INTO tb1 (s1, s2)

SELECT COUNT(a.DATE),(n + INTERVAL b.help_topic_id HOUR) 

FROM history_detail as a RIGHT JOIN mysql.help_topic as b  

ON  a.DATE BETWEEN n+INTERVAL (b.help_topic_id-1) HOUR AND n + INTERVAL b.help_topic_id HOUR AND a.result='合格' AND a.pr_name=p

 where  b.help_topic_id>=1 AND n+INTERVAL (b.help_topic_id-1) HOUR < o

group by  b.help_topic_id;





ELSEIF a>72*60 AND a <= 30*24*60 THEN

INSERT INTO tb1 (s1, s2)

SELECT COUNT(a.DATE),(n + INTERVAL b.help_topic_id DAY) 

FROM history_detail as a RIGHT JOIN mysql.help_topic as b  

ON  a.DATE BETWEEN n+INTERVAL (b.help_topic_id-1) DAY AND n + INTERVAL b.help_topic_id DAY  AND a.result='合格' AND a.pr_name=p

where b.help_topic_id>=1 AND  n+INTERVAL (b.help_topic_id-1) DAY < o

group by  b.help_topic_id;





ELSEIF a>30*24*60 AND a <= 365*24*60 THEN

INSERT INTO tb1 (s1, s2)

SELECT COUNT(a.DATE),(n + INTERVAL b.help_topic_id MONTH) 

FROM history_detail as a

RIGHT JOIN mysql.help_topic as b  

ON   a.DATE BETWEEN n+INTERVAL (b.help_topic_id-1) MONTH AND n + INTERVAL b.help_topic_id MONTH AND a.result='合格' AND a.pr_name=p

 where  b.help_topic_id>=1 AND n+INTERVAL (b.help_topic_id-1) MONTH < o

group by  b.help_topic_id;





ELSEIF a>365*24*60 THEN

INSERT INTO tb1 (s1, s2)

SELECT COUNT(a.DATE),(n + INTERVAL b.help_topic_id YEAR) 

FROM history_detail as a

RIGHT JOIN mysql.help_topic as b  

ON   a.DATE BETWEEN n+INTERVAL (b.help_topic_id-1) YEAR AND n + INTERVAL b.help_topic_id YEAR  AND a.result='合格' AND a.pr_name=p

where  b.help_topic_id>=1 AND n+INTERVAL (b.help_topic_id-1) YEAR < o

group by  b.help_topic_id;

END IF;

SELECT * FROM tb1;

TRUNCATE TABLE tb1;

END;

//

giftzheng 2016-09-13

打赏
举报


DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_atest1`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_atest1`(
IN p_begin_time VARCHAR(200),
IN p_end_time VARCHAR(200)
)
BEGIN
 
-- 3个小时以内，获取每3分钟数据量，
-- 3小时~72小时 获取每1个小时数据量，
-- 72小时~30天 获取每天数据量，
-- 30天~365天 获取每月数据量，
-- 大于365天 获取每年数据量




		
	DECLARE p_time_diff_sec INT;
	DECLARE p_type VARCHAR(20);
	SET p_time_diff_sec=TIME_TO_SEC(TIMEDIFF(p_end_time,p_begin_time));

	IF(p_time_diff_sec>=0 AND p_time_diff_sec<=3*60*60) THEN
		SET p_type='3min';
	END IF;
	IF(p_time_diff_sec>3*60*60 AND p_time_diff_sec<=72*60*60) THEN
		SET p_type='1hour';
	END IF;
	IF(p_time_diff_sec>72*60*60 AND p_time_diff_sec<=30*24*60*60) THEN
		SET p_type='1day';
	END IF;
	IF(p_time_diff_sec>30*24*60*60 AND p_time_diff_sec<=365*24*60*60) THEN
		SET p_type='1month';
	END IF;
	IF(DATEDIFF(p_end_time,p_begin_time)>365) THEN
		SET p_type='1year';
	END IF;
	-- SELECT p_time_diff_sec,p_type;
	
	IF p_type='3min' THEN
		SELECT b.begin_time,COUNT(a.id) AS cnt
		FROM history_detail a 
		RIGHT JOIN 
		(
			SELECT DATE_ADD(p_begin_time,INTERVAL 3*help_topic_id MINUTE) AS begin_time,DATE_ADD(p_begin_time,INTERVAL 3*(help_topic_id+1) MINUTE) AS end_time
			FROM mysql.help_topic 
			WHERE DATE_ADD(p_begin_time,INTERVAL 3*(help_topic_id+1) MINUTE)<=p_end_time
		)b ON a.date>=b.begin_time AND a.date<=b.end_time
		GROUP BY b.begin_time;
	ELSEIF p_type='1hour' THEN
		SELECT b.begin_time,COUNT(a.id) AS cnt
		FROM history_detail a 
		RIGHT JOIN 
		(
			SELECT DATE_ADD(p_begin_time,INTERVAL 1*help_topic_id HOUR) AS begin_time,DATE_ADD(p_begin_time,INTERVAL 1*(help_topic_id+1) HOUR) AS end_time
			FROM mysql.help_topic 
			WHERE DATE_ADD(p_begin_time,INTERVAL 1*(help_topic_id+1) HOUR)<=p_end_time
		)b ON a.date>=b.begin_time AND a.date<=b.end_time
		GROUP BY b.begin_time;
	ELSEIF p_type='1day' THEN
		SELECT b.begin_time,COUNT(a.id) AS cnt
		FROM history_detail a 
		RIGHT JOIN 
		(
			SELECT DATE_ADD(DATE(p_begin_time),INTERVAL 1*help_topic_id DAY) AS begin_time,DATE_ADD(DATE(p_begin_time),INTERVAL 1*(help_topic_id+1) DAY) AS end_time
			FROM mysql.help_topic 
			WHERE DATE_ADD(DATE(p_begin_time),INTERVAL 1*(help_topic_id+1) DAY)<=DATE(p_end_time)
		)b ON a.date>=b.begin_time AND a.date<=b.end_time
		GROUP BY b.begin_time;
	ELSEIF p_type='1month' THEN
		SELECT b.begin_time,COUNT(a.id) AS cnt
		FROM history_detail a 
		RIGHT JOIN 
		(
			SELECT DATE_ADD(DATE(DATE(p_begin_time)-DAY(p_begin_time)+1),INTERVAL 1*help_topic_id MONTH) AS begin_time,
			DATE_ADD(DATE(DATE(p_begin_time)-DAY(p_begin_time)+1),INTERVAL 1*(help_topic_id+1) MONTH) AS end_time
			FROM mysql.help_topic 
			WHERE DATE_ADD(DATE(DATE(p_begin_time)-DAY(p_begin_time)+1),INTERVAL 1*(help_topic_id+1) MONTH)<=DATE_ADD(DATE(DATE(p_end_time)-DAY(p_end_time)+1),INTERVAL 1 MONTH)
		)b ON a.date>=b.begin_time AND a.date<=DATE_ADD(DATE_ADD(b.end_time,INTERVAL 1 MONTH),INTERVAL -1 DAY)
		GROUP BY b.begin_time;	
	ELSE
		SELECT b.begin_time,COUNT(a.id) AS cnt
		FROM history_detail a 
		RIGHT JOIN 
		(
			SELECT DATE_ADD(DATE(CONCAT(LEFT(p_begin_time,4),'-01-01')),INTERVAL 1*help_topic_id YEAR) AS begin_time,
			DATE_ADD(DATE(CONCAT(LEFT(p_begin_time,4),'-01-01')),INTERVAL 1*(help_topic_id+1) YEAR) AS end_time
			FROM mysql.help_topic 
			WHERE DATE_ADD(DATE(CONCAT(LEFT(p_begin_time,4),'-01-01')),INTERVAL 1*(help_topic_id+1) YEAR)<=DATE_ADD(DATE(CONCAT(LEFT(p_end_time,4),'-01-01')),INTERVAL 1 YEAR)
		)b ON a.date>=b.begin_time AND a.date<b.end_time
		GROUP BY b.begin_time;				
	END IF;

END$$

DELIMITER ;

自己调整

giftzheng 2016-09-12

打赏
举报

你是这用程序思维来写sql 其实不用这样类似用下面的 SELECT b.begin_time,COUNT(a.id) FROM history_detail a RIGHT JOIN ( SELECT DATE_ADD('2016-09-12',INTERVAL 3*help_topic_id MINUTE) AS begin_time,DATE_ADD('2016-09-12',INTERVAL 3*(help_topic_id+1) MINUTE) AS end_time FROM mysql.help_topic WHERE help_topic_id<20 )b ON a.date>=b.begin_time AND a.date<=b.end_time GROUP BY b.begin_time

qq_34088321 2016-09-12

打赏
举报

我写了一段这样的存储过程数据库一共只有100多条数据执行的时候为什么要好几秒才出结果。。。。 DELIMITER // CREATE PROCEDURE selectCount(p VARCHAR(20),n DATETIME,o DATETIME ) BEGIN DECLARE a INT ; DECLARE b DATETIME; SET a =0; SET a = (SELECT TIMESTAMPDIFF(MINUTE,n,o)); IF a <= 3*60 THEN WHILE n<o DO INSERT INTO tb1 (s1, s2)SELECT COUNT(*),(n + INTERVAL 3 MINUTE) FROM history_detail WHERE pr_name = p AND DATE > n AND DATE < n + INTERVAL 3 MINUTE AND result='合格' ; SET n = n + INTERVAL 3 MINUTE; END WHILE; END IF; IF a>3*60 AND a <= 72*60 THEN WHILE n<o DO INSERT INTO tb1 (s1, s2)SELECT COUNT(*),(n + INTERVAL 3 HOUR) FROM history_detail WHERE pr_name = p AND DATE > n AND DATE < n + INTERVAL 3 HOUR AND result='合格'; SET n = n + INTERVAL 1 HOUR; END WHILE; END IF; IF a>72*60 AND a <= 30*24*60 THEN WHILE n<o DO INSERT INTO tb1 (s1, s2)SELECT COUNT(*),(n + INTERVAL 3 DAY) FROM history_detail WHERE pr_name = p AND DATE > n AND DATE < n + INTERVAL 3 DAY AND result='合格'; SET n = n + INTERVAL 1 DAY; END WHILE; END IF; IF a>30*24*60 AND a <= 365*24*60 THEN WHILE n<o DO INSERT INTO tb1 (s1, s2)SELECT COUNT(*),(n + INTERVAL 3 MONTH) FROM history_detail WHERE pr_name = p AND DATE > n AND DATE < n + INTERVAL 3 MONTH AND result='合格'; SET n = n + INTERVAL 1 MONTH; END WHILE; END IF; IF a>365*24*60 THEN WHILE n<o DO INSERT INTO tb1 (s1, s2)SELECT COUNT(*),(n + INTERVAL 3 YEAR) FROM history_detail WHERE pr_name = p AND DATE > n AND DATE < n + INTERVAL 3 YEAR AND result='合格'; SET n = n + INTERVAL 1 YEAR; END WHILE; END IF; SELECT * FROM tb1; TRUNCATE TABLE tb1; END; //

qq_34088321 2016-09-12

打赏
举报

这个红框的是什么意思呢，
现在这个已经能实现了但是我要放入存储过程中每次传入三个参数一个工序（组装之类），开始时间，结束时间

根据时间判断开始时间到结束时间，
是3个小时以内，获取每3分钟数据量，
3小时~72小时获取每1个小时数据量，
72小时~30天获取每天数据量，
30天~365天获取每月数据量，
大于365天获取每年数据量

shoppo0505 2016-09-06

打赏
举报

建议查出一个时间段内数据返回，每3分钟的数据，在前台归纳

Mysql存储过程，查询一段时间的数据。

相关推荐