Leetcode 178. Rank Scores

原创 2016年08月31日 05:20:46

178. Rank Scores

Total Accepted: 11758 Total Submissions: 48686 Difficulty: Medium

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number

 should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

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

For example, given the above Scores table, your query should generate the following report (order by highest score):

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

思路:

这道题还是比较tricky的。一开始我想着用alter新建一个列,然后再排序,第一遍将小的score rank+1;第二遍将相同score的rank值变成一样。结果:

ALTER command denied to user 'student'@'localhost' for table 'Scores'

这题的解法是这样,

观察给定的例子,>=4的 rank是1;>=3.85的rank是2;>= 3.65 的rank是3,也就是说其rank为>=其score的distinct score数。

# Write your MySQL query statement below
select
  Score,
  (select count(distinct Score) from Scores where Score >= s.Score) as Rank
from Scores s
order by Score desc

版权声明:本文为博主原创文章,未经博主允许不得转载。

Leetcode刷题 sql算法题 178 Rank Scores

主要参考 LeetCode解题报告 --解法一 笛卡尔连接 分组计数 排序 SELECT Scores.Score, COUNT(Ranking.Score) AS RANK FROM Scor...
  • u013458516
  • u013458516
  • 2015年11月09日 20:22
  • 1535

Leetcode 178. Rank Scores

178. Rank Scores Total Accepted: 11758 Total Submissions: 48686 Difficulty: Medium Write a SQL q...
  • fantasiasango
  • fantasiasango
  • 2016年08月31日 05:20
  • 570

leetcode 184,185 Department Top Three Salaries/HIGHEST Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for th...
  • zhangfanhehe_1
  • zhangfanhehe_1
  • 2015年01月28日 23:48
  • 977

leetcode 178. Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ra...
  • travel_1
  • travel_1
  • 2016年06月05日 15:35
  • 694

【leetcode Database】178. Rank Scores

题目: Write a SQL query to rank scores. If there is a tie between two scores, both should have th...
  • Kevin_zhai
  • Kevin_zhai
  • 2016年08月08日 17:06
  • 3691

leetcode 177 Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table. +----+--------+ | Id | Sal...
  • zhangfanhehe_1
  • zhangfanhehe_1
  • 2015年01月29日 23:49
  • 1430

本博客LeetCode题解索引及各类算法问题小结汇总(C++实现)

Some explains: 1),The current problems have been solved:Welcome, EbowTang! You have solved 101 / 316...
  • EbowTang
  • EbowTang
  • 2016年02月15日 17:18
  • 5659

178. Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ra...
  • snailrunfast
  • snailrunfast
  • 2017年04月16日 15:22
  • 56

LeetCode | Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ra...
  • u010595903
  • u010595903
  • 2015年01月20日 10:56
  • 425

[LeetCode] Leetcode 题解索引 (C++/Java/Python/Sql)

全部最新的题解可以在 我的 github 上找,欢迎 star 和 watch ~ 更新中~~说明这个系列的题解包括用 C++/Java/Python 写的 leetcode 上的算法题目,和 Sq...
  • hcbbt
  • hcbbt
  • 2015年03月11日 09:49
  • 24048
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Leetcode 178. Rank Scores
举报原因:
原因补充:

(最多只允许输入30个字)