兄弟姐妹們救命啊試了無數方法感覺都不行，大家幫忙想想啊：
有一個表里面有兩個欄位：
編號 替代組
10 B1
11 B1
20 D1
21 D1
30 G1
31 G1
32 G1
同個替代組的可以互相替代，最終是想找出這個里面的所有可能的組合，列出下面的表，
版本 編號 替代組
V1 10 B1
V1 20 D1
V1 30 G1
V2 10 B1
V2 21 D1
V2 30 G1
V3 11 B1
V3 20 D1
V3 30 G1
V4 11 B1
V4 21 D1
V4 30 G1
V5 10 B1
V5 20 D1
V5 31 G1
V6 10 B1
V6 20 D1
V6 32 G1
類似的總共12個版本的組合
uj5u.com熱心網友回復：
with tab1 as (
select 1 id, 'b' gp from dual union all
select 2 id, 'b' from dual union all
select 3 id, 'c' from dual union all
select 4 id, 'c' from dual union all
select 5 id, 'd' from dual union all
select 6 id, 'd' from dual union all
select 7 id, 'd' from dual
)
, tab2 as (
select t1.*,
dense_rank() over(order by t1.gp) dr
from tab1 t1
)
, tab3 as (
select t1.*,
level lv,
sys_connect_by_path(t1.id || t1.gp, ' ') path
from tab2 t1
start with t1.dr = 1
connect by prior t1.dr + 1 = t1.dr
)
select path from tab3 t1 where t1.lv = 3
;
uj5u.com熱心網友回復：
WITH T AS (SELECT 10 BH,'B1' TDZ FROM DUALUNION SELECT 11,'B1' FROM DUAL
UNION SELECT 20,'D1' FROM DUAL
UNION SELECT 21,'D1' FROM DUAL
UNION SELECT 30,'G1' FROM DUAL
UNION SELECT 31,'G1' FROM DUAL
UNION SELECT 32,'G1' FROM DUAL),
A AS (SELECT BH FROM T WHERE T.TDZ = 'B1'),
B AS (SELECT BH FROM T WHERE T.TDZ = 'D1'),
C AS (SELECT BH FROM T WHERE T.TDZ = 'G1')
SELECT A.BH,'B1',B.BH,'D1',C.BH,'G1'
FROM A
INNER JOIN B ON A.BH <> B.BH
INNER JOIN C ON A.BH <> C.BH;
自連接就可以
uj5u.com熱心網友回復：
這個可能我沒有說的很清楚，這邊資料我只是舉例的，實際我是動態的，所以你們的寫法好像那啥uj5u.com熱心網友回復：
使用C++的、string類，通過回圈的方式輸入5個字串，再通過回圈的方式將5個字串按逆轉后的順序顯示出來。例如，逆轉前的5個字串是：Germany Japan America Britain France 按逆轉后的順序輸出字串是 France Britain America Japan Germany幫幫我uj5u.com熱心網友回復：
樓主這個需求，不適合單一陳述句實作，建議使用存盤程序，或者在高級語言中實作。uj5u.com熱心網友回復：
難道我寫的不是動態的？uj5u.com熱心網友回復：
非要寫全了才能看懂?with tab1 as (
select 1 id, 'b' gp from dual union all
select 2 id, 'b' from dual union all
select 3 id, 'c' from dual union all
select 4 id, 'c' from dual union all
select 5 id, 'd' from dual union all
select 6 id, 'd' from dual union all
select 7 id, 'd' from dual
)
, tab2 as (
select t1.*,
dense_rank() over(order by t1.gp) dr
from tab1 t1
)
, tab3 as (
select t1.*,
level lv,
sys_connect_by_path(t1.id || t1.gp, ' ') path
from tab2 t1
start with t1.dr = 1
connect by prior t1.dr + 1 = t1.dr
)
, tab4 as (
select path, rownum rn from tab3 t1 where t1.lv = (select count(distinct v1.gp) from tab1 v1)
)
select 'v' || t1.rn,
regexp_substr(t1.path, '[^ ]+', 1, level) sss
from tab4 t1
connect by prior t1.path = t1.path
and level <= regexp_count(t1.path, ' ')
and prior sys_guid() is not null
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16368.html
標籤：開發
上一篇：oracle中的declare