mysql_存储过程_后一行减去前一行
DELIMITER $$
/*统计单个用户登录次数的存过
@times_count int 返回值
@i 记录行号的变量 初始值为0
@temp 记录时间差的变量
@total 记录登录次数的变量 初始值为0
@nums 记录共有多少行(单个用户)
@j 记录行号的变量 值为i+1
*/
CREATE PROCEDURE p_count_loginTimes(OUT times_count INT)
BEGIN
DECLARE i,
temp,
total,
nums,
j INT ;
SET total = 0 ;
SET i = 0 ;
SELECT
COUNT(1)
FROM
t_app_visit_detail INTO nums ;
WHILE
i <= nums DO SET j = i + 1 ;
SELECT
TIMESTAMPDIFF(
MINUTE,
(SELECT
APP_END_TIME
FROM
t_app_visit_detail
LIMIT i, 1),
(SELECT
APP_START_TIME
FROM
t_app_visit_detail
LIMIT j, 1)
) INTO temp ;
IF temp > 30
THEN SET total = total + 1 ;
END IF ;
SET i = i + 1 ;
SET times_count = total ;
END WHILE ;
END $$
DELIMITER ;