文章內(nèi)容

Oracle 根據(jù)行列號取值

發(fā)布時間: 2012/9/11 17:18:52

//數(shù)據(jù)
A   B C
-- -- --
a1 b1 c1
a2 b2 c2
a3 b3 c3
//結(jié)果:   -

row col value
--   --- -----
1     1 a1
1     2 b1
1     3 c1
2     1 a2
2     2 b2
2     3 c2
3     1 a3
3     2 b3
3     3 c3
--
with tb as(
     select 'a1' a,'b1' b,'c1' c from dual union all
     select 'a2','b2','c2' from dual union all
     select 'a3','b3','c3' from dual)
select rn,1,a from (select a,b,c,rownum as rn from tb )
union
select rn,2,b from (select a,b,c,rownum as rn from tb )
union
select rn,3,c from (select a,b,c,rownum as rn from tb )
--
//解析：
//通過執(zhí)行子查詢，從結(jié)果中我們看到了，對于每一行都返回了一個rn(每行行號)；
//對于每一個行號，執(zhí)行上面的select union查詢，每一次union，都返回一個結(jié)果集；
//連接指定的1,2,3,和每一個rn對應(yīng)的a，b，c值，就得到了我們想要的結(jié)果。
//例如rn=1時：
1     1 a1
1     2 b1
1     3 c1
--
SQL> with tb as(
2       select 'a1' a,'b1' b,'c1' c from dual union all
3       select 'a2','b2','c2' from dual union all
4       select 'a3','b3','c3' from dual)
5       select a,b,c,rownum as rn from tb
6 /
A B C          RN
-- -- -- ----------
a1 b1 c1          1
a2 b2 c2          2
a3 b3 c3          3