- 变量使用的易错点
- 对于SET,可以使用=或:=作为分配符
- 使用SELECT定义用户变量只能使用:=作为分配符(因为在非SET语句中 = 被视为一个‘比较操作符’.
- 通过变量和if语句实现窗口函数(1):dense_rank()
SELECT
salary,
rk AS `RANK`
FROM
(
SELECT
salary,
IF
----如果@pres和salary相等,则@currank 否则@currank等于@currank + 1
( @pres = salary, @currank, @currank := @currank + 1 ) AS rk,
@pres := salary
FROM
employee,(
SELECT
----声明变量@pres为-1,@currank为0
@pres := - 1,
@currank := 0
) c1
ORDER BY salary DESC ) s
- 通过变量和case when 语句实现窗口函数(2):dense_rank()
SELECT
salary,
( CASE WHEN @pres = salary THEN @currank WHEN @pres := salary THEN @currank := @currank + 1 END ) AS `RANK`
FROM
employee,
( SELECT @pres := - 1, @currank := 0 ) AS c1
ORDER BY
salary DESC;
- 通过Count及DISTINCT实现窗口函数(3):dense_rank()
SELECT
s.salary,
( SELECT COUNT( DISTINCT salary ) FROM employee e WHERE e.salary >= s.salary ) AS c1
FROM
employee s
ORDER BY
salary DESC;
- 通过group by,order by及变量实现窗口函数(4):dense_rank()
select
s.salary,
r.rank
from
employee s,
(
select
tmp1.salary,
(@rownum:=@rownum+1) as `Rank`
from
(select salary from employee group by salary order by salary desc) as tmp1,
(select @rownum:=0) tmp2
) as r
where
s.salary=r.salary
order by s.salary desc;
- 通过if函数解决下面的变形题
SELECT
DISTINCT s.Num
FROM
(
SELECT
t.*,
IF
( @before_num = t.Num, @times := @times + 1, 1 ) AS times,
@before_num := t.Num
FROM
( SELECT @before_num := NULL, @times := 1 ) r,
test t
) s
WHERE s.times >=3