Oracle SQL order by in 子查询问题！

Oracle SQL order by in subquery problems!

提问by Kevin Parker

I am trying to run a subquery in Oracle SQL and it will not let me order the subquery columns. Ordering the subquery is important as Oracle seems to choose at will which of the returned columns to return to the main query.

我正在尝试在 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

Really should be:

真的应该是：

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

回答by Justin Cave

Both dcw and Dems have provided appropriate alternative queries. I just wanted to toss in an explanation of why your query isn't behaving the way you expected it to.

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

If you have a query that includes a ROWNUM and an ORDER BY, Oracle applies the ROWNUM first and then the ORDER BY. So the query

如果您有一个包含 ROWNUM 和 ORDER BY 的查询，Oracle 首先应用 ROWNUM，然后应用 ORDER BY。所以查询

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

gets an arbitrary 5 rows from the EMPtable and sorts them-- almost certainly not what was intended. If you want to get the "first N" rows using ROWNUM, you would need to nest the query. This query

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

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

sorts the rows in the EMP table and returns the first 5.

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

回答by danwyand

Actually "ordering" only makes sense on the outermost query -- if you order in a subquery, the outer query is permitted to scramble the results at will, so the subquery ordering does essentially nothing.

实际上，“排序”仅对最外层查询有意义——如果您在子查询中进行排序，则允许外层查询随意打乱结果，因此子查询排序基本上没有任何作用。

It looks like you just want to get the minimum last_updated that is greater than pst.last_updated -- its easier when you look at it as the minimum (an aggregate), rather than a first row (which brings about other problems, like what if there are two rows tied for next_response?)

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

Give this a shot. Fair warning, been a few years since I've had Oracle in front of me, and I'm not used to the subquery-as-a-column syntax; if this blows up I'll make a version with it in the from clause.

试一试。公平的警告，自从我在我面前使用 Oracle 以来已经有几年了，而且我不习惯 subquery-as-a-column 语法；如果这失败了，我会在 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>

回答by MatBailie

I've experienced this myself and you have to use ROW_NUMBER(), and an extra level of subquery, instead of rownum...

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

Just showing the new subquery, something like...

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

(
  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

An alternative would be to use MIN instead...

另一种方法是使用 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

回答by Erik

The confirmed answer is plain wrong. Consider a subquery that generates a unique row index number. For example ROWNUMin Oracle.

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

You need the subquery to create the unique record number for paging purposes (see below).

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

Consider the following example query:

考虑以下示例查询：

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

The inner query is the exact same query but DISTINCTon T0. You can't put the ROWNUMon the outer query since the LEFT JOIN(s) could generate many more results.

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

If you could order the inner query (T1.Name DESC) the generated ROWNUMin the inner query would match. Since you cannot use an ORDER BY in the subquery the numbers wont match and will be useless.

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

Thank god for ROW_NUMBER OVER (ORDER BY ...)which fixes this issue. Although not supported by all DB engines.

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

One of the two methods, LIMIT(does not require ORDER) and the ROW_NUMBER() OVERwill cover most DB engines. But still if you don't have one of these options, for example the ROWNUMis your only option then a ORDER BYon the subquery is a must!

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