17,086
社区成员
发帖
与我相关
我的任务
分享
--按照你的意思,应该是要得到笛卡尔积,但是计算得到的结果,与你提供的数据不一致:
SQL> --this is a Cartesian-product
SQL> with ta as(
2 select 'A1' unm,'G1' g_id from dual union all
3 select 'B1','G1' from dual)
4 ,tb as(
5 select 'A2' unm,'G2' g_id from dual union all
6 select 'B4','G2' from dual)
7 ,tc as(
8 select 'A4' unm,'G3' g_id from dual union all
9 select 'B5','G3' from dual union all
10 select 'C1','G3' from dual)
11 ,td as(
12 select rownum rn,a.unm nm1,b.unm nm2,c.unm nm3
13 from ta a,tb b,tc c)
14 select rn,nm
15 from (
16 select rn,nm1 nm
17 from td
18 union all
19 select rn,nm2
20 from td
21 union all
22 select rn,nm3
23 from td)
24 group by rn,nm
25 /
RN NM
---------- --
1 A1
1 A2
1 A4
2 A1
2 A2
2 B5
3 A1
3 A2
3 C1
4 A1
4 A4
4 B4
5 A1
5 B4
5 B5
6 A1
6 B4
6 C1
7 A2
7 A4
7 B1
8 A2
8 B1
8 B5
9 A2
9 B1
9 C1
10 A4
10 B1
10 B4
11 B1
11 B4
11 B5
12 B1
12 B4
12 C1
36 rows selected