SQL implementation gets the field composition table data from two tables and then inserts into the function table

To achieve this effect is more difficult to explain, I am still here to achieve the effect of the figure is shown in the form of it.

This is a design of two tables. I would like to achieve the effect of an example to query Secretarycharge for example:

Click "City", I will query to the city level of all "district-level" of the number of secretarycharge, and through the organizations in the FullName as the name, the number of statistics as a value. The results are as follows:

The specific implementation of the SQL statement is to use the Join On,sql statement as follows:

Insert @result (Fullname,paramer) (select Fullname,villagecharge from (select Fullname,orgid from organizations where [ Email protected]) as Orginfojoin (select djcharge.parentorg as parent, sum (villagecharge) as Villagechargefrom Djcharge, organizations where [email protected] and Djcharge.orgid=organizations.orgid and djcharge.parentorg in (select DISTINCT OrgID from organizations where [e-mail protected]) group by djcharge.parentorg) as Djchargeinfoon orginfo.orgid= Djchargeinfo.parent   )

Here I omit the definition, declaration, and so on, this code is in the Begin-end block. Use the GroupBy, not the case will be counted the total, and can not separate sub-query statistics.

If you need to count multiple field statistics in a table, join-on can continue to use the code example as follows:

Insert @result (Regionfullname,countmale,countfemale) (        select Fullname,countmale,countfemale        from        ( Select Fullname,orgid from organizations where [email protected]) as Orginfofull join (SELECT COUNT (*) as Countmale,substri NG (parentorg,2,6) as parent from Partymembers where sex= ' man ' and substring (parentorg, 2,6) in (select substring (orgid,2,6) A S Validorgid from organizations where [e-mail protected])  GROUP by substring (parentorg,2,6)) as Maleon substring ( orginfo.orgid,2,6) =male.parent full Join (SELECT COUNT (*) as countfemale,substring (parentorg,2,6) as the parent from Partymembers where sex= ' female ' and substring (parentorg, 2,6) in (select SUBSTRING (orgid,2,6) as Validorgid from organizations W Here [e-mail protected])  GROUP by substring (parentorg,2,6)) as Femaleon substring (orginfo.orgid,2,6) =female.parent )

Here I use full join to connect, the purpose is to display the table as long as there is a value in a field, this row is obtained. The query results are as follows:

If you do not use a full connection, you may incur two extra rows of results.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL implementation gets the field composition table data from two tables and then inserts into the function table