PostgreSql树形查询备查

为什么80%的码农都做不了架构师？>>>   

初始表tree

id pid desc --描述 scope --分数 is_leaf

--查询完成率

create or replace view tree_2_rate as select * , ( with recursive cte as ( select * from tree a where pid= root.id union all
select k.* from tree k inner join cte c on c.id = k.pid ) select round( count(scope)::numeric/count(1)::numeric, 2) from cte where is_leaf = 1 ) from tree root where is_leaf = 0

--查询所有结点及其子结点数量

select * , ( with recursive cte as ( select * from tree a where pid= root.id union all
select k.* from tree k , cte c where c.id = k.pid ) select count(1) from cte ) from tree root

--批量更新叶子字段

update tree set is_leaf = ( with recursive cte as ( select * from tree a where pid= tree.id union all
select k.* from tree k , cte c where c.id = k.pid ) select case when count(1) > 0 then 0 else 1 end from cte )

--能力评价系统的完成率

select * , ( with recursive cte as ( select * from user_8_rate a where pid= root.indicatorid and a.id = 'f4d5fbb34561443294104e3ae9818767' union all
select k.* from user_8_rate k , cte c where c.indicatorid = k.pid and k.id = 'f4d5fbb34561443294104e3ae9818767' ) select round( count(rate_1_optionid)::numeric/(case when count(1) = 0 then 1 else count(1) end )::numeric, 2) from cte where isleaf = 'true' -- ) from user_8_rate root where root.isleaf = 'false' and id = 'f4d5fbb34561443294104e3ae9818767'

--select * from user_8_rate a where indicatorid= '65c835cd676247699724ca06d5e62bb6'

select * from user_8_rate root where id = 'f4d5fbb34561443294104e3ae9818767' and isleaf = 'false'

select count(*) from user_8_indicator where indicatorforyear = '2017'

select * from indicator

select count(*) from lp_sys_user

--指标左连接评价视图

drop view user_8_rate create or replace view user_8_rate as select a.*, b.rate_1_optionid from user_8_indicator a left join rate b on a.indicatorid = b.indicatorid and a.id = b.passivepepleid

select count(*) from user_8_indicator a left join rate b on a.indicatorid = b.indicatorid and a.id = b.passivepepleid where indicatorforyear = '2017'

--用户、指标全连接视图

drop view user_8_indicator create or replace view user_8_indicator as select b.id, b.username, a.* from indicator a, lp_sys_user b

转载于:https://my.oschina.net/sqhua/blog/887858