时间戳

首页 > 解决方案 > 按部门从学生表和部门表组中查找前 3 名学生

sql - 按部门从学生表和部门表组中查找前 3 名学生

问题描述

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

  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名学生。

我已经用 row_num() 函数编写了查询。

但是，当有相同百分比的学生时，我遇到了问题。

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%，所以这两个学生都将被认为是第三名，并且将成为前三名的一部分。

我尝试过类似的东西

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，当有相同百分比的学生时，它会给我一个错误的输出。

请帮助解决这个问题。

标签： sqlsql-serverdatabase

解决方案

这是您要编写的查询吗？

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 行时你想要四行。

推荐阅读