SQL268. 考试分数(三)
描述
牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:| id | language_id | score |
| 1 | 1 | 12000 |
| 2 | 1 | 13000 |
| 3 | 2 | 11000 |
| 4 | 2 | 10000 |
| 5 | 3 | 11000 |
| 6 | 1 | 11000 |
| 7 | 2 | 11000 |
| id | name |
| 1 | C++ |
| 2 | JAVA |
| 3 | Python |
| id | name | score |
| 2 | C++ | 13000 |
| 1 | C++ | 12000 |
| 3 | JAVA | 11000 |
| 7 | JAVA | 11000 |
| 4 | JAVA | 10000 |
| 5 | Python | 11000 |
示例1
输入:
drop table if exists grade; drop table if exists language; CREATE TABLE `grade` ( `id` int(4) NOT NULL, `language_id` int(4) NOT NULL, `score` int(4) NOT NULL, PRIMARY KEY (`id`)); CREATE TABLE `language` ( `id` int(4) NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO grade VALUES (1,1,12000), (2,1,13000), (3,2,11000), (4,2,10000), (5,3,11000), (6,1,11000), (7,2,11000); INSERT INTO language VALUES (1,'C++'), (2,'JAVA'), (3,'Python');
输出:
2|C++|13000 1|C++|12000 3|JAVA|11000 7|JAVA|11000 4|JAVA|10000 5|Python|11000
Sqlite 解法, 执行用时: 9ms, 内存消耗: 3508KB, 提交时间: 2021-08-09
select a.id,a.name,a.score from (select g.id,l.name,g.score, (dense_rank() over(partition by g.language_id order by g.score desc))as t_rank from grade as g,language as l where g.language_id=l.id) as a where a.t_rank<=2 order by a.name asc,a.score desc,a.id asc
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3492KB, 提交时间: 2021-09-09
select a.id, b.name, a.score from grade a
left join language b on a.language_id = b.id
where (select count(distinct c.score)
from grade c
where c.score >= a.score and a.language_id = c.language_id) <= 2
order by b.name, a.score desc, a.id
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3500KB, 提交时间: 2021-09-07
select t.id,b.name,t.score from language b, (select *,dense_rank() over(partition by language_id order by score desc) t_rank from grade) t where t_rank<=2 and b.id=t.language_id order by b.name,t.score desc,t.id asc
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3720KB, 提交时间: 2022-01-03
select t.id,name,score from( select gd.id, name, score, dense_rank() over(partition by language_id order by score desc) rank from grade gd left join language lg on gd.language_id = lg.id) t where rank < 3 order by name asc, score desc, t.id asc
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3320KB, 提交时间: 2020-11-08
select id,name,score from (select g.id,g.score,l.name, dense_rank() over(partition by g.language_id order by score desc) rn from grade g inner join language l on g.language_id=l.id) t where rn<=2 order by name asc,score desc,id asc;