Mysql instance --- Use @ variable _ MySQL in SQL statements

Mysql instance --- Use @ variable bitsCN.com in SQL statements This article introduces how to use @ variable in mysql statements to learn how to use this special variable. For more information, see. Requirements:

Calculate the number of days from the last access by the user. different users are identified based on the imei number. if there is only one access within the time range, the value is 0.

Initialize data:

Sample code:

Create table 'PD '(
'Imei' varchar (32) not null default '',
'Dat 'datetime DEFAULT NULL
) ENGINE = InnoDB default charset = utf8;

------------------------------
-- Records of pd
-- Www.jbxue.com
------------------------------
Insert into 'PD 'values ('1', '2017-07-25 00:00:01 ');
Insert into 'PD 'values ('1', '2017-07-26 00:00:02 ');
Insert into 'PD 'values ('2', '2017-07-23 00:00:04 ');
Insert into 'PD 'values ('2', '2017-07-26 00:00:03 ');
Insert into 'PD 'values ('3', '2017-07-26 00:00:01 ');

Script, using @ special variables:

Sample Code: select * from (
Select imei user_id, max (max_dd), max (max_dd_2), to_days (max (max_dd)-to_days (max (max_dd_2) days from (
Select imei, max_dd, max_dd_2 from (
Select tmp. imei, tmp. dates,
If (@ imei = tmp. imei, @ rank: = @ rank + 1, @ rank: = 1) as rank,
If (@ rank = 1, @ max_d: = tmp. dates, @ max_d: = null) as max_dd,
If (@ rank = 2, @ max_d_2: = tmp. dates, @ max_d_2: = null) as max_dd_2,
@ Imei: = tmp. imei
From (select imei, dates from pb order by imei asc, dates desc) tmp,
(Select @ rownum: = 0, @ imei: = null, @ rank: = 0, @ max_d: = null, @ max_d_2: = null)
) Result
) T
Group by imei
Having count (*)> 1
) X where x. days> = 1 and EXISTS (select 'X' from pb where dates> '2017-07-26 00:00:00 ')

Note:
When the table data volume is large, the use of union all and other operations will have tragic results.
The original link: http://www.jbxue.com/db/11874.html

BitsCN.com