查询某一机构下的所有子节点的机构名称，入党申请，积极分子，发展对象，预备党员人数。
1.党员发展表（PARTY_DEVELOPMENT）
字段
APPLY_PARTY_USER_ID
OPERATOR_BRANCH_ID
APPLY_PARTY_TIME
ACTIVIST_REPORT_DATE
DEVELOPMENT_OBJ_DATE
GET_SET_PARTY_DATE
FORMAL_PARTY_DATE
名称
入党申请人id
申请人所在支部id
申请入党时间
确定积极分子的日期
确定发展对象日期
确定预备党员日期
确定转为正式党员日期
满足入党申请的条件：
APPLY_PARTY_TIME is not null and t.ACTIVIST_REPORT_DATE is null
满足积极分子条件：
t.ACTIVIST_REPORT_DATE is not null and t.DEVELOPMENT_OBJ_DATE is null
满足发展对象的条件：
t.DEVELOPMENT_OBJ_DATE is not null and t.GET_SET_PARTY_DATE is null
满足预备党员的条件：
t.GET_SET_PARTY_DATE is not null and t.FORMAL_PARTY_DATE is null
2.机构表（po_aa）
字段
PO_AA_ID
AA1
AA4
AA18
DATA_PATH
AA19
名称
机构id
机构名称
机构类型
排序
路径
状态
1. 基础sql：查询指定机构下的所有子节点
select
po.po_aa_id as poaaid ,
po.aa1 as aaname ,
po.AA4 as poaaorgtype,
po.AA18 as aa18 ,
po.DATA_PATH as dataPath ,
pa.po_aa_id ,
pa.aa1 ,
de.*
from
po_aa po,
po_aa pa
left join PARTY_DEVELOPMENT de
on
de.OPERATOR_BRANCH_ID = pa.po_aa_id
where
po.parent_id = '-1'
and pa.data_path like concat('%', po.po_aa_id, '%')
and po.aa19 = 1
and pa.aa19 = 1
2. 通过基础sql的查询结果统计各个人员数
select
t.poaaid as "organizationId" ,
t.aaname as "organizationName",
t.poaaorgtype as "organizationType",
t.aa18 ,
t.dataPath as "dataPath" ,
sum(case when t.APPLY_PARTY_TIME is not null and t.ACTIVIST_REPORT_DATE is null then 1 else 0 end) as "partycountA" ,
sum(case when t.ACTIVIST_REPORT_DATE is not null and t.DEVELOPMENT_OBJ_DATE is null then 1 else 0 end) as "partycountB" ,
sum(case when t.DEVELOPMENT_OBJ_DATE is not null and t.GET_SET_PARTY_DATE is null then 1 else 0 end) as "partycountC" ,
sum(case when t.GET_SET_PARTY_DATE is not null and t.FORMAL_PARTY_DATE is null then 1 else 0 end) as "partycountD" ,
sum(case when t.FORMAL_PARTY_DATE is not null then 1 else 0 end) as "partycountE"
from
(
select
po.po_aa_id as poaaid ,
po.aa1 as aaname ,
po.AA4 as poaaorgtype,
po.AA18 as aa18 ,
po.DATA_PATH as dataPath ,
pa.po_aa_id ,
pa.aa1 ,
de.*
from
po_aa po,
po_aa pa
left join PARTY_DEVELOPMENT de
on
de.OPERATOR_BRANCH_ID = pa.po_aa_id
where
po.parent_id = '-1'
and pa.data_path like concat('%', po.po_aa_id, '%')
and po.aa19 = 1
and pa.aa19 = 1
)
t
group by
t.poaaid ,
t.poaaorgtype,
t.aaname ,
t.aa18 ,
t.dataPath
order by
t.poaaorgtype,
to_number(t.aa18)
常见的聚合函数：
Ø 求个数：count
Ø 求总和：sum
Ø 求最大值：max
Ø 求最小值：min
Ø 求平均值：avg
group by的用法：
Group By从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
在select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中
