使用關係代數查找每個州中成績最高的學生

假設我有一張學生表，其中包含他們的 ID、年級和州：

-------------------------
| id  |  grade  | state |
------------------------
|  1  |    83   |   CA  |
|  2  |    94   |   TX  |
|  3  |    92   |   WA  |
|  4  |    78   |   CA  |

我想要每個州的最高年級學生的 ID（例如 1、2 和 3），我該怎麼做？

我知道如何找到最大值（可以做叉積（重命名為 R1 和 R2），然後為那些不是最高的人選擇 R1.grade < R2.grade，然後從原始數據庫中減去）。但我對如何為每個州做到這一點感到困惑。

我實際上對關係代數不是很滿意，所以，我將首先使用標準 SQL 來完成，然後使用一個名為RelaX - 關係代數計算器 0.18.2的工具進行翻譯。

首先，你寫的表，我稱它為學生，定義它並填入：

CREATE TABLE students
(
   id INTEGER PRIMARY KEY,
   grade INTEGER,
   state TEXT
) ;

INSERT INTO students
   (id, grade, state)
VALUES
   (1, 83, 'CA'),
   (2, 94, 'TX'),
   (3, 92, 'WA'),
   (4, 78, 'CA') ;

RelaX 會將其轉換為數據集，由以下元組表示：

group: Joan (imported from SQL)

students = {
   id:number, grade:number, state:string
   1        , 83          , 'CA'        
   2        , 94          , 'TX'        
   3        , 92          , 'WA'        
   4        , 78          , 'CA'        
}

為了找到您要查找的內容，我們首先需要一個表格，其中包含表格中的元組(state, grade)，具有每個州的最高等級。此查詢在 SQL 中完成，其中MAX(grade)perstate使用GROUPs BY state. 你可以這樣寫：

SELECT
   state, max(grade) AS grade
FROM 
   students AS s2 
GROUP BY
   state ;

接下來，您需要將JOIN這張表（即命名為max_grades）添加到students一張，並且您將其設置為ON相等的州和相等的等級（即：每個州的最高等級）…

SELECT
   s1.id
FROM
   students AS s1
   JOIN 
   (
       SELECT
           state, max(grade) AS grade
       FROM 
           students
       GROUP BY
           state
   ) AS max_grades 
   ON s1.state = max_grades.state AND s1.grade = max_grades.grade

…這被 RelaX 翻譯成以下關係代數表達式和響應：

π s1.id ρ s1學生 ⨝ s1.state = max_grades.state 和 s1.grade = max_grades.grade ρ max_grades（π狀態，等級γ狀態；MAX（等級）→等級ρ s2學生）

s1.id

1

2

3

注1：

1. 如果一個州的幾個學生獲得最高成績，則此表達式將返回所有學生，而不僅僅是該州的任意一個。

---

選擇：

如果你不能GROUP BY，你可以使用另一個構造：

SELECT DISTINCT
   id
FROM
   students
EXCEPT
SELECT
   s1.id
FROM
   students AS s1
   JOIN students AS s2 ON s1.state = s2.state AND s1.grade < s2.grade

這更符合您的原始想法，儘管我個人覺得不太清楚……

關係代數的翻譯是：

π id學生 - π s1.id ρ s1學生 ⨝ s1.state = s2.state 和 s1.grade < s2.grade ρ s2學生

引用自：https://dba.stackexchange.com/questions/176581