A table for the target phone number
B table for the data table, phone number, transaction amount, transaction completion time,
Think through the following statements and calculate the query each time each time lag between the time,
SELECT
A. ` mobile number `,
B. ` deal ` AS time
The FROM
` mobile number ` AS A,
` cashier ` AS B
Where a. ` mobile number `=b. ` mobile number `
GROUP BY a. ` mobile number `, b. ` deal completion time `

Difficulties: I am a little white, don't know how to calculate multiple transaction completion time lag,
A mobile phone number have shopping time for many times, some also only once, so I don't know what to do,
https://bbs.csdn.net/topics/320082158 this post I also saw, don't know how to write, and imitation is not successful,

The ideal result is:
Mobile phone number transaction completion time lag
555, 20200606,
555 20200607 1
555, 20200610, 3
666, 20200611,

So, probably, you help to look at the great god, I asked the bosses have broadly fall,

CodePudding user response:

This is the result of my above statement data query,,

CodePudding user response:

With cs as
(
Select a phone number, date, row_number () over (order by phone number) as id from the table
)
Mobile phone number, select a. a. date, case when b.i d is null then 0 else a. end date - b. date ce
The from cs on Anderson, a left join cs b d=b.i d + 1 and a. phone number=b. phone number

CodePudding user response:

refer to the second floor shanghaivincent response:

with cs as
(
Select a phone number, date, row_number () over (order by phone number) as id from the table
)
Mobile phone number, select a. a. date, case when b.i d is null then 0 else a. end date - b. date ce
The from cs on Anderson, a left join cs b d=b.i d + 1 and a. phone number=b. phone number

Boss, how can not query,

CodePudding user response:

reference 3 floor m. ndre response:

Quote: refer to the second floor shanghaivincent response:

With cs as
(
Select a phone number, date, row_number () over (order by phone number) as id from the table
)
Mobile phone number, select a. a. date, case when b.i d is null then 0 else a. end date - b. date ce
The from cs on Anderson, a left join cs b d=b.i d + 1 and a. phone number=b. phone number

Boss, how can't query,

You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near 'cs AS (SELECT a phone number, date, row_number () over (ORDER BY phone number) AS I "at line 1

Prompt the,,

CodePudding user response:

You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near 'cs AS (SELECT a phone number, date, row_number () over (ORDER BY phone number) AS I "at line 1
Tip of this there are bosses, know the meaning,

CodePudding user response:

The

reference 5 floor m. ndre response:

You have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near 'cs AS (SELECT a phone number, date, row_number () over (ORDER BY phone number) AS I "at line 1
Tip of the bosses know meaning,,

Should be related to your mysql version, version may not support you with the as syntax
You rewrite history, it is ok to use the subquery

 
Mobile phone number, select a. a. date, case when b.i d is null then 0 else a. end date - b. date ce 
The from 
(
Select a phone number, date, row_number () over (order by phone number) as id from the table 
) as a 
Left the join (
Select a phone number, date, row_number () over (order by phone number) as id from the table 
) as b on Anderson, d=b.i d + 1 and a. phone number=b. phone number