sql自定义变量的基本用法

1.employee表

按 salary进行排名,相同的排名相同,并且排名连续:
sql:
SELECT
@num := IF(@salary = salary, @num, @num + 1) num,
//另@num重新赋值(如果@salary = 当前记录的salary,@num赋值为@num,否则赋值为@num+1 )
@salary := salary salary
FROM
employee,
(SELECT
@salary := 0,
@num := 0) t1 //初始数值
ORDER BY salary DESC ;

注意;set后面不论是:=,还是=,都是赋值,select直接跟的后面是:=表示赋值 ,其他情况=是比较,:=表示赋值
例如:
select distinct Num as ConsecutiveNums
from (
select Num,
case
when @prev = Num then @count := @count + 1//第一个=是比较
when (@prev := Num) is not null then @count := 1//第一个是赋值,case 只会返回第一个符合条件的值该值一定是TRUE 所以 is not null 也可以去掉
end as CNT
from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3