SELECT MAX(COUNT) MySQL

Question

我是MySQL的新手，我现在有一个任务要做，我有三个表:

1. 学生(id、名称)
2. 课程(id、名称)
3. 成绩(id, student_id (FK)， course_id(FK)， grade)

我应该

获取学生注册人数最多的课程名称，如果与其他课程有冲突或关联，按升序排序后检索该课程。

我尝试了几个查询，但它们不是"足够有效">

SELECT course.name FROM (
SELECT CI ,MAX(Total) FROM 
(
SELECT course_id as CI,COUNT(*) AS Total 
FROM grades
GROUP BY course_id ASC
) AS Results
) AS x
INNER JOIN courses ON x.CI = courses.id

和

SELECT courses.name FROM (
SELECT course_id, COUNT(*) AS how_many
FROM grades
GROUP BY course_id ASC
HAVING how_many = (
SELECT COUNT(*) AS how_many
FROM grades
GROUP BY course_id
ORDER BY how_many DESC
LIMIT 1
)
LIMIT 1
) AS X
JOIN courses ON X.course_id=courses.id

有更有效的查询吗?

Answer 1

对我来说，你的查询尝试在逻辑上都是不正确的。您应该将courses加入grades以获得每门课程注册的学生人数。关于效率，RANK分析函数是最高性能的选项之一，假设您正在运行MySQL 8+:

WITH cte AS (
SELECT c.id, c.name, RANK() OVER (ORDER BY COUNT(*) DESC, c.name) rnk
FROM courses c
INNER JOIN grades g ON g.course_id = c.id
GROUP BY c.id, c.name
)
SELECT id, name
FROM cte
WHERE rnk = 1;

在MySQL的早期版本中，我们可以使用LIMIT查询:

SELECT c.id, c.name
FROM courses c
INNER JOIN grades g ON g.course_id = c.id
GROUP BY c.id, c.name
ORDER BY COUNT(*) DESC, c.name
LIMIT 1;

Answer 2

您可以使用ORDER BY子句和LIMIT子句来获得所需的内容，而无需进行两次聚合:

WITH enrollments AS (
SELECT course_id, COUNT(DISTINCT student_id) AS num_enrollments
FROM grades
GROUP BY course_id
)
SELECT * 
FROM       enrollments e
INNER JOIN courses c
ON e.course_id = c.id
ORDER BY e.num_enrollments DESC, c.name ASC
LIMIT 1

子查询将通过聚合学生获得入学情况，然后将其与课程连接以使用课程名称。

然后按

排序数据:

• 注册数后代
• 航线名称上升

只考虑第一行