Q:已知 class_id stu_id course_id 在表 t 中,求每个班级中被选次数最多的课程 id ,返回字段 class_id course_id
1
sutra 2022-08-02 21:57:52 +08:00
select class_id, course_id, count(*) c from t group by class_id, course_id order by c desc;
|
2
reter 2022-08-02 22:38:53 +08:00 1
select
class_id, course_id from ( -- 根据每个班级下的选课人数, 给课程确定顺序 select class_id, course_id, stu_cnt, -- 根据选课人数降序 row_number() over (partition by class_id order by stu_cnt desc) as r from ( -- 先统计每个班级, 每个课程的选课数量 select class_id, course_id, count(1) as stu_cnt from t group by class_id, course_id ) as t1 ) as t2 -- 课程排第一的数据 where r = 1; |
3
wxf666 2022-08-03 08:43:56 +08:00 1
『 SQLite 测试通过(排版原因,每行代码开头有 _ 和 全角空格)』
WITH orig_data(class, stu, course) AS ( VALUES ('1 班', '学生甲', '语文'), ('2 班', '戊', '语文'), ('3 班', '辛', '语文'), ('1 班', '学生乙', '语文'), ('2 班', '己', '语文'), ('3 班', '壬', '数学'), ('1 班', '学生丙', '数学'), ('2 班', '庚', '数学'), ('3 班', '癸', '数学'), ('1 班', '学生丁', '数学') ), table_with_rank(class, course, rank) AS ( SELECT class, course, RANK() OVER (PARTITION BY class ORDER BY COUNT(*) DESC) rank FROM orig_data _ GROUP BY class, course ) SELECT class, GROUP_CONCAT(course) courses FROM table_with_rank _WHERE rank = 1 _GROUP BY class 『结果(可能出现多个并列第一的课程,此时用「,」分隔)』 class courses ----- ------- 1 班 语文,数学 2 班 语文 3 班 数学 |