Oracle sql查询Order By给出了不同的结果

Output of

Query 1:

select id from users
order by case when DEVIATION_LEVEL=2863 then 1 else 2 end

800019  
800030  
800040  
800003  
800007  
800015  
800025  
800026....etc

Output of Query 2:

查询2的输出:

select id from
    (select id from users
     order by case when DEVIATION_LEVEL=2863 then 1 else 2 end)
where rownum<=16;

800019
800030
800028
800020
800021
800018
800012
800161...etc

Why the order changes in 2nd query? Please suggest correct solution to just limit the size of the first query result.

为什么订单在第二个查询中更改?请建议正确的解决方案,以限制第一个查询结果的大小。

3 个解决方案

#1

The reason is that ORDER BY cannot guarantee the ordering on duplicate values.

原因是ORDER BY无法保证重复值的排序。

In your query, put the DEVIATION_LEVEL in the column list of the select and then you will understand that the order is just not same when they are duplicate values.

在您的查询中,将DEVIATION_LEVEL放在select的列列表中,然后您将了解当它们是重复值时,顺序是不相同的。

For example,

Query 1

SQL> SELECT empno, deptno FROM emp ORDER BY CASE WHEN deptno=10 THEN 1 ELSE 2 END;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10
      7566         20
      7654         30
      7698         30
      7900         30
      7788         20
      7369         20
      7844         30
      7876         20
      7521         30
      7499         30
      7902         20

14 rows selected.

Query 2

SQL> SELECT empno, deptno
  2  FROM
  3    (SELECT empno, deptno FROM emp ORDER BY CASE WHEN deptno=10 THEN 1 ELSE 2 END
  4    )
  5  WHERE rownum<=5;

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7934         10
      7839         10
      7369         20
      7654         30

SQL>

So, the ordering in the second case when ROWNUM is applied, it is picked randomly among the similar values.

因此,当应用ROWNUM时,在第二种情况下的排序,在相似值中随机选取。

Look at the first three ordered rows:

查看前三个有序行:

Output 1

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7839         10
      7934         10

Output 2

     EMPNO     DEPTNO
---------- ----------
      7782         10
      7934         10
      7839         10

ORDER BY deptno will not guarantee same order every time. In above query, if you want a particular order, then make an ORDER BY on another column too, i.e. empno.

ORDER BY deptno不会保证每次都有相同的订单。在上面的查询中,如果你想要一个特定的订单,那么也可以在另一列上进行ORDER BY,即empno。

ORDER BY empno, deptno

If you compare both the outputs, there is no guarantee that ordering will be same always since the deptno is same as 10 in all three rows. When you have similar values, and if you order them, it is just like picking them in random.

如果比较两个输出,则不能保证排序总是相同,因为deptno在所有三行中都是10。如果您有相似的值,并且如果您订购它们,就像随机选择它们一样。

#2

When you perform a SELECT query without an ORDER BY clause the order of the result is undetermined. If you want or need to have a consistent ordering behavior then use the ORDER BY clause at the top level SELECT.

在不使用ORDER BY子句的情况下执行SELECT查询时,结果的顺序未确定。如果您想要或需要具有一致的排序行为,请使用*SELECT的ORDER BY子句。

There is however the exception in oracle when you are limiting the rows with the ROWNUM field. In that case the ROWNUM filter would reduce the result set before applying the order by clause, thus removing rows that should have come first.

但是,当您使用ROWNUM字段限制行时,oracle中存在异常。在这种情况下,ROWNUM过滤器会在应用order by子句之前减少结果集,从而删除应该首先出现的行。

select id from users
order by case when DEVIATION_LEVEL=2863 then 1 else 2, id;

and

select id from
    (select id from users
     order by case when DEVIATION_LEVEL=2863 then 1 else 2 end, id)
where rownum<=16;

#3

ORDER BY in a subquery does not guarantee the results; SELECT * FROM table ORDER BY 1; is not same as SELECT * FROM (SELECT * FROM table ORDER BY 1);

子查询中的ORDER BY不保证结果; SELECT * FROM表ORDER BY 1;与SELECT * FROM(SELECT * FROM table ORDER BY 1)不同;

#1