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

qq_34088321 2016-09-06 09:55:52
有一个这样的表 表名是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个 （一个时间段的相同工序 ，用那个循环）

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

...全文
746 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 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中的存储过程（详细篇）
存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。 就是...
mysql存储过程时间_mysql存储过程及日期函数实践
业务场景说明：假设待...数据准备：要解决的问题：校验订单表中不同状态订单的数量与前台显示是否一致解决思路：1、通过存储过程，获取数据库中的不同时间段的待审核订单数量2、将后台获取的数据与前台进行比较实现...
mysql 存储过程详解
mysql 存储过程详解
mysql 时间查询_两种常用MySql查询时间段的方法
MySql查询时间段的方法很多，下面就为您介绍几种最常用的MySql查询时间段方法，如果您在MySql查询时间段方面遇到过问题，不妨一看。MySql的时间字段有date、time、datetime、timestamp等，往往我们在存储数据的时候...
什么是mysql存储过程_mysql 存储过程
什么是存储过程一组可编程函数，是为了满足特定功能的SQL语句集存储过程就是具有名字的一段代码，用来完成个特定的功能创建的存储过程保存在数据库的数据字典中​2.为什么要用存储过程将重复性很高的操作，封装到一...
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章