Order SQL data ORDER as in IN clause

example:

select * from t_cu where cno IN (
220,
120,
900,
350,
99,
1,
34
);
 

I would like to display the data in the same order as 

220,
120,
900,
350,
99,
1,
34

 

 

 

=========================A

select * from t_cu where cno IN (
220,
120,
900,
350,
99,
1,
34
)
order by decode(cno,
220, 1,
120, 2,
900, 3,
350, 4,
99,  5,
1,   6,
34,  7,
8);

=========================B

SQL> ed
Wrote file afiedt.buf
 
  1  with req as (select '7844,7698,7782,7499' as en from dual)
  2      ,ord as (select REGEXP_SUBSTR (en, '[^,]+', 1, level) en, rownum rn
  3               from req
  4               connect by level <= length(regexp_replace(en,'[^,]*'))+1)
  5  --
  6  select emp.*
  7  from emp join ord on (emp.empno = ord.en)
  8* order by ord.rn
SQL> / 
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
 

=========================C

SQL>  with t as (select t.*, rownum r
             from table (sys.odcinumberlist (7788,
                                             7934,
                                             7782,
                                             7902,
                                             7369,
                                             7499,
                                             7566)) t)
  select empno, ename
    from emp e, t
   where e.empno = column_value
order by r
/ 
     EMPNO ENAME     
---------- ----------
      7788 SCOTT     
      7934 MILLER    
      7782 CLARK     
      7902 FORD      
      7369 SMITH     
      7499 ALLEN     
      7566 JONES     
 
7 rows selected.

=========================

评论

1 楼 kavinhub 2013-02-04  

ref:
http://blog.csdn.net/ssqtjffcu1/article/details/8568065

1、table函数。用于构建虚拟表，并且可以套用同个from子句下其他表的值。为构建虚拟行的基础。
2、cast类型转换函数。用于将数据转换成行。
3、multiset函数。用于将数据转换成结果集的形式。
4、sys.odcinumberlist类型。告诉cast将子查询以什么类型返回结果。

----------
select column_value A from table(sys.odcinumberlist(1,2,3,4,5,6,7,8,9)) ;     
sys.odcinumberlist 函数把字符 纵向列出

也可以使用:
select count(*) from xmltable('1 to 9');