主页 / dba / 问题 / 176581
Accepted
SS'
Asked: 2017-06-19 08:21:15 +0800 CST

使用关系代数查找每个州中成绩最高的学生

  • 772

假设我有一张学生表，其中包含他们的 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，然后从原始数据库中减去）。但我对如何为每个州做到这一点感到困惑。

relational-theory relational-algebra

2 个回答

  1. Best Answer

    我实际上对关系代数不是很满意，所以，我将首先使用标准 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学生

    在此处输入图像描述

    • 2

  2. 编辑：我没有仔细阅读并给出了 SQL 的答案，以防张贴者感兴趣。如果您不感兴趣，请告诉我，然后我将删除。

    SQL：我认为使用派生表是可行的。让我们称您的表为“学生”：

    SELECT id FROM Students JOIN
  (SELECT State, Max(Grade) AS MaxGrade FROM Students GROUP BY State ) AS T 
  ON T.State=Students.State  
WHERE Students.Grade >= T.MaxGrade
 ;
    • 1

相关问题