DELIMITER ;
CREATE PROCEDURE test_insert()
BEGIN
DECLARE y BIGINT DEFAULT 138;
DECLARE h BIGINT DEFAULT 13;
DECLARE m BIGINT DEFAULT 50;
WHILE y<1000
DO
set h = RAND() * 500%24;
set m = RAND() * 10000%60;
INSERT INTO t_ods(`id`, `uid`, `login_time`)
VALUES (y, FLOOR( 1 + RAND() * 5), CONCAT('2019-',CAST(y%12 AS CHAR),'-07 ',CAST(h AS CHAR),':',CAST(m AS CHAR),':',CAST(y%60 AS CHAR)) );
SET y=y+1;
END WHILE ;
COMMIT;
END;
CALL test_insert();
DROP PROCEDURE IF EXISTS test_insert;
select id ,uid,login_time from t_ods where uid = 3 and login_time like '2019-10-0%' order by login_time
计算在同一个mysql表中第一个时间与第二个时间比较,第二个时间与第三个时间比较
SELECT
b.id,
b.uid,
b.curr,
b.pre,
TIMESTAMPDIFF(MINUTE,b.pre, b.curr) AS diff
FROM
(
SELECT
a.`id` AS id,
a.`uid` AS uid,
a.`login_time` AS curr,
@a.login_time AS pre,
@a.login_time:= a.login_time
FROM t_ods a,(SELECT @a.login_time:=0)r where uid = 3 and login_time like '2019-10-07%' order by login_time asc
)b
SELECT
(@r :=@r + 1) AS rank
FROM (SELECT @r := 0) r
;
SELECT
a.`id` AS id,
a.`name` AS NAME,
a.`login_time` AS curr,
@a.login_time AS pre, --上一条记录的login_time值
@a.login_time:= a.login_time AS tmp
FROM stock_store_addinfo a,(SELECT @a.login_time:=0)s
@a.login_time:=a.login_time AS tmp --将a.login_time值赋到临时临时变量 @a.login_time
SELECT @a.login_time:=0 --选择当前表,上一条记录 a.login_time,默认值为0