Scenario analysis: When the project is developed, there is a case where it is statistically statistically for the monthly design of the designated organ unit, requires data on all units of the subordinate. However, the level of the unit is multi-level, such as: provincial-municipal-district-district county-street.
And my table TBL1 structural design is
Organ Code (JG) Business Data Column 1 (a) Business Data Column 2 (b) ...
The design table TBL2 is designed as follows:
Organ Code (JG) superior agency (SJ) ...
If you pass a current organ code, check out the current agency and all sub-authorities data through a SQL statement. The database is Oracle, and the way I started is that the program first obtains the collection of the current agency and all subordinates, then spelling SQL with the IN clause. This can solve the problem, but I always feel awkward. Later, it was found that Oracle's PL / SQL can find this multi-level data.
As follows (assuming the current tax authority variable is 1234):
Sleect Sum (A), SUM (B) from TBL1 WHERE TBL1.JG in (SELECT JG from TBL2 WHERE TBL2.SJ = '1234' OR (TBL2.jg in (SELECT TBL2.SJ from TBL2 START with TBL2.SJ = ' 1234 'Connect by tbl2.sj = prior tbl2.jg)))))))
The problem is solved by a complex SQL statement to avoid two access databases.
