SQL SubQueries执行

Question

“查找平均薪资最高的部门。”。

select dept_name
from instructor
group by dept_name
having avg (salary) >= all (select avg (salary)
                            from instructor
                            group by dept_name)

基于上面嵌套的子查询，我有点困惑它是如何执行的。有人能确认我的解释是否正确吗？

首先，第二行from instructor将运行，根据该关系，元组将按dept_name分组。然后，在由dept_name分组的新形成的关系上，您将检查每个组的平均薪资，并将值与在子查询中形成的新的指导员关系中的所有其他平均薪资进行比较。这是正确的吗？子查询将形成一个新的关系，因此您实际上将有两个独立的关系被操作？

(select avg (salary)
from instructor
group by dept_name)

在上面的子查询中，我最初认为子句组中没有聚合的任何属性都有出现在select子句中。但是在这种情况下，为什么dept_name不在子查询的select子句中呢？

Answer 1

查询以两段形式运行。第一个是嵌套查询(实际上是作为一个左半连接执行的)。这将返回每个dept_name的平均薪资。

--Nested Query Returns all dept_name average salaries
select avg (salary)
from #instructor
group by dept_name

然后将其与使用"全“运算符在主select语句中提取的平均薪资进行比较。这相当于与所有部门的最高平均工资进行比较。把它想象成以下几点：

Main Select       Nested Comparison
Math 200     >=   Max({Math 200, Bio 250, English 100})
Bio 250      >=   Max({Math 200, Bio 250, English 100})
English 100  >=   Max({Math 200, Bio 250, English 100})

因此，在查询的情况下，总是只返回“平均薪资”等于或高于最高平均薪资的部门。下面是一个简单的版本，以发挥和帮助您的理解。

Drop Table #instructor 
Create Table #instructor (dept_name nvarchar(200), salary money)
Insert Into #instructor 
Select 'Math', 200
Union Select 'English', 150
Union Select 'Biology', 275
Union Select 'Computer Science', 300

select dept_name
from #instructor
group by dept_name
having avg (salary) >= all(select avg (salary)
                            from #instructor
                            group by dept_name)

你会注意到，在上面的例子中，唯一出现的部门是计算机科学。但是如果你把生物学改成300，那么生物学和计算机科学都会出现。

Answer 2

group by中的列不必出现在select中。在这种情况下，这是不可能的，因为查询只需要返回一个列。

此查询的结果：

select avg(salary)
from instructor
group by dept_name

只是列出每个部门的平均工资。作为一个查询本身，如果您也有部门名称，这将更有用。但在这种情况下这是不必要的。