//数据 A B C -- -- -- a1 b1 c1 a2 b2 c2 a3 b3 c3 //结果: 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 ) -- //解析: //通过执行子查询,从结果中我们看到了,对于每一行都返回了一个rn(每行行号); //对于每一个行号,执行上面的select union查询,每一次union,都返回一个结果集; //连接指定的1,2,3,和每一个rn对应的a,b,c值,就得到了我们想要的结果。 //例如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
oracle 根据行列号取值
最新推荐文章于 2024-01-18 16:37:40 发布