--按照你的意思,应该是要得到笛卡尔积,但是计算得到的结果,与你提供的数据不一致: 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 /
组合,上面GROUP_ID哪里去了,下面的ID怎么来的?
--按照你的意思,应该是要得到笛卡尔积,但是计算得到的结果,与你提供的数据不一致:
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
数据不一,是因为 你的第三行写错了. 3 select 'B1','G1' from dual)
B1 应该改为 B2. 我的大概意思和你提供的一致.
只是 当前你提供的是3个群组的做法.
如我之前提到的 " 群组的数量是不定的 " 就是 还有可能出现 G4, G5 ... 请问能够有解法吗 ? 谢谢!
(
U_ID VARCHAR2(18 BYTE),
G_ID VARCHAR2(18 BYTE)
)commit;insert into u01 values('A1','G1');
insert into u01 values('B2','G1');
insert into u01 values('A2','G2');
insert into u01 values('B4','G2');
insert into u01 values('A4','G3');
insert into u01 values('B5','G3');
insert into u01 values('C1','G3');
commit;注意, 数据只是以 3 个群组举例, 有可能还有其它群组 G4,G5...