Similar to this, the project owner, contractor, then contractor subcontract to another contractor, this time the contractor has become the meaning of project owner, there may be multiple subcontract, can now demand is to list the total package a subcontracting contract name amount b f g h and so on



Then for example also to C and D B



How to list who is a total package of the contractor or subcontractor for who, who finally doing this project, each link is how many amount?

CodePudding user response:

The above figure in addition to sorting didn't tell the difference, the original table, need what results?

CodePudding user response:

[ reference quote=1/f weixin_45069503 response:]
The above figure in addition to sorting didn't tell the difference, the original table, need what results?

The original table is excel table sample, I want to import the oracle database,

Need to form is fine, as long as they can display it would have been nice to have that kind of tree

CodePudding user response:

Like this?
Select the project owner, contractor, lpad (contractor, length (contractor) + level 1, '-') from sc_module
Start with the project owner=A
Construction of the connect by the prior=operator
The order siblings by contractor

CodePudding user response:

Tip the device identifier is invalid, and then highlight quality

CodePudding user response:

Wrote lpad 3 buildings, is the lowercase L, not the I

CodePudding user response:

reference weixin_45069503 reply: 3/f

like this?
Select the project owner, contractor, lpad (contractor, length (contractor) + level 1, '-') from sc_module
Start with the project owner=A
Construction of the connect by the prior=operator
Construction of the order siblings by

A great god, and I joined the contract amount is error

reference ZJHZ_ leaf reply: 3/f

like this?
Select the project owner, contractor, lpad (contractor, length (contractor) + level 1, '-') from sc_module
Start with the project owner=A
Construction of the connect by the prior=operator
Construction of the order siblings by

The great god, I written according to your operation is successful!!!!!!

But there is a new problem, now I need to sort out the two columns of data, one is the contractor id id is a project owner, to meet the follow-up frame data structure, encounter a problem,

How to give their hierarchical level, I illustrate: such as a enterprise is the construction of the project owner that I would write id for 1 project owner id is empty a construction enterprise is

Such as a subcontracting to b that b enterprise project owner, the contractor is 11 with id 1 to achieve the effect of this hierarchy, now the problem is that if b enterprise subcontract is the same project at the same time three e f g how to through the function or SQL implementation to e f g assignment respectively?
The final result is
Construction contractor id id project owner name
1 a company
11 1 b enterprise
111 11 e enterprise
112 11 f enterprise
113 11 g

CodePudding user response:

Don't make the contractor, the construction of the subcontracting of what specific business concepts, in this problem, you have built table statements, test data, the results are coming, with less than a minute can get everything right, but I now I don't know what you're saying, also too lazy to go to guess,

CodePudding user response:

SELECT the JSF, SGF, rnum, 1 | | sys_connect_by_path (rnum, '/')
The FROM (select JSF, SGF, row_number () over (partition by JSF order by SGF) rnum
The from table_test)
START WITH the JSF='A'
Connect by the prior SGF=JSF
The order siblings by SGF
Like this? If you don't/replace