在SQL中查找一个类别中的大多数结果并将其连接到另一个单独表中的另一个类别

Question

我需要返回Is_Registered_T表中注册了最多节的Student Name的结果。

示例数据

Student
Student_ID  Student_Name
12365       Kayla
38214       LetterSky
45874       Michael
54907       Altvater
66324       Aiken
70542       Marra
78412       Jennifer
78457       Kim
78478       Steve
78548       Kristy
78596       Eddie
78956       Christ
86582       Dianne
89547       Agnes
89754       Mary
Is_Registered
Student_ID  Setion_ID  Semester
12365       2723       I-2001
38214       2714       I-2001
38214       2723       I-2002
45874       2714       I-2002
45874       2716       I-1999
45874       2722       I-2001
54907       2714       I-2001
54907       2715       I-2001
54907       2720       I-2003
66324       2713       I-2001
66324       2719       I-1999
66324       2722       I-2001
66324       2725       I-2000
70542       2720       I-1998
70542       2728       I-1999
78596       2723       I-2000
86582       2725       I-2003
89547       2712       I-2002
89547       2713       I-1999

最简单的方法是什么?我已经使用下面的INTO函数来创建一个单独的表，我可以返回最多的记录(4)，但不是学生的名字(Aiken)。有没有更简单的解决办法?

Select Student_Name, Count(Section_ID) Sections_Enrolled
from Student_t, IS_Registered_t
where Student_t.Student_ID = IS_Registered_t.Student_ID
group by Student_Name
Student_Name Sections_Enrolled
------------ -----------------
Agnes        2
Aiken        4
Altvater     3
Dianne       1
Eddie        1
Kayla        1
LeterSky     2
Marra        2
Michael      3

Answer 1

根据您的用例，您可以:

1. 获取所有注册人数等于最大注册人数的学生(即如果有多个学生注册了4次，您将返回所有学生)
2. 按报名人数排序，取第一个学生(如果多个学生报名人数相同，则任意选择一个学生)

方案1.1(优选)

使用带有领带的Top并生成一个更简单的执行计划

SELECT TOP 1 WITH TIES s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
ORDER BY COUNT(*) DESC

解决方案1.2 -<手册>

不推荐，会产生更复杂的执行计划。最内层的查询对登记的节进行计数;上层查询查找注册的最大节数;最外面的查询再次计数登记的区段，并过滤结果，只包括那些等于前面找到的最大值

的区段。

SELECT s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
HAVING COUNT(*) = -- number of sections enrolled equals to the maximum number of sections enrolled
(SELECT MAX(c.Sections_Enrolled)
FROM (SELECT COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID) c)

解决方案2

SELECT TOP 1 s.Student_Name, COUNT(*) AS Sections_Enrolled
FROM Student s JOIN Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID, s.Student_Name
ORDER BY COUNT(*) DESC

如果您只想要名称，您可以从最外面的查询中删除COUNT(*) AS Sections_Enrolled。

DbFiddle

Answer 2

我认为在子查询中不需要内部连接。

SELECT
MIN(s.Student_Name) AS Name,
COUNT(*) AS Sections_Enrolled
FROM Student s INNER JOIN 
Is_Registered r ON s.Student_ID = r.Student_ID
GROUP BY s.Student_ID
HAVING COUNT(*) >= ALL (
SELECT COUNT(*)
FROM Is_Registered
GROUP BY Student_ID
);

或extra-succinct:

with summary as (select Student_ID, count(*) cnt from Is_Registered group by Student_ID)
select (select Student_Name from Students where Student_ID = s.Student_ID) as Student_Name, cnt
from summary s where cnt = (select max(cnt) from summary);