There are two list
A table
The create table a
(aid varchar2 (40),
Name varchar2 (100),
Parentid varchar2 (40)
);
Table b
The create table b
(aid varchar2 (40),
Operation varchar2 (100)
);
A table data below
AID the name parentid
420000000000 in hubei province 000000000000
Huangshi city, hubei province 420200000000 420000000000
420202000000 huangshi Yellowstone harbor 420200000000
420203000000 cisse huangshi city mountain 420200000000

B table data below
AID operation
420000000000, 12312321321,

Hope to get the results
AID operation
420000000000, 12312321321,
420200000000, 12312321321,
420202000000, 12312321321,
420203000000, 12312321321,

Great god action, in order to reduce the pressure in table b store only the parent ID, but want to get results all subsets of the parent ID, like a statement with

CodePudding user response:

- the building Lord baidu this string,
Oracle connect by the prior id=id

CodePudding user response:

To describe, lest any misunderstanding
There are two list
A table
The create table a
(aid varchar2 (40),
Name varchar2 (100),
Parentid varchar2 (40)
);
Table b
The create table b
(aid varchar2 (40),
Operation varchar2 (100)
);
A table data below
AID the name parentid
420000000000 in hubei province 000000000000
Huangshi city, hubei province 420200000000 420000000000
420202000000 huangshi Yellowstone harbor 420200000000
420203000000 cisse huangshi city mountain 420200000000

B table data below
AID operation
420000000000, 12312321321,
420200000000, 36565112121,

Hope to get the results
AID operation
420000000000, 12312321321,
420200000000, 12312321321,
420202000000, 12312321321,
420203000000, 12312321321,
420200000000, 36565112121,
420202000000, 36565112121,
420203000000, 36565112121,

Great god action, in order to reduce the pressure in table b store only the parent ID, but want to get results all subsets of the parent ID, like a statement

CodePudding user response:

refer to the second floor wmxcn2000 response:

- the building Lord baidu this string,
Oracle connect by the prior id=id

I know this, but this is not only used in one table

CodePudding user response:

Select a t1. Aid, b.o peration
The from (select a. *, level, connect_by_root (a.a id) as rootid
From a
Start with Amy polumbo arentid='000000000000'
Connect by Amy polumbo arentid=the prior a.a id) t1,
B
Where a t1. Rootid=b.a id;

Take a look at this should be able to meet your requirements

CodePudding user response:

reference 5 floor liuzhijian2008x reply:

select t1. The aid, b.o peration
The from (select a. *, level, connect_by_root (a.a id) as rootid
From a
Start with Amy polumbo arentid='000000000000'
Connect by Amy polumbo arentid=the prior a.a id) t1,
B
Where a t1. Rootid=b.a id;

Look at this should be able to meet your requirements

can describe, look at my second B AID may save the table surface is actually a list below any ID, that is a father of table ID is B table inside put ID, not only that a data

CodePudding user response:

reference 5 floor liuzhijian2008x reply:

select t1. The aid, b.o peration
The from (select a. *, level, connect_by_root (a.a id) as rootid
From a
Start with Amy polumbo arentid='000000000000'
Connect by Amy polumbo arentid=the prior a.a id) t1,
B
Where a t1. Rootid=b.a id;

Look at this should be able to meet your requirements

I resolved, on the basis of the change, thank you for inspiration

CodePudding user response:

The select t.a id, t.o peration from (
Select a. *, level, connect_by_root (a.a id) as rootid, b.o peration
From a, b
Start with Amy polumbo arentid=b.a id
Connect by Amy polumbo arentid=the prior a.a id) t;

CodePudding user response:

Well, the corresponding change can meet your needs, the tree structure with connect by just by the way,