MySQL存储过程 获取最近一段时间 - yiyixiaozhi/readingNotes GitHub Wiki

title: MySQL存储过程，列出获取最近一段时间 date: 2020-3-10 description: MySQL存储过程，列出获取最近一段时间 categories: - 工具 tags: - MySQL - 存储过程

思路：以当前时间为准，循环计算时间，计算的结果放到临时表中。 获取最近的10分钟，将每秒都列出来

CREATE DEFINER=`yyxz`@`%` PROCEDURE `last_ten_min`()
BEGIN
    declare now_time timestamp;
    declare sec int;
    drop table if exists tmp_table_01;
    CREATE TEMPORARY TABLE tmp_table_01(   
    `sec_time` timestamp
    );
    set now_time=now();
    set sec=0;
    while sec < 1000 do
    set sec = sec + 1;
        if sec mod 100 >= 60 then
        set sec = (sec div 100 + 1) * 100;
        end if;
    INSERT INTO tmp_table_01 (`sec_time`) VALUES (SUBTIME(now_time, sec));
    end while;
    END

查看效果：

CALL last_ten_min();
select * from tmp_table_01;

20200310113934.png

查询过去10分钟的出入记录

SELECT
	DATE_FORMAT(entry_time, '%Y-%m-%d %k:%i:%s') AS X, 
	COUNT(id) AS Y
FROM
	t_student_entry_record
	WHERE entry_time BETWEEN SUBTIME(NOW(), 1000) AND NOW()
GROUP BY entry_time

将过去10分钟的记录跟生成的时间列表右连接

SELECT
	DATE_FORMAT(tt01.sec_time, '%Y-%m-%d %k:%i:%s') AS X,
	COUNT(tser.id) AS Y
FROM
	(SELECT id, entry_time FROM t_student_entry_record where entry_time between subtime(now(), 1000) and now()) AS tser 
	RIGHT JOIN tmp_table_01 AS tt01 ON tser.`entry_time` = tt01.sec_time
GROUP BY
	tt01.sec_time
ORDER BY tt01.sec_time
⚠️ **GitHub.com Fallback** ⚠️