在SQL嵌套查询中比较同一张表

Question

如果一个数据表如下所示：

Name cgpa dept
a     4.0   x
b     3.9   x
c     3.8   x
d     3.8   y
e     4.0   y
f     3.9   z
g     3.6   z

选择那些成绩比同一院系的其他学生都高的学生的姓名和平均学分绩点（cgpa）。

select NAME, CGPA
from STD_INFO T1
where CGPA > all
(
select CGPA 
from STD_INFO T2
where lower(T2.DEPT)=lower(T1.DEPT)
and T2.STD_NO != T1.STD_NO
);

我需要理解完整的模拟……请帮助我了解这个……当我们比较相同的表命名（如上所示t1，t2）时，这被称为什么？

- user2951209

---

如果两个学生的GPA相同且都是最高的，这种方法就行不通了。你会显示哪个名字？ - LCIII

其实我对SQL很陌生...我在网上浏览了一下模拟的内容，但没有什么收获...如果您能详细解释一下这里发生了什么...我的意思是这个模拟。 - user2951209

我的解决方案将返回一个系中所有最高的学生，如果他们的 CGPA 相同。 - Ron Smith

Answer 1

您想要找到每个DEPT中的max(CGPA)，并将该数据集与您的STD_INFO表进行inner join：

select s1.NAME,s1.CGPA
from STD_INFO as s1
  inner join (select DEPT,max(CGPA) as CGPA
              from STD_INFO
              group by DEPT) as s2
    on s2.DEPT = s1.DEPT
    and s2.CGPA = s1.CGPA;

SQL FIDDLE

SQL FIDDLE

是一个在线 SQL 编辑器，可以帮助开发人员快速创建、测试和共享 SQL 查询。它提供了一个简单易用的界面，让用户可以轻松地输入 SQL 代码并运行查询。此外，它还支持多种数据库平台，包括 MySQL、PostgreSQL、Oracle 等。如果你需要一个方便的工具来处理 SQL 查询，SQL FIDDLE 绝对是一个不错的选择。

- Ron Smith

---

这与原始查询产生不同的结果，当一个系中有多个最高cgpa的学生时。（详见我在LCIII答案上的评论。） - Thorsten Kettner

Answer 2

你需要循环遍历表std_info中的所有记录，将其称为t1。对于每个记录，您需要检查它们的cgpa是否大于子查询中选择的所有cgpa。

在子查询中，您需要再次从表std_info中进行选择，这次将其称为t2。因此，对于一个t1记录，您需要选择具有相同部门（lower(T2.DEPT)=lower(T1.DEPT)）但不同学生编号（T2.STD_NO != T1.STD_NO）的所有记录。

如果该部门中的所有其他学生的cgpa都低于您当前正在查看的学生，则将该学生保留在结果列表中，否则将其从中删除。

因此，在检查完所有学生之后，您将保留那些cgpa高于同一部门中所有其他学生的学生。

我能想到的唯一“名称”是：这是对同一表的相关子查询。

使用> ALL（或SOME或ANY）非常罕见。通常情况下，您会使用EXISTS或IN代替：

select NAME, CGPA
from STD_INFO T1
where not exists
(
  select *
  from STD_INFO T2
  where lower(T2.DEPT)=lower(T1.DEPT)
  and T2.STD_NO != T1.STD_NO
  and T2.CGPA  >= T1.CGPA 
);

- Thorsten Kettner

Answer 3

选择那些CGPA高于同一系其他学生的学生的姓名和CGPA:

如果两个学生的GPA相同，您需要同时显示两个记录，否则系统将不知道要显示哪个姓名，因为他们都是该系的“最佳”。就像在这个例子中一样：

Name  cgpa  dept
a     4.0   x
b     4.0   x
r     3.1   x
c     3.8   y
e     3.5   y
d     3.9   z

这个查询：

SELECT t.* FROM table AS t
INNER JOIN (
SELECT MAX(cgpa) AS 'cgpa',dept FROM table AS t2
GROUP BY dept) AS innerTable
ON innerTable.cgpa = t.cgpa AND innerTable.dept = t.dept

会返回这个：

Name cgpa dept
a    4    x
b    4    x
c    3.8  y
d    3.9  z

这里的答案与此问题非常相似：如何在T/SQL中按一列分组并检索另一列的最小值行？

- LCIII

---

如果有两个或更多的学生具有最高的cgpa，则“cgpa高于同一系所有其他学生”对他们来说并不正确，他们不会在原始查询中显示。这没有任何问题。您正在显示一个略微不同的查询，其中显示所有cgpa在其部门中达到最大值的学生。 - Thorsten Kettner