mysql - SQL:获取具有最大值的列(按列分组)的行

Question

我想知道如何检索与列的最大值匹配的行。

SCHEMA

assignments:
id  student_id   subject_id
1   10           1
2   10           2
3   20           1
4   30           3
5   30           3
6   40           2

students:
id  name
10   A
20   B
30   C

subjects:
id  name
1   Math
2   Science
3   English

查询:提供 SQL:

1. Display the names of the students who have taken most number of assignments
2. Display the names of the subjects which have been taken the most number of times

结果:

1. 
  A
  C

2. 
  Math
  English

谢谢!

Answer 1

前面的答案不太正确 - 您不会得到两个计数相同的实例。试试这个 - 一旦理解了这个概念，第二个就很容易复制。

SELECT a.student_id, s.name, COUNT(a.subject_id) as taken_subjects
FROM assignments a
INNER JOIN students s ON a.student_id = s.id
GROUP BY a.student_id, s.name
HAVING COUNT(a.subject_id) = (SELECT COUNT(*) FROM assignments GROUP BY student_id LIMIT 1)

替代查询:SELECT a.subject_id, s.subject_name, COUNT(a.subject_id) FROM 作业 a, 主题 s其中 a.subject_id = s.subject_id按 a.student_id、s.subject_name 分组HAVING COUNT(a.subject_id) = (从分配 GROUP BY subject_id 中选择 MAX(COUNT(1))