文章/答案/技术大牛

发布

社区首页 >问答首页 >从“学生表”和“系表组”中按部门查找前3名学生

问从“学生表”和“系表组”中按部门查找前3名学生
EN

Stack Overflow用户

提问于 2019-08-13 16:50:56

回答 2查看 797关注 0票数 1

假设我们有两张桌子: 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，当有相同百分比的学生时，它会给我错误的输出。

请帮个忙。

sql

sql-server

database

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-08-13 16:53:50

这是您要编写的查询吗？

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的情况下，您需要四行。

票数 5

Stack Overflow用户

发布于 2019-08-13 17:47:06

您可以使用DENSE_RANK()代替ROW_NUMBER()，如下所示-

SELECT A.dept_id,A.name,A.dept_name,A.percentage 
FROM
(
    SELECT D.dept_id,S.name,D.dept_name,S.percentage,
    DENSE_RANK() OVER(PARTITION BY D.dept_id ORDER BY S.[percentage] DESC) RN
    FROM Department D
    INNER JOIN Student S ON D.dept_id = S.dept_id
)A 
WHERE RN <= 3

输出是-

dept_id name    dept_name               percentage
1       c       Information Technology  90
1       b       Information Technology  80
1       d       Information Technology  70
1       a       Information Technology  70
2       h       Computer Science        68
2       g       Computer Science        65
2       f       Computer Science        50

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/57482171

复制

相似问题

问从“学生表”和“系表组”中按部门查找前3名学生
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从“学生表”和“系表组”中按部门查找前3名学生EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从“学生表”和“系表组”中按部门查找前3名学生
EN