{"action":"opened","issue":{"url":"https://api.github.com/repos/techial1042/Blog/issues/13","repository_url":"https://api.github.com/repos/techial1042/Blog","labels_url":"https://api.github.com/repos/techial1042/Blog/issues/13/labels{/name}","comments_url":"https://api.github.com/repos/techial1042/Blog/issues/13/comments","events_url":"https://api.github.com/repos/techial1042/Blog/issues/13/events","html_url":"https://github.com/techial1042/Blog/issues/13","id":333151076,"node_id":"MDU6SXNzdWUzMzMxNTEwNzY=","number":13,"title":"分数排名","user":{"login":"techial1042","id":22126304,"node_id":"MDQ6VXNlcjIyMTI2MzA0","avatar_url":"https://avatars0.githubusercontent.com/u/22126304?v=4","gravatar_id":"","url":"https://api.github.com/users/techial1042","html_url":"https://github.com/techial1042","followers_url":"https://api.github.com/users/techial1042/followers","following_url":"https://api.github.com/users/techial1042/following{/other_user}","gists_url":"https://api.github.com/users/techial1042/gists{/gist_id}","starred_url":"https://api.github.com/users/techial1042/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/techial1042/subscriptions","organizations_url":"https://api.github.com/users/techial1042/orgs","repos_url":"https://api.github.com/users/techial1042/repos","events_url":"https://api.github.com/users/techial1042/events{/privacy}","received_events_url":"https://api.github.com/users/techial1042/received_events","type":"User","site_admin":false},"labels":[{"id":960394416,"node_id":"MDU6TGFiZWw5NjAzOTQ0MTY=","url":"https://api.github.com/repos/techial1042/Blog/labels/Algorithm","name":"Algorithm","color":"4ba01c","default":false},{"id":877286465,"node_id":"MDU6TGFiZWw4NzcyODY0NjU=","url":"https://api.github.com/repos/techial1042/Blog/labels/Arduino","name":"Arduino","color":"e57c52","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-06-18T06:50:50Z","updated_at":"2018-06-18T06:50:50Z","closed_at":null,"author_association":"OWNER","body":"编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。\r\n\r\n```\r\n+----+-------+\r\n| Id | Score |\r\n+----+-------+\r\n| 1  | 3.50  |\r\n| 2  | 3.65  |\r\n| 3  | 4.00  |\r\n| 4  | 3.85  |\r\n| 5  | 4.00  |\r\n| 6  | 3.65  |\r\n+----+-------+\r\n```\r\n\r\n例如，根据上述给定的 `Scores` 表，你的查询应该返回（按分数从高到低排列）：\r\n\r\n```\r\n+-------+------+\r\n| Score | Rank |\r\n+-------+------+\r\n| 4.00  | 1    |\r\n| 4.00  | 1    |\r\n| 3.85  | 2    |\r\n| 3.65  | 3    |\r\n| 3.65  | 3    |\r\n| 3.50  | 4    |\r\n+-------+------+\r\n```\r\n\r\n\r\n\r\n原先的想法使用 SQL 里面的变量来做的，然后点执行代码的时候发现过不去，就放弃了这种想法。\r\n\r\n使用 count 计数， distinct 去重，比较两个分数，在利用 order by 进行排序即可。\r\n\r\n\r\n\r\n**mysql**\r\n\r\n\r\n\r\n```sql\r\nselect s.Score, (select count(distinct s1.Score)\r\n\tfrom Scores s1 where s1.Score >= s.Score) Rank\r\nfrom Scores s order by s.Score desc;\r\n```\r\n\r\n\r\n\r\n测试成功后，发现用的时间有点长……\r\n\r\n我看了一下其他人的提交，找了执行用时较少的代码看，居然发现他们是使用变量，而且思路更简单。\r\n\r\n（其实执行代码那里使用变量测试不通过，但是直接提交一发，就知道是不是正确了。\r\n\r\n\r\n**思路如下**\r\n\r\n\r\n预选设置一个分数，实际生活中是不存在负的分数的，因此选一个负数的值作为预置的分数即可。\r\n\r\nrank 的起始为 0，递增即可。\r\n\r\nrank 递增的条件：等于上一次的 rank，加上判断分数是否等于上一次的分数。\r\n\r\n比较的两个值不相等就是 1，就递增，如果相等，就不递增。\r\n\r\n```\r\n@cur_rank := @cur_rank + (@pre_score != (@pre_score := s.Score))\r\n\r\n相当于\r\n\r\n@tmp1 = (@pre_score := s.Score);\r\n\r\n@tmp2 = @pre_score != tmp; // 值只会是 0 或者 1\r\n\r\n@cur_rank = @cur_rank + tmp2;\r\n```\r\n\r\n\r\n```sql\r\nset @pre_score = -324234;\r\nset @cur_rank = 0;\r\nselect s.Score, (@cur_rank := @cur_rank + (@pre_score != (@pre_score := s.Score))) Rank\r\n\tfrom Scores s order by 1 desc;\r\n```\r\n\r\n"}}
{"actor":{"display_login":"techial1042"}}