There is an existing users table, as shown below: NAMEVALUEID -------------------------- A a1 B b2 C c3 Ding d4 now, they need to be combined in pairs, for example, AB and ba are the same, now we need to use the select statement to query all the combinations of abcd. The final result should be 6, AB, ac, ad, bc, bd, and cd.
There is an existing users Table, as follows: name value id ---------- a 1 B 2 c 3 d 4 now, they need to be combined in pairs, for example, AB and ba are the same. Now we need to use the select statement to query the combinations of all abcd. The final result should be 6, AB, ac, ad, bc, bd, cd.
An existing users table is as follows:
NAME VALUE ID
------------------------------
Jia a 1
B 2
C 3
Ding d 4
Now, we need to combine them in pairs. For example, AB and ba are the same. Now we need to use the select statement to query the combinations of all abcd. The final result should be 6, AB, ac, ad, bc, bd, cd. <无>
Create table users (name char (2), value char (1), id number); insert into users values ('A', 'A', 1 ); insert into users values ('B', 'B', 2); insert into users values ('C', 'C', 3 ); insert into users values ('dd', 'D', 4); commit;
Select a. value | B. value result from test_j a, test_j bwhere a. rowid <> B. rowid and a. value
Select replace (. combo, '#') as "Combination" from (select id, sys_connect_by_path (value, '#') | '#' combo from (select 1 as id, value, 1 as ctrl from users) connect by prior id = id and value> prior value) a, (select 1 as id, value, 1 as ctrl from users) B where B. id =. id and instr (. combo, '#' | B. value | '#')> 0 group by. id,. combo having sum (B. ctrl) = 2;
Select replace (. combo, '#') as "Combination" from (select id, sys_connect_by_path (value, '#') | '#' combo from (select 1 as id, value, 1 as ctrl from users) connect by prior id = id and value> prior value) a, (select 1 as id, value, 1 as ctrl from users) B where B. id =. id and instr (. combo, '#' | B. value | '#')> 0 group by. id,. combo having sum (B. ctrl) = 3;
Select replace (. combo, '#') as "Combination" from (select id, sys_connect_by_path (value, '#') | '#' combo from (select 1 as id, value, 1 as ctrl from users) connect by prior id = id and value> prior value) a, (select 1 as id, value, 1 as ctrl from users) B where B. id =. id and instr (. combo, '#' | B. value | '#')> 0 group by. id,. combo having sum (B. ctrl) = 4;