SQL retrieves fields from two tables to form table data and then inserts it into the function table. SQL Fields

SQL retrieves fields from two tables to form table data and then inserts it into the function table. SQL Fields

It is difficult to describe how to achieve this effect. Here, I will first show the implemented effect in the form of a diagram.

This is the design of two tables. For example, to query secretaryCharge:

Click "city level". I will query the number of secretaryCharge in all "district level" cities, and use fullName in organizations as the name. The number of statistics found is used as the value. The result is as follows:

The specific implementation of SQL statements is to use join on. The SQL statements are as follows:

insert @result(fullName,paramer)(select fullName,villageCharge from (select fullName,orgID from organizations where parent=@orgId) as orgInfojoin(select DJCharge.parentOrg as parent ,sum(villageCharge) as villageChargefrom DJCharge,organizations where year=@year and DJCharge.orgID=organizations.orgID and DJCharge.parentOrg in(select distinct orgID from organizations where parent=@orgID)group by DJCharge.parentOrg) as DJChargeInfoon orgInfo.orgID=DJChargeInfo.parent   )

Here, I omit the definition, declaration, and other processes. The code segment is in the begin-end block. If groupby is used, the total number is counted instead of the subquery.

If you want to count the statistics of multiple fields in a table, join-on can continue to be used. The code example is as follows:

Insert @ result (regionFullName, countmale, countfemale) (select fullName, countmale, countfemale from (select fullName, orgID from organizations where parent = @ orgID) as orgInfofull join (select count (*) as countmale, SUBSTRING (parentOrg,) AS parent from partyMembers where sex = 'male' and substring (parentOrg,) in (select substring (orgID) AS validOrgID From organizations where parent = @ orgID) group by substring (parentOrg, 2, 6) as Maleon substring (orgInfo. orgID, 2, 6) = Male. parent full join (select count (*) as countfemale, substring (parentOrg,) AS parent from partyMembers where sex = 'female 'and substring (parentOrg) in (select substring (orgID, 2, 6) AS validOrgID From organizations where parent = @ orgID) group by substring (parentOrg, 2, 6) as Femaleon substring (orgInfo. orgID, 2, 6) = Female. parent)

Here I use full join for full join. The purpose is to obtain this row if there is a field with a value in the table that is displayed. The query results are as follows:

If full join is not used, two additional rows may be generated.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.