首页 > 试题广场 >

SQL 编程打印出每个班级的分数前三名的分数(假设所有学生

[问答题]

SQL 编程
打印出每个班级的分数前三名的分数(假设所有学生中没有分数相同的情况, 也就是不考虑并列第一第二第三的场景)
表结构：
id varchar(20),-- 编号
class varchar(20),-- 班级
score int-- 分数

光辉岁月v

select

a.*

from

mscore as a

where

(select

count(*)

from

mscore as b

where

b.class = a.class and b.score >= a.score

) <= 3

order by a.class asc, a.score desc;

mysql> select

-> a.*
-> from
-> mscore as a
-> where
-> (select
-> count(*)
-> from
-> mscore as b
-> where
-> b.class = a.class and b.score >= a.score
-> ) <= 3
-> order by a.class asc, a.score desc;
+------+-------+-------+
| id | class | score |
+------+-------+-------+
| 11 | 1班 | 98 |
| 13 | 1班 | 93 |
| 12 | 1班 | 92 |
| 24 | 2班 | 76 |
| 22 | 2班 | 57 |
| 23 | 2班 | 53 |
| 33 | 3班 | 98 |
| 35 | 3班 | 94 |
| 31 | 3班 | 87 |
+------+-------+-------+
9 rows in set (0.00 sec)

发表于 2020-07-23 10:26:20 回复(0)

git

DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`class` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`score` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `classes` VALUES (1, 'A', 85);
INSERT INTO `classes` VALUES (2, 'B', 75);
INSERT INTO `classes` VALUES (3, 'A', 90);
INSERT INTO `classes` VALUES (4, 'A', 100);
INSERT INTO `classes` VALUES (5, 'A', 91);
INSERT INTO `classes` VALUES (6, 'B', 89);
INSERT INTO `classes` VALUES (7, 'B', 99);
INSERT INTO `classes` VALUES (8, 'B', 60);
INSERT INTO `classes` VALUES (9, 'A', 65);
INSERT INTO `classes` VALUES (10, 'B', 78);

方法一： select * from classes c where (select count(*) from classes a where a.class=c.class and a.score>c.score)<3 order by c.class,c.score desc;

方法二： select * from classes t where exists(select count(*) from classes ts where ts.score>=t.score group by ts.class having count(*)<=3) order by class,sco

re desc;

性能分析：

EXPLAIN select * from classes c where (select count(*) from classes a where a.class=c.class and a.score>c.score)<3 order by c.class,c.score desc;
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    c    ALL                    10    Using where; Using filesort
2    DEPENDENT SUBQUERY    a    ALL                    10    Using where

EXPLAIN select * from classes t where exists(select count(*) from classes ts where ts.score>=t.score group by ts.class having count(*)<=3) order by class,score desc;

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    t    ALL                    10    Using where; Using filesort
2    DEPENDENT SUBQUERY    ts    ALL                    10    Using where; Using temporary; Using filesort

都使用了 Using filesort，方法二使用了group by会产生Using temporary，也不好。最好的sql应该优化掉2个。

// TODO

发表于 2019-12-16 10:55:23 回复(0)

提交观点

问题信息

测试开发工程师第四范式 2020

上传者：小小

难度：

2条回答 15收藏 1376浏览

SQL 编程打印出每个班级的分数前三名的分数(假设所有学生

问题信息

热门推荐

相关试题

SQL 编程 打印出每个班级的分数前三名的分数(假设所有学生

问题信息

热门推荐

相关试题

SQL 编程打印出每个班级的分数前三名的分数(假设所有学生