Home > database >  Alternative combination algorithm, help for help
Alternative combination algorithm, help for help

Time:10-11

Brothers and sisters can't help feeling tried countless method, yeah, everybody help think:
There is a table there are two fields:
Alternative group number
10 B1
11 B1
20 D1
21 D1
30 the G1
31 G1
32 G1

With an alternative set of can replace each other, is ultimately want to find out the inside of all possible combinations, list the following table,
Version number of the replacement group
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

11 V4 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
A total of 12 version of the combination of similar

CodePudding user response:

 with tab1 as (
Select 1 id, 'b' gp from dual union all
Select id, 2 '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 id, 'd' from dual
)
Tab2 as (
Select a t1. *,
Dense_rank () over (order by t1. Gp) Dr
The from tab1 t1
)
, tab3 as (
Select a t1. *,
Level lv,
Sys_connect_by_path (t1. Id | | t1. Gp, ' ') path
The from tab2 t1
Start with t1. Dr=1
Connect by the prior t1. Dr + 1=t1. Dr
)
Select the path from tab3 t1 where t1. The lv=3

;

CodePudding user response:

WITH T AS (SELECT 10 BH, 'B1 TDZ FROM DUAL
UNION 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),
AS A (SELECT BH FROM T WHERE T.T DZ='B1'),
B the AS (SELECT BH FROM T WHERE T.T DZ=' 'D1),
C AS (SELECT BH FROM T WHERE T.T DZ='G1')
SELECT a. H, 'B1, B.B H and D1, mount H,' G1 '
FROM A
INNER JOIN ON a. B H & lt;> B.B H
INNER JOIN ON a. C H & lt;> Mount H;
Since the connection is ok

CodePudding user response:

This may be I don't have made it very clear, this data I just, for example, I actually is dynamic, so you seem that what

CodePudding user response:

Using c + + string class, 5 string input by means of circulation, then through the way of circulation will be displayed after five string in reverse order, for example, reverse the five before the string is: Germany Japan America Britain France after according to reverse the order of the output string is France Britain America Japan Germany to help me

CodePudding user response:

The building Lord this requirement for a single statement, it is recommended to use the stored procedure, or in a high-level language,

CodePudding user response:

Don't I write dynamic?

CodePudding user response:

Must you all could read it?
 with tab1 as (
Select 1 id, 'b' gp from dual union all
Select id, 2 '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 id, 'd' from dual
)
Tab2 as (
Select a t1. *,
Dense_rank () over (order by t1. Gp) Dr
The from tab1 t1
)
, tab3 as (
Select a t1. *,
Level lv,
Sys_connect_by_path (t1. Id | | t1. Gp, ' ') path
The from tab2 t1
Start with t1. Dr=1
Connect by the prior t1. Dr + 1=t1. Dr
)
, tab4, as (
Select path, rownum rn from tab3 t1 where t1. The lv=(select count (distinct v1. Gp) from tab1 v1)
)
Select the 'v' | | t1. Rn,
Regexp_substr (t1) path, '[^] +' 1, level) SSS
The from tab4, t1
Connect by the prior t1) path=t1) path
And level & lt;=regexp_count (t1) path, ' ')
And the prior sys_guid () is not null
;
  • Related