当前位置: 首页 > 编程笔记 >

Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出

花阳秋
2023-03-14
本文向大家介绍Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出,包括了Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出的使用技巧和注意事项,需要的朋友参考一下

ORDER BY非稳定的排序
提一个问题: oracle在order by 排序时,是稳定排序算法吗? 发现用一个type进行排序后,做分页查询,第一页的数据和第二页的数据有重复 怀疑是order by 时,两次排列的顺序不一致

看到业务描述的问题可以得到的结论order by排序不稳定,还有第一个印象就是,type肯定是不唯一的,并且没有索引吧。

这里先科普下排序的稳定性,举个最简单的例子,1,2,3,1,4,5 排序 排序的结果是1,1,2,3,4,5,这时候观察这个1,如果第一个1还是排序前的那个1,那么算法是稳定的。也就是说相等数在排序后不发生交换。

还记得以前数据结构中的几种排序算法:

选择排序复杂度为n*n,不稳定排序,

快速排序复杂度为n*n,不稳定排序,

希尔排序复杂度为nlogn,不稳定排序,

堆排序复杂度为nlogn,不稳定排序,

冒泡排序复杂度为n*n,稳定排序,

插入排序复杂度为n*n,稳定排序,

归并排序复杂度为nlogn,稳定排序

基数排序的复杂度和位数是有关的,是稳定排序。

好了回到正题,本机测试,插入几条测试数据,表结构就两个字段,id和name,没有索引

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST;

1  2  test
2  2  test
3  3  test
4  4  test
5  1  test

可以看到,默认差的时候是是按照rownum排序的。
然后按照name排序,

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"

1  2  test
2  2  test
5  1  test
4  4  test
3  3  test

可以看到,排列的顺序不是按照rownum来排序了。
这里再插入一个知识,如何在oracle里查看执行计划,我敲了半天的explain 发现没有用。。。

原来是这么看的,而且消息要比mysql详细多了。: 

select * from table(dbms_xplan.display());

-------------------------------------------------------------------------------
| Id | Operation      | Name  | Rows | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |     |   1 |   8 |  16  (7)| 00:00:01 |
|  1 | SORT ORDER BY   |     |   1 |   8 |  16  (7)| 00:00:01 |
|  2 |  COUNT       |     |    |    |      |     |
|  3 |  TABLE ACCESS FULL| ZZ_TEST |   1 |   8 |  15  (0)| 00:00:01 |
-------------------------------------------------------------------------------
好了,那么排序和索引有没有关系呢?
我们先在type上面加一个索引试试,这里我清空了重新插入了5个数据
SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"

1  3  test
2  4  test
5  2  test
4  1  test
3  5  test

貌似不给力啊老湿。
好,删掉type的索引,在id上加索引,清空表再插入5个数据

SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"

1  3  test
2  4  test
5  2  test
4  1  test
3  5  test

好吧。原来带上索引都不给力啊。。。

但是不对啊。。。总感觉不对劲啊。没错。。。我TMD一直再用的rownum而不是rowID啊。我一定是最近写分页写多了,坑爹啊。

这里简单的分辨一下rownum和rowid的区别,rownum是返回结果集的一个伪数列,用来标记返回结果的顺序,而rowid是一个物理值用来标记存储位置的。这个值是唯一而固定的

rowid和rownum都是虚列,但含义完全不同。rowid是物理地址,用于定位oracle中具体数据的物理存储位置,而rownum则是sql的输出结果排序。通俗的讲:rowid是相对不变的,rownum会变化,尤其是使用order by的时候。
那么我们再查下rowid试试,这时候表没有索引

SELECT rowid as rono,ROWNUM,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name"

AAA7JjAB9AAAD+RAAA 1  3  test
AAA7JjAB9AAAD+RAAB 2  4  test
AAA7JjAB9AAAD+RAAG 5  2  test
AAA7JjAB9AAAD+RAAD 4  1  test
AAA7JjAB9AAAD+RAAC 3  5  test

感觉rowno和rowid一个样子啊

清空表,再在name上建立一个索引,然后在插入5条数据

AAA7JjAB9AAAD+RAAA 1  3  test
AAA7JjAB9AAAD+RAAB 2  4  test
AAA7JjAB9AAAD+RAAG 5  2  test
AAA7JjAB9AAAD+RAAD 4  1  test
AAA7JjAB9AAAD+RAAC 3  5  test

所以,也不是rowid的问题,oralce的排序就是不稳定的。
这里有个小技巧,因为rownum的输出顺序并不是排序的结果 那么如何能输出排序顺序的rownum呢?可以使用嵌套查询,这个和分页写法是一个道理的

select ROWNUM ,t.* from (SELECT rowid rono,ZZ_TEST.* FROM ZZ_TEST ORDER BY ZZ_TEST."name") t

这里再插入一个小知识,如何在oracle下看表的

select  *  from  user_tables

   可以查询出所有的用户表

select  table_name  from  user_tables;

查询结果按照in条件顺序输出序输出

业务需要,通过lucene查出符合搜索条件的id,然后在详情表里查出这些id的详情

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64);

其中id是根据搜索的权值进行的排序,sql没有问题,但是通过这种sql查出来的结果的排序就不对了。

61 测试问题101 测试问题101
62 测试问题102 测试问题102
63 测试问题103 测试问题103
64 测试问题104 测试问题104
65 测试问题106 测试问题106 

这个一般默认是按照主键来排序的,而并不是根据in中条件的顺序来排列的


网上有个案例是按照in顺序来排序的解决方案,是利用sql server的charindex来解决的。不过仅限于sqlserver

select id,title from info  
where id in ('3,1,2,5,4')  
order by charindex(','+convert(varchar,ID)+',',',3,1,2,5,4,') 

CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下:

CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1是要到expression2中寻找的字符中,start_location是CHARINDEX函数开始在expression2中找expression1的位置。 CHARINDEX函数返回一个整数,返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串,那么函数整数“0”
这里有小技巧,可以利用charindex来进行模糊匹配

select name,pass from dps_user where
charindex('张三',dps_user.name)> 0

但是oracle下是怎么实现相同的效果的呢?可以使用decode函数

SELECT id,QUESTION,QUESTIONCOMMENT FROM "ASKDBA_QUESTION" where ID IN (63,62,65,61,64) ORDER BY "DECODE"(id, 63,1,62,2,65,3,61,64);

63 测试问题103 测试问题103
62 测试问题102 测试问题102
65 测试问题106 测试问题106
61 测试问题101 测试问题101
64 测试问题104 测试问题104

结果是符合条件的

 类似资料:
  • 问题内容: 我正在尝试做一个标准查询,该查询返回像常见问题一样的stackoverflow中回答最多的问题。 一个问题包含多个答案。 我试图以标准查询返回按每个问题的答案数排序的最常回答的问题。 有人知道我应该在hibernate标准util中使用什么吗? 问题答案: 这将返回一个Object []列表。每个Object []都将问题的ID作为第一个元素,并将此问题的答案数目作为第二个元素。问题按

  • 问题内容: 假设我有一个查询“ select * from子句,其中id在(0,2,5,1,3)中”,我实际上希望返回的行以它们在where子句中指定的相同顺序返回。ID的顺序将随查询的不同而改变,并且该顺序没有模式。 我知道可以更改数据模型,创建临时表等。但是请相信我,这些类型的解决方案在我的情况下将行不通。我也无法在应用程序代码中更改结果对象的顺序。 我还知道,不同的数据库引擎对事物的排序方式

  • 问题内容: 当我使用IN从表中选择一组行时,例如 有一种SQL技巧可以按照IN集合中给定的顺序恢复它们吗? 因此,在示例中,假设x具有ID为23、55、44和12的行,则将按该顺序返回这四行。 问题答案:

  • 我想使用instr函数按照in子句中值的顺序对查询结果进行排序。 映射器 XML 当ids集合为[1,2,3]时,mybatis生成的sql为: 在如下所示更改用于测试的xml之后,不会发生异常。

  • 问题内容: 我想知道是否有按IN()子句中的值顺序进行排序的方法(可能是一种更好的方法)。 问题是我有2个查询,一个查询获取所有ID,第二个查询获取所有信息。第一个创建我要第二个排序的ID的顺序。这些ID以正确的顺序放入IN()子句中。 因此,它类似于(极其简化): 问题在于第二个查询不会以将ID放入IN()子句中的顺序来返回结果。 我发现的一种解决方案是将所有ID放入具有自动递增字段的临时表中,

  • 问题内容: 我有这样的自定义订购需求: 我曾经想过用3个不同的选择查询的帮助下结合和。但是,我不能这样做,因为必须 在 和 之前 使用。 如何进行选择(或多个选择)以实现上面的自定义排序? 另一个解决方法可能会有所帮助,就是使此选择查询中返回的 第一条记录 成为最后一条记录,但是如何? 问题答案: 试试这个: 1亿个常量必须大于N。 这是一个简单的演示