PostgreSql树形查询备查

概述初始表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

初始表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

总结

以上是内存溢出为你收集整理的PostgreSql树形查询备查全部内容，希望文章能够帮你解决PostgreSql树形查询备查所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。