这两个简单的SQL查询有什么区别？

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)
SELECT DISTINCT D.managerid

FROM Dept D

WHERE 1000000 < ALL (SELECT D2.budget
 FROM Dept D2
 WHERE D2.managerid = D.managerid )


other one:
SELECT DISTINCT managerid FROM dept WHERE dept.budget > 1000000

They both get the id of managers who work in departments with budgets higher than 1million. Sorry about the formatting.

他们都得到了在预算高于100万的部门工作的经理人的身份。抱歉格式化。

1 个解决方案

#1

The difference is with the ALL operator. Read here about the ALL operator when it is used with the comparison operators.

不同之处在于ALL运算符。当与比较运算符一起使用时,请在此处阅读有关ALL运算符的信息。

The difference will be for the cases where a manager oversees multiple departments.

不同之处在于经理监督多个部门的情况。

If you have a manager that looks after these two departments:

如果你有一个管理这两个部门的经理:

declare @Dept table (did integer, dname varchar(20), budget real, managerid integer)
insert into @Dept values(1, 'dept1', 10, 1),(2, 'dept2', 20000000, 1)

Then the first query will not give you any results, as the manager with the managerid=1 has a department with budget over and less then 1000000

然后第一个查询不会给你任何结果,因为managerid = 1的经理有一个预算超过且少于1000000的部门

SELECT DISTINCT D.managerid
FROM @Dept D
WHERE 1000000 < ALL (SELECT D2.budget
 FROM @Dept D2
 WHERE D2.managerid = D.managerid )

managerid
-----------
(0 row(s) affected)

The second query happily returns the result:

第二个查询愉快地返回结果:

SELECT DISTINCT managerid FROM @dept WHERE budget > 1000000

managerid
-----------
1
(1 row(s) affected)

UPDATE: to make the two queries equivalent you can use the ANY operator:

更新:要使两个查询等效,您可以使用ANY运算符:

SELECT DISTINCT D.managerid
FROM @Dept D
WHERE 1000000 < ANY (SELECT D2.budget
 FROM @Dept D2
 WHERE D2.managerid = D.managerid )
managerid
-----------
1
(1 row(s) affected)

I have added the picture from the URL above here, for completeness:

为了完整性,我在这里添加了上面URL中的图片:

#1