Oracle SQL命令在子查询中的问题！ [英] Oracle SQL order by in subquery problems!

问题描述

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

确实应该是:

解决方案

实际上，排序"仅在最外部的查询中有意义-如果您在子查询中进行排序，则允许外部查询随意对结果进行加扰，因此子查询排序实际上什么也没做.

您似乎只想获取大于pst.last_updated的最小last_updated-当您将其视为最小值(总计)而不是第一行(而不是第一行)时，这样做会更容易(这会带来其他问题，就像如果有两行要绑定next_response怎么办?)

试一下.合理的警告，自从我开始使用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>

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.

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

解决方案

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?)

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.

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>

这篇关于Oracle SQL命令在子查询中的问题！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！