如何检索热门项目的结果

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

在这种情况下，您可以使用Sub Query来实现结果集。

要在子查询中使用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

Answer 2

假设您使用的是SQL Server，您可以使用CTE加入第一个聚合，其详细信息如下：

;WITH cte as (
    SELECT TOP 1000 dbo_Schools.SchoolID, 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
)
SELECT
    sc.<your school name column>,
    st.<your student columns>
from
    dbo_tStudent st
        INNER JOIN cte ON st.SchoolID = cte.SchoolID
        INNER JOIN dbo_tSchools sc on cte.SchoolID = sc.SchoolID

更一般地说：您需要一个derived table（包含group-by子句的聚合），它与学生详细信息的select语句连接在一起。在本例中，CTE基本上是一个便于使用派生表的SQL Server特性。