按部门从“学生”表和“部门”表中查找前三名学生 [英] Finding top 3 students from the Student table and Department table group by department

问题描述

假设我们有两个表：
1.学生
2.部门

Suppose we have two Tables: 1. Student 2. Department

  Student table has 4 columns 
   1. id (int pk)
   2. name (varchar)
   3. percentage (int)
   4. dept_id (fk)

  Department table has 3 columns
   1. dept_id (int pk)
   2. dept_name (varchar)   

查询是从每个部门中选择百分比最高的前3名学生。

Query is to select top 3 students from the each department having highest percentage.

我已经用row_num（）函数编写了查询。

I have already written the query with row_num() function.

但是，当有相同百分比的学生时，我面临问题。

But, I am facing problem when there are students with the same percentage.

Student table with:
id  name   percentage  dept_id
1   a      70          1
2   b      80          1 
3   c      90          1 
4   d      70          1
5   e      55          1 
6   f      50          2
7   g      65          2
8   h      68          2   

Department table with 

dept_id  dept_name 
1        Information Technology
2        Computer Science


**expected Result**

id  student_name  dept_name                percentage  row_number
3   c             Information Technology   90          1
2   b             Information Technology   80          2
1   a             Information Technology   70          3  
4   d             Information Technology   70          4
8   h             Computer Science         68          5 
7   g             Computer Science         65          6
6   f             Computer Science         50          7

您可以看到，有两个学生占70％，所以两个学生将被认为是第三名，并且将是前3名的一部分。

you can see that, there are two student with 70% so both the student will be considered at the third place, and will be a part of top 3.

我尝试过类似

SELECT *, ROW_NUM() OVER (PARTITION BY D.Dept_ID ORDER BY S.PERCENTAGE DESC) AS ROW_NUMBER
   FROM STUDENT S, DEPARTMENT D WHERE D.DEPT_ID = S.DEPT_ID
)
SELECT ID, NAME AS STUDENT_NAME, DEPT_NAME, PERCENTAGE FROM CTE WHERE ROW_NUMBER < 4.

在这里，我要添加静态条件（row_number< 4）row_number，它将给我

HERE, I am adding static condition (row_number < 4) row_number, It will give me a wrong output when there are students with the same percentage.

请提供帮助。

推荐答案

这是您要编写的查询吗？

Is this the query you are trying to write?

WITH cte as (
    SELECT s.ID, s.NAME, d.DEPT_NAME, s.PERCENTAGE,
           RANK() OVER (PARTITION BY D.Dept_ID ORDER BY S.PERCENTAGE DESC) AS seqnum
   FROM STUDENT S JOIN
        DEPARTMENT D
       ON  D.DEPT_ID = S.DEPT_ID
)
SELECT cte.ID, cte.NAME AS STUDENT_NAME, cte.DEPT_NAME, cte.PERCENTAGE
FROM CTE
WHERE seqnum < 4;

注意：

• 从不在 FROM 子句中使用逗号。


• 始终使用正确的，明确的，标准 JOIN 语法。


• ROW_RANK（）不是函数。我想您要 RANK（），因为当限制为3时您要四行。

• Never use commas in the FROM clause.
• Always use proper, explicit, standard JOIN syntax.
• ROW_RANK() is not a function. I think you want RANK(), given that you want four rows when the limit is just 3.

这篇关于按部门从“学生”表和“部门”表中查找前三名学生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！