最近,我正在尝试创建一个表。我有一个包含'a', 'b', 'c'的列,并且想将它与1,2,3 in交叉连接到下面的表中。
但是,我没有一个包含值1,2,3的表,并且需要在不创建表的情况下完成。
我可以在不创建任何表的情况下实现这一点吗?非常感谢!
Col1
a
b
c
Col1 Col2
a 1
b 1
c 1
a 2
b 2
c 2
a 3
b 3
c 3
用CTE代替:
SQL> with
2 a (col) as
3 (select 'a' from dual union all
4 select 'b' from dual union all
5 select 'c' from dual
6 ),
7 b (col) as
8 (select 1 from dual union all
9 select 2 from dual union all
10 select 3 from dual
11 )
12 select a.col, b.col
13 from a cross join b;
C COL
- ----------
a 1
a 2
a 3
b 1
b 2
b 3
c 1
c 2
c 3
9 rows selected.
SQL>
您可以使用:
SELECT *
FROM table1
CROSS JOIN (SELECT LEVEL AS col2 FROM DUAL CONNECT BY LEVEL <= 3);
或
WITH data (col1, col2) AS (
SELECT col1, 1 FROM table1
UNION ALL
SELECT col1, col2 + 1 FROM data WHERE col2 < 3
)
SELECT * FROM data;
给定您的样本数据:
CREATE TABLE table1 (col1) AS
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL;
两输出:
<表类>tbody><<tr> COL1 COL2 1 bc 1 1 b 2 c 2 2 b 3 c 3 表类> 3 您可以创建"表格";在你的问题中有许多不同的方式,其中一些已经在其他答案中说明了。
对于您的请求,我喜欢如下所示的XML解决方案:
create table table1 (col1 varchar2(1)); insert into table1(col1) values('a'); insert into table1(col1) values('b'); insert into table1(col1) values('c'); commit; select t1.col1, xmlcast(column_value as number) as col2 from table1 t1 cross join xmltable('1 to 3') ; COL1 COL2 ---- ---- a 1 a 2 a 3 b 1 b 2 b 3 c 1 c 2 c 3