sql - 如何检索热门项目的结果

Question

我运行了一个查询以提供每所学校的学生总数，但现在我需要知道每所学校内这些学生的姓名，同时将总人数排在首位的结果放在首位。如何添加到此查询以显示学生的姓名？

这是我必须显示的每所学校的学生总数:

SELECT 
   dbo_Schools.Schools, 
   Count(dbo_tStudent.Student) AS NumberOfStudents
FROM 
   dbo_tStudent 
      INNER JOIN dbo_tSchools ON dbo_tStudent.SchoolID=dbo_tSchool.SchoolID
GROUP BY dbo_tSchool.School
ORDER BY Count(dbo_tStudent.Student) DESC;

重要的是我在列出学生时保持学校从学生人数最多的顺序

Answer 1

在这种情况下，您可以使用子查询 来实现您的结果集。

要在子查询中使用order by，您还需要一个top or limit 运算符。

SELECT sc.schoolname
    ,st.columns...
FROM dbo_tStudent st
INNER JOIN (
    SELECT TOP 1000 dbo_Schools.SchoolID
        ,min(schoolname) schoolname
        ,Count(dbo_tStudent.Student) AS NumberOfStudents
    FROM dbo_tStudent
    INNER JOIN dbo_tSchools ON dbo_tStudent.SchoolID = dbo_tSchools.SchoolID
    GROUP BY dbo_tSchool.School
    ORDER BY Count(dbo_tStudent.Student) DESC
    ) sc ON st.SchoolID = sc.SchoolID