一条sql语句查询成绩排名

首先问题:

然后答案:

SELECT
	a.id,
	a.score AS Score,
	COUNT( DISTINCT b.score ) AS Rank 
FROM
	scores a,
	scores b 
WHERE
	b.score >= a.score 
GROUP BY
	a.id 
ORDER BY
	a.score DESC

分步骤理解(sql拆解理解):

前提:要简单理解sql执行顺序, 和"笛卡尔积"的概念 

1. SELECT
   	*
   FROM
   	scores a,
   	scores b 
   

   拆解之后得到结果:

   id	score	id(1)	score(1)
   1	3.50	1	3.50
   2	3.65	1	3.50
   3	4.00	1	3.50
   4	3.85	1	3.50
   5	4.00	1	3.50
   6	3.65	1	3.50
   1	3.50	2	3.65
   2	3.65	2	3.65
   3	4.00	2	3.65
   4	3.85	2	3.65
   5	4.00	2	3.65
   6	3.65	2	3.65
   1	3.50	3	4.00
   2	3.65	3	4.00
   3	4.00	3	4.00
   4	3.85	3	4.00
   5	4.00	3	4.00
   6	3.65	3	4.00
   1	3.50	4	3.85
   2	3.65	4	3.85
   3	4.00	4	3.85
   4	3.85	4	3.85
   5	4.00	4	3.85
   6	3.65	4	3.85
   1	3.50	5	4.00
   2	3.65	5	4.00
   3	4.00	5	4.00
   4	3.85	5	4.00
   5	4.00	5	4.00
   6	3.65	5	4.00
   1	3.50	6	3.65
   2	3.65	6	3.65
   3	4.00	6	3.65
   4	3.85	6	3.65
   5	4.00	6	3.65
   6	3.65	6	3.65

   可以看到联表查询不加条件产生"笛卡尔积"后的结果是36条记录
   相当于score(成绩)的6条记录每条之间都列了出来 

2. 进行上面结果的筛选

   SELECT
   	*
   FROM
   	scores a,
   	scores b 
   WHERE
   	b.score >= a.score 

   多加了一个where条件,结果如下:

   id	score	id(1)	score(1)
   1	3.50	1	3.50
   1	3.50	2	3.65
   2	3.65	2	3.65
   6	3.65	2	3.65
   1	3.50	3	4.00
   2	3.65	3	4.00
   3	4.00	3	4.00
   4	3.85	3	4.00
   5	4.00	3	4.00
   6	3.65	3	4.00
   1	3.50	4	3.85
   2	3.65	4	3.85
   4	3.85	4	3.85
   6	3.65	4	3.85
   1	3.50	5	4.00
   2	3.65	5	4.00
   3	4.00	5	4.00
   4	3.85	5	4.00
   5	4.00	5	4.00
   6	3.65	5	4.00
   1	3.50	6	3.65
   2	3.65	6	3.65
   6	3.65	6	3.65

   以上结果把"b表"的成绩(score) 大于 "a表"的成绩的记录都筛选出来记录数是23条
   此时score(1)的字段全部都是大于等于score字段的

3. 根据以上结果我们在加一个排序

   SELECT
   	*
   FROM
   	scores a,
   	scores b 
   WHERE
   	b.score >= a.score 
   ORDER BY a.id
   
   

   得到结果:

   id	score	id(1)	score(1)
   1	3.50	6	3.65
   1	3.50	4	3.85
   1	3.50	1	3.50
   1	3.50	2	3.65
   1	3.50	5	4.00
   1	3.50	3	4.00
   2	3.65	6	3.65
   2	3.65	4	3.85
   2	3.65	2	3.65
   2	3.65	5	4.00
   2	3.65	3	4.00
   3	4.00	5	4.00
   3	4.00	3	4.00
   4	3.85	4	3.85
   4	3.85	5	4.00
   4	3.85	3	4.00
   5	4.00	5	4.00
   5	4.00	3	4.00
   6	3.65	6	3.65
   6	3.65	4	3.85
   6	3.65	2	3.65
   6	3.65	5	4.00
   6	3.65	3	4.00

   可以看到 id为1 成绩3.50的 大于他的有6条记录, id为2 成绩3.65的 大于他的有5条记录, 以此类推把6个成绩都想象出来

4. 最后我们根据id分组(GROUP BY), 再把"b表"的成绩去重(DISTINCT)再记总数(COUNT)

   SELECT
   	a.id,
   	a.score AS Score,
   	COUNT( DISTINCT b.score ) AS Rank 
   FROM
   	scores a,
   	scores b 
   WHERE
   	b.score >= a.score 
   GROUP BY
   	a.id

   此sql不太好理解的就是" COUNT( DISTINCT b.score ) AS Rank  "这段
   结合步骤3(也就是上面的)结果 举例一个来理解这句sql:
   id为1 成绩3.50的 大于他的有6条记录, 分别是 3.65, 3.85, 3.50, 3.65, 4.00, 4.00
   我们看到这6条记录去掉重复后剩下 3.65, 3.85, 3.50, 4.00 这4个, 也就说明成绩 3.50有4个大于等于他的,那么最高只能排名第4
   这就是上面去重求和的作用, 求的和就是他的最高排名

5. 补充上完整的sql

   SELECT
   	a.id,
   	a.score AS Score,
   	COUNT( DISTINCT b.score ) AS Rank 
   FROM
   	scores a,
   	scores b 
   WHERE
   	b.score >= a.score 
   GROUP BY
   	a.id 
   ORDER BY
   	a.score DESC

   结果:

   id	Score	Rank
   3	4.00	1
   5	4.00	1
   4	3.85	2
   2	3.65	3
   6	3.65	3
   1	3.50	4

   菜鸟分享, 大佬勿喷