时间戳

首页 > 解决方案 > ORACLE DB 查询 - 教职员工的最高津贴

sql - ORACLE DB 查询 - 教职员工的最高津贴

问题描述

所以，我在大学有一项任务，从一张有津贴的桌子上获得每个教师的最大津贴。

教师表是：

ID_FACULTY FACULTY_NAME                   DEAN                 TELEPHON
---------- ------------------------------ -------------------- --------
        10 Informacijas tehnologiju       Vitols               63023095
        11 Lauksaimniecibas               Gaile                63022584
        12 Tehniska                       Dukulis              53020762
        13 Partikas tehnologijas          Sabovics             63021075

钱表是：

 ID_PAYOUT STUDENT_ID PAYOUT_DA    STIPEND COMPENSATION
---------- ---------- --------- ---------- ------------
       100          1 24-SEP-20      45.25           15
       101          7 20-SEP-20     149.99            0
       102          3 18-SEP-20        100            0
       103         17 02-SEP-20      90.85           20
       104          9 03-SEP-20         85           20
       105         19 09-SEP-20      70.75            0
       106         25 15-SEP-20         55           15
       107         17 17-SEP-20     105.54            0
       108         15 22-SEP-20         94            0
       109         27 28-SEP-20        100           20

学生表是：

ID_STUDENT SURNAME                   NAME                 COURSE_YEAR FACULTY_ID BIRTHDATE
---------- ------------------------- -------------------- ----------- ---------- ---------
         1 Lapa                      Juris                          4         13 27-SEP-96
         3 Vilkauss                  Fredis                         2         10 17-MAY-99
         5 Karlsone                  Rasa                           1         11 13-MAR-00
         7 Grozitis                  Guntars                        3         12 16-APR-97
         9 Sonciks                   Jurgis                         2         10 17-MAR-99
        11 Berzajs                   Olafs                          3         10 14-FEB-97
        13 Vike                      Ilvija                         2         13 14-MAY-99
        15 Baure                     Inga                           3         11 12-APR-97
        17 Viskers                   Zigmunds                       2         13 15-AUG-99
        19 Talmanis                  Harijs                         3         13 15-JUL-97
        21 Livmanis                  Indulis                        1         10 19-JAN-00
        23 Shaveja                   Uva                            2         13 18-FEB-98
        25 Lacis                     Guntis                         4         10 17-SEP-96
        27 Liepa                     Guna                           4         11 18-AUG-96
        29 Klava                     Juris                          2         10 19-MAY-98

我尝试了许多查询变体，我认为我什至尝试了所有可能的连接组合，但我无法获得必要的结果。

我的一个查询如下所示：

SQL> SELECT ROW_NUMBER() OVER (ORDER BY surname) "Nr.",
f.faculty_name,
s.surname,
s.name,
MAX(m.stipend)
FROM faculty f, student s INNER JOIN money m ON s.id_student = m.student_id
WHERE s.faculty_id = f.id_faculty
GROUP BY f.faculty_name, s.surname, s.name 
ORDER BY s.surname;

这给了我以下结果：

       Nr. FACULTY_NAME                   SURNAME                   NAME                 MAX(M.STIPEND)
---------- ------------------------------ ------------------------- -------------------- --------------
         1 Lauksaimniecibas               Baure                     Inga                             94
         2 Tehniska                       Grozitis                  Guntars                      149.99
         3 Informacijas tehnologiju       Lacis                     Guntis                           55
         4 Partikas tehnologijas          Lapa                      Juris                         45.25
         5 Lauksaimniecibas               Liepa                     Guna                            100
         6 Informacijas tehnologiju       Sonciks                   Jurgis                           85
         7 Partikas tehnologijas          Talmanis                  Harijs                        70.75
         8 Informacijas tehnologiju       Vilkauss                  Fredis                          100
         9 Partikas tehnologijas          Viskers                   Zigmunds                     105.54

9 rows selected.

因此，此任务的目标是检索授予某个学院学生的最大助学金金额。有人可以告诉我我在这里做错了什么吗？

标签： sqloracle

解决方案

每个教师的最大数量：

SELECT 
f.faculty_name,
MAX(m.stipend)
FROM    
   faculty f
   INNER JOIN student s ON s.faculty_id = f.id_faculty 
   INNER JOIN money m ON s.id_student = m.student_id
GROUP BY f.faculty_name

最大金额和所有其他详细信息：

SELECT * FROM
(
 SELECT
   ROW_NUMBER() OVER (PARTITION BY f.faculty_name ORDER BY m.stipend desc) rn,
   f.*,
   s.*,
   m.*
 FROM  
   faculty f
   INNER JOIN student s ON s.faculty_id = f.id_faculty 
   INNER JOIN money m ON s.id_student = m.student_id
) x
WHERE x.rn = 1

注意事项：

  • 不要使用旧式连接；如果您曾经在 FROM 块中写过一个table_name, other_table_name，那么您使用的是旧式连接。不要这样做；大约 30 年前，他们成了坏消息
  • 当您有一个 max-n-per-group 时，您可以指定该组的详细程度。如果你GROUP BY s.first_name, s.last_name, f.faculty_name那么你的小组是“名字/姓氏/教师的每一个独特的组合，所以你在小组中获得多个项目的唯一方法是如果数学中有两个约翰史密斯。如果小组是整个数学, 那么教师名称（以及任何其他与它 1:1 唯一相关的内容，如教师 ID）就是您可以放入组中的所有内容。任何不在组中的内容都必须在聚合中，例如 MAX
  • 当您还需要其他详细信息时，您可以对数据进行分组和最大化，然后将这个分组后的数据连接回原始数据以将其用作过滤器，或者使用类似于此处的方法，使用 row_number 或 rank，并使用分区（这就像一个自动加入的分组摘要）。这里没有组；行编号就像一个组，因为它从每个不同的教员的 1 重新开始，并随着津贴的减少而递增。这意味着最高津贴总是在第 1 行。
  • 与使用您加入的 groupmax 来获取详细信息不同，row_number 路由不会产生具有最高津贴的重复行

推荐阅读