一家公司招聘考试题，oracle存储过程问题请教

doni

1. 
   
2. break on Record_ID
   
3. 
   
4. select 'Record '||vtab.num Record_ID, vtab.fld, vtab val from
   
5. (
   
6. (select rownum num,'a' fld, a val from tab)
   
7. union
   
8. (select rownum num,'b' fld, b val from tab)
   
9. union
   
10. (select rownum num,'c' fld, c val from tab)
    
11. union
    
12. (select rownum num,'d' fld, d val from tab)
    
13. union
    
14. (select rownum num,'e' fld, e val from tab)
    
15. ) vtab
    
16. order by num, val

复制代码

blessgod

使用 case when 可以处理,但是计算字段数值大小很麻烦啊(而且有局限)
本人很菜~~大家不要笑我的写法

eg:
select * from sort_t;

      COL1       COL2       COL3
---------- ---------- ----------
         1          3          2
         4          2          1
         4          7          1

select case when col1<col2 and col1<col3 then col1 When col2<col1 And col2<col3 Then col2 When col3<col1 And col3<col1 Then col3 End new_col1,
case when (col1<col2 and col1>col3) Or (col1>col2 and col1<col3) then col1 when (col2<col1 and col2>col3) Or ((col2>col1 and col2<col3) ) then col2 When (col3<col1 And Col3>col2) Or (col3>col1 And Col3<col2) Then col3 End new_col2,
case when col1>col2 and col1>col3 then col1 When col2>col1 And col2>col3 Then col2 When col3>col1 And col3>col1 Then col3 End new_col3
from sort_t;

  NEW_COL1   NEW_COL2   NEW_COL3
---------- ---------- ----------
         1          2          3
         1          2          4
         1          4          7

rollingpig

我会create一个function ,

1. 
   
2. Create or replace function pos(a number , b number , c number ,
   
3. d number , e number , pos number)
   
4. return number
   
5. as
   
6. ret number ;
   
7. begin
   
8. select v into ret from
   
9. (select v , rownum as r from
   
10. (select a as v from dual
    
11. union all
    
12. select b from dual
    
13. union all
    
14. select c from dual
    
15. union all
    
16. select d from dual
    
17. union all
    
18. select e from dual
    
19. order by 1
    
20. )
    
21. )where r = pos ;
    
22. return ret;
    
23. end;
    
24. /
    

复制代码