sss

with ete as (
SELECT * from (
SELECT * from (
SELECT "rank"()over(PARTITION by ci.id ORDER BY cf.year desc),ci.id company_id,cf.year,cf.snzcz from company_info ci
left join company_finance_r cfr on ci.id = cfr.company_id
left join company_finance cf on cf.id = cfr.finance_id
where cf.del_flag = 2
) one where one.rank = 1
) two left join (

SELECT ci.id, sum(bnglyhgczcz) filter(where cf.year > 2021) bnglyhgczcz

from company_info ci
left join company_finance_r cfr on ci.id = cfr.company_id
left join company_finance cf on cf.id = cfr.finance_id
where cf.del_flag = 2
GROUP BY ci.id
) three on two.company_id = three.id
)

SELECT ci."name" "企业名称",
string_agg(distinct case when ap.zzlx = 1 then '路基路面'
when ap.zzlx = 2 then '桥梁'
when ap.zzlx = 3 then '隧道'
when ap.zzlx = 4 then '交通安全设施' end || case when ap.zzdj = 1 then '甲级' when ap.zzdj = 2 then '乙级' else '' end, ',') "资质"
,ci.zczj "注册资金（万元）",ci.qyjzc "企业净资产（万元）",ete.snzcz "近三年公路养护工程总产值(2022,2023,2024)",ete.bnglyhgczcz "上年总产值",ete.year "统计年份"

,count(distinct p.id) filter(where p.person_type = 5 and p.del_flag = 2) "企业技术负责人"--人员类别（1企业法人、2资质技术负责人、3专业技术人员、4技术工人、5企业技术负责人
,count(distinct p.id) filter(where p.person_type = 3 and p.del_flag = 2) "专业技术人员"
,count(distinct p.id) filter(where p.person_type = 4 and p.del_flag = 2) "技术工人"
,count(distinct p.id) filter(where p.person_type = 2 and p.del_flag = 2) "资质技术负责人"
from company_info_snapshot cis
left join company_aptitude_r car on cis.id = car.snapshot_id
left join aptitude ap on ap.id = car.aptitude_id
left join company_info ci on ci.id = cis.company_id
left join company_person_r cpr on cpr.company_id = ci.id
left join personnel p on p.id = cpr.person_id
left join ete on ete.company_id = ci.id
where ap.del_flag = 2 and cis.del_flag = 2 and ap.shzt in(7,14,15)
GROUP BY ci.id,ete.bnglyhgczcz,ete.snzcz,ete.year