sql - 比较 SQL 中的两个关系

Question

我刚开始学习SQL，这是老师在在线课程中给出的演示，效果很好。该声明正在寻找“具有相同 GPA 的其他学生数量等于具有相同 sizeHS 的其他学生数量的学生”：

select *
from Student S1
where (
    select count(*)
    from Student S2
    where S2.sID <> S1.sID and S2.GPA = S1.GPA
) = (
    select count(*)
    from Student S2
    where S2.sID <> S1.sID and S2.sizeHS = S1.sizeHS
);

似乎在这个where子句中，我们正在比较两个关系（因为子查询的结果是一个关系），但大多数时候我们是在比较属性（据我所见）。

因此，我正在考虑在比较两个 RELATIONS 时 RELATION 应该包含多少个属性和多少个元组。如果没有，当有多个属性或多个元组时，我们如何比较两个关系，我们会得到什么结果？ 注意：

学生关系有4 个属性：sID、sName、GPA、sizeHS。这是数据：

+-----+--------+-----+--------+
| 标识 | 姓名 | 平均绩点 | 尺寸HS |
+-----+--------+-----+--------+
| 123 | 艾米 | 3.9 | 1000 |
| 234 | 鲍勃 | 3.6 | 1500 |
| 345 | 克雷格 | 3.5 | 500 |
| 第456章 多丽丝 | 3.9 | 1000 |
| 第567章 爱德华 | 2.9 | 2000 |
| 第678章 费 | 3.8 | 200 |
| 第789章 加里 | 3.4 | 800 |
| 987 | 海伦 | 3.7 | 800 |
| 第876章 艾琳 | 3.9 | 400 |
| 765 | 周杰伦 | 2.9 | 1500 |
| 第654章 艾米 | 3.9 | 1000 |
| 第543章 克雷格 | 3.4 | 2000 |
+-----+--------+-----+--------+

这个查询的结果是：

+-----+--------+-----+---------+
| 标识 | 姓名 | 平均绩点 | 尺寸HS |
+-----+--------+-----+---------+
| 345 | 克雷格 | 3.5 | 500 |
| 第567章 爱德华 | 2.9 | 2000 |
| 第678章 费 | 3.8 | 200 |
| 第789章 加里 | 3.4 | 800 |
| 765 | 周杰伦 | 2.9 | 1500 |
| 第543章 克雷格 | 3.4 | 2000 |
+-----+--------+-----+---------+

标签： sqlsqlite

Answer 1

因为子查询的结果是一个关系

关系是我们在数据库中称为表的科学名称，我更喜欢“表”这个名称而不是“关系”。一张桌子很容易想象。例如，我们从学校的时间表中了解它们。是的，我们将这里的东西放在一个表中（日期和时间以及学校教授的科目），但我们也可以将表与表相关联（学生的时间表与教室表、整体学科时间表和教师的时间表） . 因此，RDBMS 中的表也相互关联（因此称为关系数据库管理系统）。我发现表的名称关系非常混乱（许多人使用“关系”这个词来描述表代替）。

所以，是的，查询结果本身又是一个表（“关系”）。我们当然可以从表格中选择：

select * from (select * from b) as subq;

然后有一些标量查询只返回一行和一列。select count(*) from b是这样一个查询。虽然这仍然是一张我们可以选择的表格

select * from (select count(*) as cnt from b) as subq;

我们甚至可以在我们通常有单个值的地方使用它们，例如在 select 子句中：

select a.*, (select count(*) from b) as cnt from a;

在您的查询中，您的 where 子句中有两个标量子查询。

对于子查询，还有另一个区别：我们有相关和不相关的子查询。我刚刚展示的最后一个查询包含一个不相关的子查询。它为每个结果行选择 b 行的计数，无论该行包含什么，否则。另一方面，相关子查询可能如下所示：

select a.*, (select count(*) from b where b.x = a.y) as cnt from a;

在这里，子查询与主表相关。对于每个结果行，我们通过 查找与我们正在显示的 a 行匹配的 b 行的where b.x = a.y计数，因此计数因行而异（但对于共享相同 y 值的行，我们会得到相同的计数）。

您的子查询也是相关的。与 select 子句一样，where 子句一次处理一行（为了保留或关闭它）。所以我们一次只看一个学生 S1。where S2.sID <> S1.sID对于这个学生，我们计算具有相同 GPA ( ) 的其他学生 (S2, and S2.GPA = S1.GPA) 并计算具有相同 sizeHS 的其他学生。我们只保留学生 (S1)，其中 GPA 相同的学生数量与 sizeHS 相同的学生数量完全相同。

---

更新

与处理多个元组一样

select *
from Student S1
where (
    select count(*), avg(grade)
    from Student S2
    where S2.sID <> S1.sID and S2.GPA = S1.GPA
) = (
    select count(*), avg(grade)
    from Student S2
    where S2.sID <> S1.sID and S2.sizeHS = S1.sizeHS
);

这在某些 DBMS 中是可能的，但在 SQL Server 中是不可能的。SQL Server 不知道元组。

但是还有其他方法可以达到同样的效果。您可以只添加两个子查询：

select * from student s1
where (...) = (...) -- compare counts here
and (...) = (...) -- compare averages here

或者获取FROM子句中的数据，然后进行处理。例如：

select *
from Student S1
cross apply
(
    select count(*) as cnt, avg(grade) as avg_grade
    from Student S2
    where S2.sID <> S1.sID and S2.GPA = S1.GPA
) sx
cross apply
(
    select count(*) as cnt, avg(grade) as avg_grade
    from Student S2
    where S2.sID <> S1.sID and S2.sizeHS = S1.sizeHS
) sy
where sx.cnt = sy.cnt and sx.avg_grade = sy.avg_grade;