总结：
1.定义变量一般写在from语句之后，相当于在末尾定义变量
2.定义变量一般都要取一个别名，比如这里的init,t1等
3.变量在sql语句中表面上看只是个变量，但是从整个语句来看，它相当于一张表
4.大多数有变量的sql语句中，都有order by。个人理解是：指定变量对原始数据的执行过程每次面对的是哪一行。如果少了这个order by，则可能会出现一些错误
什么时候用@变量：目前我掌握的很浅，发现大家都基本在动态求和（累加）、排名中用。
Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))
Truncate table Scores
insert into Scores (Id, Score) values ('1', '3.5')
insert into Scores (Id, Score) values ('2', '3.65')
insert into Scores (Id, Score) values ('3', '4.0')
insert into Scores (Id, Score) values ('4', '3.85')
insert into Scores (Id, Score) values ('5', '4.0')
insert into Scores (Id, Score) values ('6', '3.65')别名不要使用rank，会报错。所以我这里使用的都是rank1
select score , (@i:=@i+1) as rank1 from Scores,(select @i:=0) init order by score desc
select (5<>4) 答案为1；select (5<>5)答案为0.
select
Score,
@i := @i + (@pre <> ( @pre := Score)) Rank1
from
Scores,
(select @i := 0,@pre := -1) init
order by Score desc分析：
第一步：变量初始值均为0，-1肯定和Score不相等，所以此时加1，这样4显示排名1；
第二步：@i=1，第三行中左边的@pre=4，右边Score=4赋值给右边的@pre，这样的话，就相等了，所以值为0，排名依然是1
第三步：@i=1，第三行中左边的@pre=4，右边Score=3.85赋值给右边的@pre，这样的话，就不相等了，所以值为1，排名则+1等于2
后面依次类推：
Create table If Not Exists Queue (person_id int, person_name varchar(30), weight int, turn int)
Truncate table Queue
insert into Queue (person_id, person_name, weight, turn) values ('5', 'George Washington', '250', '1')
insert into Queue (person_id, person_name, weight, turn) values ('4', 'Thomas Jefferson', '175', '5')
insert into Queue (person_id, person_name, weight, turn) values ('3', 'John Adams', '350', '2')
insert into Queue (person_id, person_name, weight, turn) values ('6', 'Thomas Jefferson', '400', '3')
insert into Queue (person_id, person_name, weight, turn) values ('1', 'James Elephant', '500', '6')
insert into Queue (person_id, person_name, weight, turn) values ('2', 'Will Johnliams', '200', '4')
这里不加order by就完全乱套了，没有从上至下的运行顺序，有些人名还出现了两次。
select person_name, @cnt:=@cnt+weight amount
from queue, (select @cnt:=0) t1
select person_name, @cnt:=@cnt+weight amount
from queue, (select @cnt:=0) t1
order by turn
select person_name
from
(select person_name, @cnt:=@cnt+weight amount
from queue, (select @cnt:=0) t1
order by turn) t2
where t2.amount <= 1000
order by amount desc
limit 1Create table If Not Exists Scores (player_name varchar(20), gender varchar(1), day date, score_points int)
Truncate table Scores
insert into Scores (player_name, gender, day, score_points) values ('Aron', 'F', '2020-01-01', '17')
insert into Scores (player_name, gender, day, score_points) values ('Alice', 'F', '2020-01-07', '23')
insert into Scores (player_name, gender, day, score_points) values ('Bajrang', 'M', '2020-01-07', '7')
insert into Scores (player_name, gender, day, score_points) values ('Khali', 'M', '2019-12-25', '11')
insert into Scores (player_name, gender, day, score_points) values ('Slaman', 'M', '2019-12-30', '13')
insert into Scores (player_name, gender, day, score_points) values ('Joe', 'M', '2019-12-31', '3')
insert into Scores (player_name, gender, day, score_points) values ('Jose', 'M', '2019-12-18', '2')
insert into Scores (player_name, gender, day, score_points) values ('Priya', 'F', '2019-12-31', '23')
insert into Scores (player_name, gender, day, score_points) values ('Priyanka', 'F', '2019-12-30', '17')
select gender, day,
case gender when 'F'
then @prePointF := @prePointF + score_points
else @prePointM := @prePointM + score_points
end as total
from Scores, (select @prePointF := 0, @prePointM := 0) temp
order by gender, day不加order by，累加的顺序就会乱，这里order by规定为gender，day，先分为性别，再按照日期，注意这里不要用group by
我们在按日期统计每天的数据时，有时候会想统计截止到每一天的总量。固然可以通过一张辅助表（存储了所需要的所有日期）来达到目的，可效率太低，影响查询速度。
今天分享一个效率高、用法简单的方式：自定义变量！
废话少说，直接上码：
SELECT a.day,a.num,@total:=@total+num
FROM
(SELECT DATE(a.`enter_time`) DAY, COUNT(*) num
FROM member_enter a
GROUP BY DATE(a.`enter_time`)
)a,(SELECT @total:=0)c代码中，在子查询SELECT @total:=0中，声明了一个变量total，然后，通过@total:=@total+num 的方法，实现原来每天的num数据累加。
这种方法，简单且实用!
