//數(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é)果中我們看到了,對(duì)于每一行都返回了一個(gè)rn(每行行號(hào));
//對(duì)于每一個(gè)行號(hào),執(zhí)行上面的select union查詢,每一次union,都返回一個(gè)結(jié)果集;
//連接指定的1,2,3,和每一個(gè)rn對(duì)應(yīng)的a,b,c值,就得到了我們想要的結(jié)果。
//例如rn=1時(shí):
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
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國五強(qiáng)!虛擬主機(jī)域名注冊頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|