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

这篇文章主要介绍了Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出的方法,其中ORDER BY的排序结果需要注意其是否稳定,需要的朋友可以参考下

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，没有索引

1. SELECT ROWNUM,ZZ_TEST.* FROM ZZ_TEST; 

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

可以看到，默认差的时候是是按照rownum排序的。

然后按照name排序，

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

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

可以看到，排列的顺序不是按照rownum来排序了。

这里再插入一个知识，如何在oracle里查看执行计划，我敲了半天的explain 发现没有用。。。

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

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

1. ------------------------------------------------------------------------------- 
2. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
3. ------------------------------------------------------------------------------- 
4. | 0 | SELECT STATEMENT | | 1 | 8 | 16 (7)| 00:00:01 | 
5. | 1 | SORT ORDER BY | | 1 | 8 | 16 (7)| 00:00:01 | 
6. | 2 | COUNT | | | | | | 
7. | 3 | TABLE ACCESS FULL| ZZ_TEST | 1 | 8 | 15 (0)| 00:00:01 | 
8. ------------------------------------------------------------------------------- 

好了，那么排序和索引有没有关系呢?

我们先在type上面加一个索引试试，这里我清空了重新插入了5个数据

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

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

貌似不给力啊老湿。

好，删掉type的索引，在id上加索引，清空表再插入5个数据

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

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

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

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

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

rowid和rownum都是虚列，但含义完全不同。rowid是物理地址，用于定位oracle中具体数据的物理存储位置，而rownum则是sql的输出结果排序。通俗的讲：rowid是相对不变的，rownum会变化，尤其是使用order by的时候。

那么我们再查下rowid试试，这时候表没有索引

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

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

感觉rowno和rowid一个样子啊

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

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

所以，也不是rowid的问题，oralce的排序就是不稳定的。

这里有个小技巧，因为rownum的输出顺序并不是排序的结果 那么如何能输出排序顺序的rownum呢?可以使用嵌套查询，这个和分页写法是一个道理的

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

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

1. select * from user_tables 

可以查询出所有的用户表

1. select table_name from user_tables; 

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

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

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

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

1. 61 测试问题101 测试问题101 
2. 62 测试问题102 测试问题102 
3. 63 测试问题103 测试问题103 
4. 64 测试问题104 测试问题104 
5. 65 测试问题106 测试问题106  

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

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

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

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

1. CHARINDEX ( expression1 , expression2 [ , start_location ] ) 

Expression1是要到expression2中寻找的字符中，start_location是CHARINDEX函数开始在expression2中找expression1的位置。 CHARINDEX函数返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串，那么函数整数“0”

这里有小技巧，可以利用charindex来进行模糊匹配

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

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

1. 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); 

结果是符合条件的