gpt4 book ai didi

mysql - 如何在mysql存储过程中循环日期时间

转载 作者:可可西里 更新时间:2023-11-01 07:59:28 24 4
gpt4 key购买 nike

我使用的是 mysql 存储过程,我如何创建一个循环,使变量 strathour 每 1 小时间隔 1 小时,然后返回查询的总输出。当 starthour 小于 11 月的日期时,它将以 1 小时的间隔执行查询。

这是我的代码:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_asessiontime`(
out `total` int
)
BEGIN

declare `starthour`, `endhour` datetime;
set `starthour` = '2012-09-20 01:59:00';
set `endhour` = '2012-09-20 02:00:00';


select count(terminalcount.terminalids) into total from (
select distinct ts.TerminalID `terminalids` from
tmptransactiondetails td
inner join transactionsummary ts
on td.TransactionSummaryID = ts.TransactionsSummaryID
where
td.ServiceID = 4
and
td.TransactionType in ('D','W')
and
(ts.DateStarted >= starthour and ts.DateStarted < endhour)
or
(ts.DateEnded >= starthour and ts.DateEnded < endhour)
or
(ts.DateStarted < starthour and starthour <= ts.DateEnded)
)as terminalcount;

-- 每1小时循环一次

while 

starthour < '2012-11-01 01:59:00' do
select starthour + interval 1 hour;
select total as totalnumber;

end while;

END

非常感谢你们。

最佳答案

我不确定我是否正确回答了你的问题,但这就是你在日期之间循环的方式

set currHour = '2012-09-20 01:59:00';
set endhour = '2012-09-21 02:00:00';

REPEAT

-- execute your queries for every hour

Set currHour = DATE_ADD(currHour,INTERVAL 1 HOUR);
UNTIL currHour > endhour END REPEAT;

如果必须使用while循环:

WHILE currHour < endhour DO
-- execute your queries for every hour
Set currHour = DATE_ADD(currHour,INTERVAL 1 HOUR);
END WHILE;

关于mysql - 如何在mysql存储过程中循环日期时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13305383/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com