SQL ANY 查询 - 有效语法

Question

尝试使用以下“ANY”语法并收到错误：

SELECT DISTINCT Em.ename
FROM Emp Em, Dept D
WHERE Em.eid In (Select D.managerid 
                 FROM Dept D
                 GROUP BY D.managerid
                 HAVING EVERY (D.budget > 1000000) AND ANY (D.budget < 5000000)
             );

错误输出：

psql:queries.sql:28: ERROR:  syntax error at or near "ANY"
LINE 6:      HAVING EVERY (D.budget > 1000000) AND ANY (D.budget < 5...
                                                     ^

编辑：我正在使用 Postgres 并使用 Psql 进行连接。

Answer 1

你可以让它变得更简单，不是吗？

SELECT DISTINCT Em.ename
FROM Emp Em, Dept D
WHERE Em.eid In (Select D.managerid 
                 FROM Dept D
                 WHERE D.budget > 1000000             
                 GROUP BY D.managerid
                 HAVING MIN(D.budget) < 5000000
             );

或

SELECT DISTINCT Em.ename
FROM Emp Em, Dept D
WHERE Em.eid In (Select D.managerid 
                 FROM Dept D
                 GROUP BY D.managerid
                 HAVING EVERY (D.budget > 1000000) AND MIN(D.budget) < 5000000
             );

或

SELECT DISTINCT Em.ename
FROM Emp Em, Dept D
WHERE Em.eid In (Select D.managerid 
                 FROM Dept D
                 GROUP BY D.managerid
                 HAVING MIN (D.budget) > 1000000 AND MIN(D.budget) < 5000000
             );

Answer 2

SELECT Em.ename
FROM Emp Em                                            --- only Emp table
WHERE Em.eid In (Select D.managerid 
                 FROM Dept D
                 GROUP BY D.managerid
                 HAVING ALL (D.budget > 1000000)       --- ALL
                   AND ANY (D.budget < 5000000)
             );