主页 / dba / 问题 / 225952
Accepted
Adel SaadEddin
Asked: 2018-12-29 15:17:52 +0800 CST

如何在sql中找到分组依据的平均值的最大值

  • 772

我有三个表 Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark}

我会选择每个学院的尖子生

我厌倦了以多种方式执行此操作，但我不能

SELECT Faculty.name, Student.name, MAX(AVG(mark)) 
FROM (Faculty inner join Student On Faculty.id = Student.faculty_id) inner join Course_student On Student.id = Course_student.student_id
group by Faculty.name
select ddl

1 个回答

  1. Best Answer

    我无法检查结果，但我希望下面的查询应该有效。将来，请添加 DDL（数据定义语言），包括用于创建表的脚本，并插入一些记录来说明预期结果。

    WITH CTE AS (
SELECT Faculty.name AS Faculty_Name, 
       Student.name AS Student_Name, 
       AVG(mark) AS Avg_Mark,  
       ROW_NUMBER() OVER(PARTITION BY Faculty.name ORDER BY AVG(mark) DESC) AS Row
FROM Faculty 
INNER JOIN Student On Faculty.id = Student.faculty_id) 
INNER JOIN Course_student On Student.id = Course_student.student_id
GROUP BY Faculty.name
)
SELECT Faculty_Name, Student_Name, Avg_Mark
FROM CTE
WHERE Row = 1
    • 1

相关问题