leetcode 178. 分数排名 【笛卡尔乘积】

笛卡尔乘积其实就是两个表的级联

题目
编写一个 SQL查询 来实现分数排名。如果两个分数相同，则两个分数排名（Rank）应该相同。请注意，平局之后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“漏洞”。

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

例如，根据给定的上述 Scores 表，您的查询应该生成以下报告（按最高分排序）：

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

===数据库 mysql

== 表的结构 Scores

字段	类型	空	默认
Id	int(11)	是	NULL
Score	decimal(3,2)	是	NULL

== 转存表中的数据 Scores

创建SQL代码

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))
Truncate table Scores
insert into Scores (Id, Score) values ('1', '3.5')
insert into Scores (Id, Score) values ('2', '3.65')
insert into Scores (Id, Score) values ('3', '4.0')
insert into Scores (Id, Score) values ('4', '3.85')
insert into Scores (Id, Score) values ('5', '4.0')
insert into Scores (Id, Score) values ('6', '3.65')

以下从尾开始拆步
1.

select s1.Score as Score, count(s2.Score) as Rank from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.id order by s1.Score desc

这是最终解答
去重将score分数找出来
s1和s2比较，选择s1.score<=s2.score的记录数，即为rank
按照id分组，再按照s1.score排序组，最后倒序

2.

select s1.Score as Score, count(s2.Score) as Rank from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.id order by s1.Score

去掉倒序

3.

select s1.Score as Score, count(s2.Score) as Rank from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.id

去掉score排序

4.

select * from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score

选择所有不重复的Score数据，组成新表s2。

(select distinct Score from Scores) as s2;

级联Scores表和s2表，级联的条件是Scores表的Score字段值小于等于s2表Score字段的值。

select * from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score;

s1和s2比较，选择s1.score<=s2.score的记录数，即为rank
例如：s2的4条score记录都比s1的第一条score记录大，那么这4次比较记录都会记录下来生成同等表，
也就是s1.score从1行变成4行，同时这4行的id都是一样的。

5.

select s1.Score as Score, count(s2.Score) as Rank from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score

没有分组的情况下，只取一次，因为只有一组，并计算了这一组的s2.score

6.

select s1.Score as Score, (s2.Score) as Rank from Scores as s1 left join (select distinct Score from Scores) as s2 on s1.Score <= s2.Score group by s1.id

按id分组，rank不是计算而是直接s2.score的情况下，证明这时select是只会取每组的第一行

另一中解法就是使用用户变量

SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score))  Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc