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 语句的语法顺序是：
-
SELECT [DISTINCT]
-
FROM [JOIN [ON]]
-
WHERE
-
GROUP BY [HAVING]
-
UNION
-
ORDER BY
其执行顺序为：
-
FROM
- JOIN [ON]
-
WHERE
-
GROUP BY [HAVING]
-
SELECT
-
DISTINCT
-
UNION
-
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/
