求select写法 oracle 11g(PLSQL)有如下表：create table temp_order (col1 int,col2 varchar(10))insert in
求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
[解决办法]
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;
[解决办法]
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'
);
COMMIT;
SELECT t1.col1,
temp_order.col2
FROM temp_order
RIGHT JOIN
(SELECT rownum AS col1 FROM dual CONNECT BY rownum < 5
) t1
ON temp_order.col1 = t1.col1
ORDER BY t1.col1;