Oracle SQL order by 子查询问题！

Question

我正在尝试在 Oracle SQL 中运行子查询，但它不允许我对子查询列进行排序。对子查询进行排序很重要，因为 Oracle 似乎随意选择返回的列中的哪一个返回到主查询。

<代码>

select ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
        (select last_updated from mwcrm.process_state_transition subpst
            where subpst.last_updated > pst.last_updated
            and subpst.process_state = ps.id
            and rownum = 1) as next_response
        from mwcrm.process_state ps, mwcrm.process_state_transition pst
        where ps.created_date > sysdate - 1/24
        and ps.id=pst.process_state
        order by ps.id asc

确实应该是：

select ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
        (select last_updated from mwcrm.process_state_transition subpst
            where subpst.last_updated > pst.last_updated
            and subpst.process_state = ps.id
            and rownum = 1
            order by subpst.last_updated asc) as next_response
        from mwcrm.process_state ps, mwcrm.process_state_transition pst
        where ps.created_date > sysdate - 1/24
        and ps.id=pst.process_state
        order by ps.id asc

Answer 1

dcw 和民主党都提供了适当的替代查询。我只是想解释一下为什么您的查询没有按照您预期的方式运行。

如果您的查询包含 ROWNUM 和 ORDER BY，则 Oracle 首先应用 ROWNUM，然后应用 ORDER BY。因此，查询

SELECT *
  FROM emp
 WHERE rownum <= 5
 ORDER BY empno

从 EMP 表中获取任意 5 行并对它们进行排序——几乎可以肯定不是预期的结果。如果您想使用 ROWNUM 获取“前 N”行，则需要嵌套查询。此查询

SELECT *
  FROM (SELECT *
          FROM emp
         ORDER BY empno)
 WHERE rownum <= 5

对 EMP 表中的行进行排序并返回前 5 行。

Answer 2

实际上，“排序”仅对最外层查询有意义 - 如果您在子查询中排序，则允许外部查询随意打乱结果，因此子查询排序本质上不执行任何操作。

看起来您只想获得大于 pst.last_updated 的最小值 last_updated ——当您将其视为最小值（聚合）而不是第一行（这会带来其他问题，例如如果有两行与 next_response 相关？）

试一试。公平警告，距离我使用 Oracle 已经有几年了，我还不习惯子查询即列语法；如果这个问题发生了，我会在 from 子句中制作一个版本。

select
    ps.id, ps.created_date, pst.last_updated, pst.from_state, pst.to_state,
    (   select min(last_updated)
        from mwcrm.process_state_transition subpst
        where subpst.last_updated > pst.last_updated
          and subpst.process_state = ps.id) as next_response
from <the rest>

Answer 3

我自己经历过这种情况，你必须使用 ROW_NUMBER() 和一个额外的子查询级别，而不是 rownum...

只是显示新的子查询，例如...

(
  SELECT
    last_updated
  FROM
  (
    select
      last_updated,
      ROW_NUMBER() OVER (ORDER BY last_updated ASC) row_id
    from
      mwcrm.process_state_transition subpst
    where
      subpst.last_updated > pst.last_updated
      and subpst.process_state = ps.id
  )
    as ordered_results
  WHERE
    row_id = 1
)
  as next_response

另一种选择是使用 MIN 代替...

(
  select
    MIN(last_updated)
  from
    mwcrm.process_state_transition subpst
  where
    subpst.last_updated > pst.last_updated
    and subpst.process_state = ps.id
)
  as next_response

Answer 4

已确认的答案是完全错误的。
考虑一个生成唯一行索引号的子查询。
例如 Oracle 中的 ROWNUM。

您需要子查询来创建用于分页目的的唯一记录号（见下文）。

考虑以下示例查询：

SELECT T0.*, T1.* FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
JOIN 
(
SELECT DISTINCT T0.*, ROWNUM FROM T0 LEFT JOIN T1 ON T0.Id = T1.Id
WHERE (filter...)
)
WHERE (filter...) AND (ROWNUM > 10 AND ROWNUM < 20)
ORDER BY T1.Name DESC

内部查询与 T0 上的查询完全相同，但DISTINCT。
您不能将 ROWNUM 放在外部查询上，因为 LEFT JOIN 可能会生成更多结果。

如果您可以对内部查询 (T1.Name DESC) 进行排序，则内部查询中生成的 ROWNUM 将匹配。
由于您不能在子查询中使用 ORDER BY，因此数字将不会匹配并且毫无用处。

感谢上帝 ROW_NUMBER OVER (ORDER BY ...) 解决了这个问题。
尽管并非所有数据库引擎都支持。

两种方法之一，LIMIT（不需要ORDER）和ROW_NUMBER() OVER 将覆盖大多数数据库引擎。
但如果您没有这些选项之一，例如 ROWNUM 是您唯一的选项，那么子查询上的 ORDER BY 是必须的！