时间戳

首页 > 解决方案 > 最大函数返回多个值 [SQL]

sql - 最大函数返回多个值 [SQL]

问题描述

我有 3 张桌子：钱，学生，教师。此查询返回每个教员和每个教员的最高津贴。

select 
    f.name as "FACULTY_NAME",
    max(stipend) as "MAX_STIPEND"
from 
    money m, student s 
inner join
    faculty f on f.id_faculty = s.faculty_id
where 
    m.student_id = s.id_student
group by 
    f.id_faculty, f.name;

查询工作正常：

FACULTY_NAME     |    MAX_STIPEND
-----------------+---------------
IT Faculty       |    50
Architecture     |    60
Journalism       |    40

但是，当我将 s.name 添加到原始查询以显示收到 max_stipend 的学生的姓名时，查询不像以前那样工作 - 它返回所有学生

select 
    f.name as "FACULTY_NAME",s.name,
    max(stipend) as "MAX_STIPEND"
from 
    money m, student s 
inner join
    faculty f on f.id_faculty = s.faculty_id
where 
    m.student_id = s.id_student
group by 
    f.id_faculty, f.name, s.name;

查询结果：

FACULTY_NAME    |   s.name  |   MAX_STIPEND
----------------+-----------+---------------
IT Faculty      |   Joe     |   50
IT Faculty      |   Lisa    |   10
Architecture    |   Bob     |   60
Journalism      |   Fred    |   5
Architecture    |   Susan   |   5
Journalism      |   Tom     |   40

它使用右、左和内连接做同样的事情。有人可以告诉问题出在哪里吗？

标签： sqloraclefunctionmax

解决方案

首先，您应该为所有连接使用正确的JOIN语法。

其次，可以使用Oracle的keep语法：

select f.name as FACULTY_NAME,
       max(stipend) as MAX_STIPEND,
       max(s.name) keep (dense_rank first order by stipend desc)
from money m join
     student s 
     on  m.student_id = s.id_student join
     faculty f
     on f.id_faculty = s.faculty_id   
group by f.id_faculty, f.name;

推荐阅读