实现此效果说起来比较难以说明，我这里还是先将实现的效果已图的形式展示一下吧。
这是两个表的设计。我想实现的效果举个例子，以查询secretaryCharge为例：
点击“市级”，我将查询到市级一下所有“区级”的secretaryCharge数目，并通过organizations中的fullName来作为名字，查到的统计数目作为值。得到的效果便是如下图所示：
具体实现sql语句就是用到join on，sql语句具体如下：
?
insert @result(fullName,paramer)
(
select fullName,villageCharge
from
(
select fullName,orgID from organizations where parent=@orgId
) as orgInfo
join
(
select DJCharge.parentOrg as parent ,sum(villageCharge) as villageCharge
from 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 DJChargeInfo
on orgInfo.orgID=DJChargeInfo.parent
)
这里我省略了定义、声明等过程，此段代码是begin-end块中的。用了groupby，不用的话则会统计总数，而不能将子查询分开统计。
?
如果需要统计一个表中的多个字段统计数，join-on还可以继续使用，代码例子如下所示：
?
insert @result (regionFullName,countmale,countfemale)
(
select fullName,countmale,countfemale
from
(
select fullName,orgID from organizations where parent=@orgID
) as orgInfo
full join
(
select count(*) as countmale,SUBSTRING(parentOrg,2,6) AS parent from partyMembers where sex='男'
and substring(parentOrg ,2,6)in
( select substring(orgID,2,6) AS validOrgID From organizations where parent=@orgID)
group by substring(parentOrg,2,6)
)as Male
on substring(orgInfo.orgID,2,6)=Male.parent
full join
(
select count(*) as countfemale,substring(parentOrg,2,6)AS parent from partyMembers where sex='女'
and substring(parentOrg ,2,6)in
( select substring(orgID,2,6) AS validOrgID From organizations where parent=@orgID)
group by substring(parentOrg,2,6)
)as Female
on substring(orgInfo.orgID,2,6)=Female.parent
)
这里我用了full join全连接，目的是在显示时只要组成的表中有一个字段有值，这一行都得到。查询效果如下：
?
如果不使用全连接可能会产生多余两行结果。