首页 > 试题广场 >

考试分数(三)

[编程题]考试分数(三)

热度指数：126538 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如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

第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
....
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，

不同的语言岗位(language)表简化如下:

id	name
1	C++
2	JAVA
3	Python

请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

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

算法知识视频讲解

Jack____

SELECT g1.id, l.name, g1.score
FROM grade as g1 INNER JOIN grade as g2
ON g1.language_id = g2.language_id
INNER JOIN language as l
ON g1.language_id = l.id
WHERE g1.score <= g2.score
GROUP BY g1.id, l.name, g1.score
HAVING COUNT(DISTINCT g2.score) <= 2
ORDER BY l.name ASC, g1.score DESC, g1.id ASC

发表于 2020-09-05 14:48:17 回复(2)

Mysql

呼啦啦NO.1

解题思路：用表连接

第一步：找出每个岗位分数排名前2名的用户

很明显需要用到开窗函数，要注意有分数相同的排名，由此用到dense_rank()

再来“每个岗位分数排名前2名”：dense_rank()over(partition by language_id order by score desc)

然后建立一张表

(select id, language_id, score,
dense_rank () over (partition by language_id order by score desc) as R
from grade) as Tg

第二步：把语言岗位(language)表需要的信息提取出来

(select id, name from language) as Tl

第三步：把两张表结合起来
select Tg.id, Tl.name, Tg.score
from (select id, name from language) as Tl
join (select id,
language_id,
score,
dense_rank () over (partition by language_id order by score desc) as R
from grade) as Tg
第四步：找出表连接的条件

on Tg.language_id = Tl.id and Tg.R <= 2

第五步：排序

order by Tl.name, Tg.score desc, Tg.id asc;

最后一步：

select Tg.id, Tl.name, Tg.score
from (select id, name from language) as Tl
join (select id,
language_id,
score,
dense_rank () over (partition by language_id order by score desc) as R
from grade) as Tg
on Tg.language_id = Tl.id and Tg.R <= 2
order by Tl.name, Tg.score desc, Tg.id asc;

发表于 2021-12-14 19:52:06 回复(0)

Mysql

牛客168328177号

select id
,name
,score
from (
    select gr.id
    ,la.name
    ,gr.score
    ,dense_rank() over (partition by gr.language_id order by gr.score desc) ranking
    from grade gr,language la
    where gr.language_id = la.id
) t1
where ranking < 3
order by name asc,score desc,id asc;

发表于 2021-10-29 11:20:42 回复(0)

Mysql

Baily1234

SELECT 
    id, 
    name, 
    score
FROM (
    SELECT
        g.id, 
        l.name,
        g.score,
        DENSE_RANK() OVER(PARTITION BY l.name ORDER BY g.score DESC) AS r
    FROM
        grade g
        LEFT JOIN `language` l ON g.language_id = l.id
) t
WHERE 
    r <= 2
ORDER BY
    name ASC,
    score DESC

发表于 2021-10-19 18:37:39 回复(0)

Mysql

想吃烤肉的四郎在做ppt

select 
    id,
    name,
    score 
from(
    select 
        grade.id as id,
        language.name as name,
        grade.score as score,
        dense_rank() over(partition by language_id order by score desc) as myrank
    from 
        grade
    left join language 
    on 
        grade.language_id=language.id
    order by 
        language.name asc,
        grade.score DESC,
        grade.id asc
) T where T.myrank<=2;

发表于 2021-10-12 15:31:17 回复(0)

Mysql

牛客184739658号

select id
,name
,score
from
(
    select g.id
    ,l.name
    ,g.score
    ,dense_rank()over(partition by name order by score desc) r
    from grade g
    join language l
    on g.language_id = l.id
) a
where r in (1,2)
order by 2 asc,3 desc,1 asc

发表于 2021-07-22 08:47:01 回复(0)

晓_

简单

select
    g.id,l.name,g.score
from
    (
    select
    *,dense_rank() over(partition by language_id order by score DESC) as r
    from
    grade
    ) g
left join
    language l
on
    g.language_id=l.id
where
    g.r<=2
order by
    l.name ASC, g.score DESC, g.id ASC

发表于 2021-07-07 14:32:11 回复(0)

木析

SELECT t.id,language.name,t.score
FROM language
    JOIN (
        #列出人员信息及排名
        SELECT g1.id, g1.language_id, g1.score, COUNT(DISTINCT g2.score) AS 'rank'
        FROM grade AS g1
        LEFT JOIN grade AS g2
        ON g1.language_id=g2.language_id AND g2.score>=g1.score
        GROUP BY g1.id
    ) AS t
    ON language.id=t.language_id
WHERE t.rank<=2
ORDER BY name, score DESC, score;

重命名为rank时候要加引号，这儿卡了一会儿

发表于 2021-06-11 21:11:38 回复(0)

S9see

-- 不会用窗口函数，泪目！
SELECT 
    g.id,
    l.name,
    g.score
FROM grade g
JOIN language l ON g.language_id = l.id
WHERE g.score IN (
    SELECT a.score
    FROM (
        SELECT DISTINCT g1.score
        FROM  grade g1
        WHERE g1.language_id = g.language_id
        ORDER BY g1.score DESC
        LIMIT 2
    ) AS a
)
ORDER BY l.name,g.score DESC,g.id

发表于 2021-05-15 21:57:40 回复(2)

小落啊

感觉根据id排序就没必要，之前的两个条件完之后，id就排完之后，id就基本已经是乱的，多此一举

编辑于 2021-03-04 10:21:17 回复(1)

牛客940400079号

select g.id, name, g.score from grade g 
left join `language` l on l.id = g.language_id 
where
2 > (select count(distinct g2.score) from grade g2 where g2.language_id = g.language_id and g2.score > g.score) 
order by name asc, g.score desc

发表于 2021-01-22 11:59:37 回复(0)

多吃早睡

三种解题方式

1、如果仅使用内连接：先组内排序得出名词，再根据id选取出名词对应的分数

注意：分数的前两名，需要加distinct

select a.id, b.name, g3.score
from (
    select g1.id, g1.language_id, count(distinct g2.score) as score
    from grade as g1
    inner join grade as g2
    on g1.language_id=g2.language_id
    and g1.score<=g2.score
    group by g1.id, g1.language_id
    having count(distinct g2.score)<=2
) a 
inner join language b
on a.language_id=b.id
inner join grade as g3
on a.id=g3.id
order by b.name asc, g3.score desc

2、使用窗口函数进行组内排序，但是需要注意的是：1）where a.ranking<=2筛选条件不能放到子查询中，这个可能和窗口函数的机制有关，是先运行下面的语句，再运行窗口函数，所以如果放在内部，一开始是没有ranking字段的，这就会导致冲突报错 2）一定要对子查询另起一个名字，如：a，如果省去，也会报错

select a.id, a.name, a.score
from (
    select grade.id, language.name, grade.score,
    dense_rank() over (partition by grade.language_id order by grade.score desc) as ranking
    from grade
    inner join language
    on grade.language_id=language.id

) as a
where a.ranking<=2
order by a.name asc, a.score desc, a.id asc

3、使用where子查询：思想与内连接类似，不过也是一种思路。我们可以知道的是，一般可以使用where子查询的题目，同样可以使用内连接代替

select g1.id, a.name, g1.score
from grade as g1
inner join language as a
on g1.language_id=a.id
where (
    select count(distinct g2.score)
    from grade as g2
    where g1.language_id=g2.language_id
    and g2.score>=g1.score
)<=2
order by a.name asc, g1.score desc, g1.id asc

编辑于 2020-11-13 22:37:34 回复(8)

求个offer，让我毕业吧

没用排名函数，好像有点笨拙了

SELECT
    g.id,
    l.name,
    g.score
FROM
    grade g JOIN LANGUAGE l
    ON g.language_id = l.id
WHERE score >= (SELECT IFNULL(MAX(score),0) FROM grade g2 
                WHERE g2.language_id = g.language_id
               AND score < (SELECT MAX(score) FROM grade g3 
                                 WHERE g3.language_id = g.language_id))
ORDER BY l.name ASC , g.score DESC

发表于 2020-09-23 20:28:54 回复(2)

某位同学

用dense_rank()

select g.id, l.name, g.score
from (select *, 
      dense_rank() over(partition by language_id order by score desc) as rank 
      from grade) g, 
      language l
where g.language_id = l.id and g.rank <= 2
order by l.name asc, g.score desc, g.id asc

编辑于 2020-08-30 05:36:49 回复(18)

牛客243020975号

第一步：计算满足排名前两名的id

select t1.id
from grade as t1
join grade as t2 on t1.language_id = t2.language_id and t1.score<=t2.score
group by t1.language_id,t1.id
having count(distinct t2.score)<3

第二步：多表联立

答案：

select l1.id,l3.name,l1.score

from grade as l1
join(
select t1.id
from grade as t1
join grade as t2 on t1.language_id = t2.language_id and t1.score<=t2.score
group by t1.language_id,t1.id
having count(distinct t2.score)<3) as l2 on l1.id = l2.id
join language as l3 on l1.language_id = l3.id
order by l3.name asc,l1.score desc,l1.id asc;

发表于 2020-08-24 15:42:42 回复(5)

明明铭

主要是用了窗口函数 DENSE_RANK（）

SELECT t.ID,t.NAME,t.SCORE FROM (SELECT g.id AS ID,l.name AS NAME,g.score AS SCORE,DENSE_RANK() OVER(PARTITION BY l.id ORDER BY g.score DESC) AS rank 
               FROM grade g JOIN language l ON g.language_id = l.id) t 
WHERE t.rank IN (1,2)
ORDER BY t.NAME,t.SCORE DESC,t.ID

发表于 2020-09-05 19:49:25 回复(6)

Mysql

牛客496467263号

使用窗口函数dense_rank()进行排序相同

select a.id,name,score from(
select t.*
    ,dense_rank()over(partition by language_id order by score desc) tt 
    from grade t) a join language b 
on a.language_id = b.id
where a.tt <=2
order by name asc,score desc,a.id asc

发表于 2021-08-19 10:25:18 回复(0)

牛客298508523号

select g1.id,l.name,g1.score from grade as g1
left join language as l on g1.language_id=l.id
where g1.score in (select g2.score from grade as g2
where g2.language_id=g1.language_id
group by score
order by score desc limit 2
)
order by l.name asc,g1.score desc,g1.id asc

发表于 2021-07-22 09:41:41 回复(1)

是咸蛋黄呀

SELECT a.id, b.name, a.score
FROM
(
SELECT * ,
dense_rank() over (partition by language_id order by score desc) as rank
FROM grade
) a
INNER JOIN
language b
ON a.language_id = b.id
WHERE rank < 3
ORDER BY b.name, a.score desc, a.id

发表于 2020-10-27 23:42:31 回复(0)