我正在使用MySQL存储过程,如何编写一个循环,在每个1小时内变量strathour将具有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
非常感谢大家。