oracle - 使用开发人员桌面和开发人员 Web 的 Oracle 数据库中没有 GROUP BY 的 HAVING 子句

Question

根据标准做法，我的理解是 HAVING 将与 GROUP BY 一起用于过滤条件，而 WHERE 应该用于一般的逐行过滤条件。
但是，有online discussions关于是否使用 HAVING 作为 WHERE 子句的超集，结论不一。也就是说，它是否可以在没有 GROUP BY 的情况下使用，在这种情况下它可以用作 WHERE 子句。
我想了解跨 Oracle、Microsoft SQL server、MySQL、PostGreSQL 和其他工具使用 HAVING 子句的行业惯例。
执行此查询时我观察到的一件有趣的事情:

SELECT *
FROM SH.SALES
WHERE amount_sold > 1000
HAVING amount_sold < 2000;

在 Oracle SQL 开发人员桌面中执行而在 Oracle SQL 开发人员 Web 中成功运行时会出错。

Answer 1

这是一个很好的问题和谜题!
Oracle SQL Developer Web 通过 Oracle REST 数据服务 (ORDS) 提供。有一个 RESTful Web 服务用于执行“临时”SQL 语句和脚本。
我们不是在一次调用中从查询中取回所有行，而是对它们进行分页。我们坚持使用 RESTful 方式，并在一次调用和响应中完成所有工作，而不是保持结果集打开和进程运行。
我们如何做到这一点？
好吧，当您从问题中输入该查询并在后端执行它时，这实际上并不是执行的内容。
我们用另一个 SELECT 包装该查询，并使用 ROW_NUMBER() OVER 分析函数调用。这使我们可以“窗口化”查询结果，在这种情况下，在第 1 行和第 26 行之间，或者该查询的前 25 行之间，即您的查询。

SELECT *
  FROM (
       SELECT Q_.*,
              ROW_NUMBER() OVER(
                      ORDER BY 1
              ) RN___
         FROM (
              select * 
from sh.sales
where amount_sold > 1000
having amount_sold < 2000
       ) Q_
)
 WHERE RN___ BETWEEN :1 AND :2

好的，但那又怎样？
好吧，优化器发现这个查询仍然可以运行，即使有子句不合适。
在搜索最佳执行计划之前，优化器总是可以自由地重新安排查询。
在这种情况下，10053 跟踪显示如下查询来自 SQL Dev Web(我使用的是 EMP，但同样适用于任何表)

SELECT *
  FROM (
       SELECT Q_.*,
              ROW_NUMBER() OVER(
                      ORDER BY 1
              ) RN___
         FROM (
              SELECT *
              FROM emp
              WHERE sal > 1000
HAVING sal < 2000
       ) Q_
)
 WHERE RN___ BETWEEN :1 AND :2

在针对计划进行优化之前，已在内部转换为以下内容。

SELECT 
  subq.EMPNO EMPNO,
  subq.ENAME ENAME,
  subq.JOB JOB,
  subq.MGR MGR,
  subq.HIREDATE HIREDATE,
  subq.SAL SAL,subq.COMM COMM,
  subq.DEPTNO DEPTNO,
  subq.RN___ RN___ 
FROM  
  (SELECT 
      EMP.EMPNO EMPNO,
      EMP.ENAME ENAME,
      EMP.JOB JOB,EMP.MGR MGR,
      EMP.HIREDATE HIREDATE,
      EMP.SAL SAL,
      EMP.COMM COMM,
      EMP.DEPTNO DEPTNO,
      ROW_NUMBER() OVER ( ORDER BY  NULL ) RN___ 
   FROM EMP EMP 
   WHERE EMP.SAL>1000 AND TO_NUMBER(:B1)>=TO_NUMBER(:B2)
   ) subq 
WHERE subq.RN___>=TO_NUMBER(:B3) 
AND subq.RN___<=TO_NUMBER(:B4)

请注意，HAVING 已从查询中转换/优化，从而使其进入执行阶段。
少校👏到 @connor-mcdonald AskTom 因帮助我解决这个问题而出名。
这就是为什么它可以在 SQL Developer Web 中工作，但不能在 SQL Developer Desktop 中工作的原因，在 SQL Developer Desktop 中，查询完全按照所写的方式执行。