I saw a similar problem, no answer not Oracle

Now there are two table, table in a

Aid manager
1, 1, 2, 3
2, 2, 4

In table b

The bid name
1 the li
2.
liu3 threes
4 li si

How to joint query out
The serial number manager manager name
1 1, 2, 3 li, liu, zhang SAN
2 2, 2, liu li si

Help!!!!!!!!!!

CodePudding user response:

With a as (select '1' aid, '1, 2, 3' manager from dual union all select '2', '2, 4' from dual),
B the as (select '1' bid, 'li' name from dual union all select '2', 'liu 2 from dual union all
Select '3', 'zhang' from dual union all select '4', 'bill' from dual),

Aa as (select aid, regexp_substr (competent leadership, '[^,]', 1, level) manager
From a
Connect by level<=regexp_count (competent leadership, ', ') + 1
And the prior aid=aid
And the prior sys_guid () is not null),
Ab as (select aa. Aid, aa. Competent leadership, b. name
The from aa, b
Where aa. Manager=b.b id)

Select aid, listagg (manager, ', ') within group (order by competent leadership) competent leadership,
Listagg (name, ', ') within group (order by competent leadership) name
The from ab
Group by aid

CodePudding user response:

Select aid, the competent leadership,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where instr (', '| | a. manager | |', ', ', '| | b.b id | |', ') & gt; 0) manager's name
From a

CodePudding user response:

To learn, you can also write so

CodePudding user response:

Performance is the best way is to write a function, according to the comma will id find them into one name spliced back to
Stitching order also won't have what problem

CodePudding user response:

reference 4 floor wildwave response:

performance is the best approach is to write a function, according to the comma will id find them into one name spliced back to
Stitching order also won't have what problem

Not good, write function no context switching cost?

CodePudding user response:

See your version of the data,
Version 11.2 after using the method of the best on the second floor

CodePudding user response:

Context switching costs relative to the large table above article (one thousand) for a full table scan, negligible
Can use a certain amount of data test

CodePudding user response:

Listagg, on the second floor of the way

CodePudding user response:

A little before 11 g:
With a (aid, aa)
As (
Select 1, '1, 2, 3 from dual
Union all select 2, '2', 4 'from dual
),
B (the bid, bb)
As (
Select 1, 'lee' from dual
Union all select 2, 'liu from dual
Union all select 3, 'zhang' from dual
Union all select 4, 'si' from dual
)
The select aid, aa, to_char (substr (WMSYS. WM_CONCAT (bb), 1100 0)) aa_name
From a, b
Where instr (', '| | a.a a | |', ', ', '| | b.b id | |', ') & gt; 0
Group by aid of aa

CodePudding user response:

Design method of this paradigm is shit, comma separated, too sharp, at the very least a paradigm are not, do so to ignore small data quantity, with the function of the large amount of data, each record is processing, so dead tens of millions of records

CodePudding user response:

Great

CodePudding user response:

With the function how to write? I seem to have encountered similar problems,

CodePudding user response:

2 l is available, effective measurement

CodePudding user response:

Study the morning was behind ', 'confused
As such is clearly a lot?
Select aid, the competent leadership,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where instr (a. competent leadership, b.b id) & gt; 0) manager's name
From a

Select aid, the competent leadership,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where a manager like '%' | | b.b id | | '%' & gt; 0) manager's name
from a

CodePudding user response:

refer to the second floor liuzhe_521 response:

select aid, manager,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where instr (', '| | a. manager | |', ', ', '| | b.b id | |', ') & gt; 0) manager's name
From a

qualified on the second floor

CodePudding user response:

On the 14th floor freedom97

reference response:

research the morning was the back of the ', 'confused
As such is clearly a lot?
Select aid, the competent leadership,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where instr (a. competent leadership, b.b id) & gt; 0) manager's name
From a

Select aid, the competent leadership,
(select listagg (b. name, ', ') within group (the order by the bid)
The from b
Where a manager like '%' | | b.b id | | '%' & gt; 0) manager's name
From a

So, the bid for more than two digits and single digits of mixed there will be a problem

CodePudding user response:

Please support!!!!!!!!!!!!!!!! I also met

CodePudding user response:

Very simple, execute the following statements can be directly,

WITH
AS A (
SELECT '1' AID '1, 2, 3,' manager FROM DUAL UNION ALL
SELECT '2' AID, '2', 4 'manager FROM DUAL),
B the AS (
SELECT '1' BID, 'li' name FROM DUAL UNION ALL
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull