sql - 为什么 "ANY"不能正常工作？

Question

我正在使用 Oracle 10g 学习 SQL。我需要一个返回员工最多的部门的查询，以便在更新语句中使用它。我已经解决了它，但我不明白为什么这个查询不起作用:

select deptno
from (select deptno, 
             count(*) num 
        from emp 
       group by deptno)
where not num < any(select count(deptno) 
                      from emp 
                     group by deptno)

这更让我困惑，因为根据文档，它应该等价并优化为以下内容:

select deptno 
  from (select deptno, 
               count(*) num 
          from emp 
         group by deptno )
 where not exists( select deptno, 
                          count(*) 
                     from emp 
                   having count(*) > num 
                    group by deptno)

那个工作没有错误。以下也有效:

select deptno 
  from (select deptno, 
               count(*) num 
          from emp 
         group by deptno)
 where num = (select max(alias) 
                from (select count(deptno) alias 
                        from emp 
                       group by deptno))


select deptno 
  from emp
 group by deptno
 having not count(deptno) < any( select count(deptno) 
                                   from emp 
                                  group by deptno)

编辑。如果我发布内部选择的返回值，可能会有帮助。

第一个选择返回:

Dept. Number         Employees
30                   6
20                   5
10                   3

最后一个返回(3,5,6)

我分别检查了它们。同样奇怪的是，如果我手动输入值，它会按预期工作，并将返回 30 作为员工最多的部门。

select deptno
from (select deptno, 
             count(*) num 
        from emp 
       group by deptno)
where not num < any(6,5,3)

我正在使用 Oracle 10g 10.2.0.1.0

可能是最后一次编辑。仍然不知道发生了什么，但行为就好像最后一个选择以某种方式返回 null 一样。因此，即使我删除了“not”，它仍然不会选择任何内容。

如果有人感兴趣，我也发现这很有用: TSQL - SOME | ANY why are they same with different names?阅读第一个答案。最好避免使用 any/some, all。

Answer 1

下面是一个类似的例子，它可能会澄清一些事情(标准 SQL，可以很容易地转换为 Oracle):

WITH T
     AS 
     (
      SELECT * 
        FROM (
              VALUES (0),
                     (1), 
                     (2),
                     (NULL)
             ) AS T (c)
     )
SELECT DISTINCT c
  FROM T
 WHERE 1 > ALL (SELECT c FROM T T2);

这返回空集，这是合理的:鉴于表中存在 null，1 > NULL 是 UNKNOWN，因此不知道值 1 是否大于所有值在集合中。

但是，添加 NOT 运算符:

 WHERE NOT 1 > ALL (SELECT c FROM T T2);

返回集合中的所有值，包括空值。乍一看，这似乎是错误的:鉴于 1 > 2 为 FALSE，我们可以肯定地说值 1 不大于集合中的所有值，无论是否为 null。

但是，在这种情况下，NOT 只是翻转较早的结果，即所有无行的相反是所有行! ;)

进一步考虑使用列(而不是文字值 1)的否定比较:

WHERE NOT c > ALL (SELECT c FROM T T2);

这次它返回除空值以外的所有行。