Function implementation of over () row_number () similar to oracle in mysql
I found a lot of information from the Internet, referred to the following link, and completed a requirement: the start time of the next line, as the end time of the start time of the previous line.
Https://www.cnblogs.com/yhzh/p/6222580.html
The following is done by yourself and can be ignored
-- the explanation is as follows:
-- sort by device number, user id,book_id, and reading time
-- then grouped by device number, and numbered the data in the group in chronological order
-- first of all, last_device_num is empty
The first line determines device_num = null, so assign 1 and pass the device_num of the first line to last_device_num
On the second line, determine whether device_num is equal to the device_num of the previous line (that is, last_device_num). If so, increment it, otherwise it is 1.
The second line is purely used for assignment.
UPDATE
Xxx p
(SELECT
N.id
N.begin_time
M.begin_time AS end_time
TIMESTAMPDIFF (
SECOND
N.begin_time
M.begin_time
) AS interva
FROM
(SELECT
Id
Device_num
Begin_time
IF (
@ last_device_num = a.device_num
@ rank: = @ rank + 1
@ rank: = 1
) AS row_number
@ last_device_num: = a.device_num
FROM
(SELECT
*
FROM
Xxx
ORDER BY device_num
User_id
Book_id
Begin_time ASC) a
(SELECT
@ rownum: = 0
@ last_device_num: = NULL
@ rank: = 0) b) m
RIGHT JOIN
(SELECT
Id
Device_num
Begin_time
IF (
@ last_device_num1 = a.device_num
@ rank1: = @ rank1 + 1
@ rank1: = 2
) AS row_number
@ last_device_num1: = a.device_num
FROM
(SELECT
*
FROM
Xxx
ORDER BY device_num
User_id
Book_id
Begin_time ASC) a
(SELECT
@ rownum1: = 0
@ last_device_num1: = NULL
@ rank1: = 1) b) n
ON m.row_number = n.row_number
AND m.device_num = n.device_num) Q
SET
P.`end _ time` = q.end_time
P.`read _ time` = q.interva
WHERE p.id = q.id
