SQL的执行顺序与使用MySQL进行排名

问题描述：碰到这个问题是在使用MySQL显示排名时候引出来的，在执行SQL时的执行顺序问题。

a.显示排名，相同年龄不同排名

1 SELECT pid, name, age, @curRank := @curRank + 1 AS rank
2 FROM players p, (
3 SELECT @curRank := 0
4 ) q
5 ORDER BY age

 

b.显示排名，相同年龄排名相同，相同年龄后的排名按照排名递增

1 SELECT pid, name, age,
2 CASE
3 WHEN @prevRank = age THEN @curRank
4 WHEN @prevRank := age THEN @curRank := @curRank + 1
5 END AS rank
6 FROM players p,
7 (SELECT @curRank :=0, @prevRank := NULL) r
8 ORDER BY age

 

c.相同年龄后的年龄排名按照之前排名总个数递增。

1 SELECT pid, name, age, rank FROM
2 (SELECT pid, name, age,
3 @curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,
4 @incRank := @incRank + 1,
5 @prevRank := age
6 FROM players p, (
7 SELECT @curRank :=0, @prevRank := NULL, @incRank := 1
8 ) r
9 ORDER BY age) s

 

在这个过程中添加语句，需要注意语句的执行顺序，因为SELECT是在DISTINCT、UNION、ORDER BY之前执行的，而排名是在SELECT就已经出来了的。

SQL 语句的语法顺序是：

1. SELECT [DISTINCT]

2. FROM [JOIN  [ON]]

3. WHERE

4. GROUP BY [HAVING]

5. UNION

6. ORDER BY

其执行顺序为：

1. FROM

2. JOIN  [ON]

3. WHERE

4. GROUP BY  [HAVING]

5. SELECT

6. DISTINCT

7. UNION

8. ORDER BY

 

Reference：

http://fellowtuts.com/mysql/query-to-obtain-rank-function-in-mysql/

http://blog.csdn.net/u014044812/article/details/51004754

http://blog.itpub.net/29254281/viewspace-1415038/

转载于:https://www.cnblogs.com/SleepyDot/p/8552890.html