求select写法 oracle 11g(PLSQL)解决思路
求select写法 oracle 11g(PLSQL)
有如下表:
create table temp_order (col1 int,col2 varchar(10))
insert into temp_order values ('1','A')
insert into temp_order values ('3','B')
insert into temp_order values ('4','C')
如何得到
1 A
2
3 B
4 C
------解决方案--------------------
------解决方案--------------------
可以 这样来
select rownum col1 , col2 from temp_order where rownum=temp_order.col1
------解决方案--------------------
WITH TEMP_ORDER(col1, col2) AS (
SELECT 1,'A' FROM DUAL
UNION ALL
SELECT 3,'B' FROM DUAL
UNION ALL
SELECT 4,'C' FROM DUAL
)
SELECT t2.rid, nvl(t1.col2,' ') FROM TEMP_ORDER T1 RIGHT JOIN (SELECT ROWNUM rid FROM DUAL CONNECT BY ROWNUM<5) t2 on t1.col1 = t2.rid order by t2.rid asc;
有如下表:
create table temp_order (col1 int,col2 varchar(10))
insert into temp_order values ('1','A')
insert into temp_order values ('3','B')
insert into temp_order values ('4','C')
如何得到
1 A
2
3 B
4 C
------解决方案--------------------
16:25:44 SCOTT@myora > create table temp_order (col1 int,col2 varchar(10));
表已创建。
16:26:00 SCOTT@myora > insert into temp_order values ('1','A');
已创建 1 行。
16:26:00 SCOTT@myora > insert into temp_order values ('3','B');
已创建 1 行。
16:26:00 SCOTT@myora > insert into temp_order values ('4','C');
已创建 1 行。
16:26:00 SCOTT@myora > commit;
提交完成。
16:26:00 SCOTT@myora > select col1, col2 from temp_order;
COL1 COL2
---------- --------------------
1 A
3 B
4 C
已选择3行。
16:26:00 SCOTT@myora >
16:26:00 SCOTT@myora > with a1 as (select level c1
16:26:00 2 from dual t1
16:26:00 3 connect by level<=(select max(col1) from temp_order))
16:26:00 4 select a1.c1 as col1, a2.col2
16:26:00 5 from a1 left join temp_order a2 on a1.c1=a2.col1
16:26:00 6 order by a1.c1;
COL1 COL2
---------- --------------------
1 A
2
3 B
4 C
已选择4行。
16:26:01 SCOTT@myora >
------解决方案--------------------
可以 这样来
select rownum col1 , col2 from temp_order where rownum=temp_order.col1
------解决方案--------------------
WITH TEMP_ORDER(col1, col2) AS (
SELECT 1,'A' FROM DUAL
UNION ALL
SELECT 3,'B' FROM DUAL
UNION ALL
SELECT 4,'C' FROM DUAL
)
SELECT t2.rid, nvl(t1.col2,' ') FROM TEMP_ORDER T1 RIGHT JOIN (SELECT ROWNUM rid FROM DUAL CONNECT BY ROWNUM<5) t2 on t1.col1 = t2.rid order by t2.rid asc;