Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.