查询某一机构下的所有子节点的机构名称,入党申请,积极分子,发展对象,预备党员人数。
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语句的后面,作为分组的依据;要么就要被包含在聚合函数中