为什么“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

最后编辑，大概。仍然不知道发生了什么，但行为就好像最后一个select以某种方式返回null。所以，即使我删除了'不'，它仍然不会选择任何东西。

如果有人有兴趣，我也发现这个有用： TSQL - SOME | ANY why are they same with different names? 阅读第一个答案。最好避免使用任何/某些，全部。

Answer 1

校正（更新）

not num < any(select ...) 

应该是相同的其他查询。你也可以尝试这个变化：

num >= ALL(select ...) 

但我不明白你为什么给出错误的结果。也许是因为not的优先顺序。你可以trythis代替？：

not (num < ANY(select ...)) 

---

完整查询：

select deptno 
from (select deptno, count(*) num from emp group by deptno) 
where num >= all(select count(deptno) from emp group by deptno) 

和：

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

Answer 2

这里有一个类似的例子可以澄清的东西（标准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); 

这将返回空集合，这是合理的：给定表中存在空值，1 > NULL为UNKNOWN，因此不知道值1是否大于集合中的所有值。

然而，加入NOT操作：

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

返回所有值的集合，包括空值。乍一看这似乎是错误的：鉴于1 > 2是假的，我们可以肯定地说，值1不大于集合中的所有值，而不管空值。

但是，在这种情况下，NOT仅仅是翻转早先的结果，即所有没有行的相反都是行！ ）

使用柱（而不是字面值为1）进一步考虑取反比较：

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

这一次它返回除了空值的所有行。