下面是使用Postgresql 9.1的测试表/数据：
DROP table if exists family CASCADE;DROP table if exists plant CASCADE;CREATE table family ( ID serial,family_key VARCHAR(20) UNIQUE,female_plant_ID INTEGER NOT NulL DEFAulT 1,male_plant_ID INTEGER NOT NulL DEFAulT 1,filial_n INTEGER NOT NulL DEFAulT -1,-- eg 0,1,2... Which would represent None,F1,F2... CONSTRAINT family_pk PRIMARY KEY (ID));CREATE table plant ( ID serial,plant_key VARCHAR(20) UNIQUE,ID_family INTEGER NOT NulL,CONSTRAINT plant_pk PRIMARY KEY (ID),CONSTRAINT plant_ID_family_fk FOREIGN KEY(ID_family) REFERENCES family(ID) -- temp may need to remove constraint...);-- FAMILY table DATA:insert into family (ID,family_key,female_plant_ID,male_plant_ID,filial_n) VALUES (1,'NA',1); -- Default place holder record-- Root level Alba familIEsinsert into family (ID,filial_n) VALUES (2,'family1AA',2,3,1);insert into family (ID,filial_n) VALUES (3,'family2AA',4,5,filial_n) VALUES (4,'family3AA',6,7,1);-- F2 HybrID FamilIEsinsert into family (ID,filial_n) VALUES (5,'family4AE',8,11,0); insert into family (ID,filial_n) VALUES (6,'family5AG',9,12,0);insert into family (ID,filial_n) VALUES (7,'family6AT',10,13,0); -- F3 Double HybrID family:insert into family (ID,filial_n) VALUES (9,'family7AEAG',14,15,0);-- F3 Tri-hybrID backcross family:insert into family (ID,filial_n) VALUES (10,'family8AEAGAT',17,16,0);-- PLANT table DATA:-- Root level Alba Parents: insert into plant (ID,plant_key,ID_family) VALUES (1,1); -- Default place holder recordinsert into plant (ID,ID_family) VALUES (2,'female1A',1); insert into plant (ID,ID_family) VALUES (3,'male1A',1);insert into plant (ID,ID_family) VALUES (4,'female2A',ID_family) VALUES (5,'male2A',ID_family) VALUES (6,'female3A',ID_family) VALUES (7,'male3A',1);-- Female Alba progeny:insert into plant (ID,ID_family) VALUES (8,'female4A',2);insert into plant (ID,ID_family) VALUES (9,'female5A',3);insert into plant (ID,ID_family) VALUES (10,'female6A',4);-- Male Aspen Root level parents:insert into plant (ID,ID_family) VALUES (11,'male1E',ID_family) VALUES (12,'male1G',1); insert into plant (ID,ID_family) VALUES (13,'female1T',1);-- F1 HybrID progeny:insert into plant (ID,ID_family) VALUES (14,'female1AE',5); insert into plant (ID,ID_family) VALUES (15,'male1AG',6); insert into plant (ID,ID_family) VALUES (16,'male1AT',7);-- HybrID progenyinsert into plant (ID,ID_family) VALUES (17,'female1AEAG',9);-- Tri-hybrID backcross progeny:insert into plant (ID,ID_family) VALUES (18,'female1AEAGAT',10);insert into plant (ID,ID_family) VALUES (19,'female2AEAGAT',10);
下面是我从Postgres WITH Queries文档中派生的递归查询：
WITH RECURSIVE search_tree( family_key,female_plant,male_plant,depth,path,cycle) AS ( SELECT f.family_key,pf.plant_key,pm.plant_key,ARRAY[ROW(pf.plant_key,pm.plant_key)],false FROM family f,plant pf,plant pm WHERE f.female_plant_ID = pf.ID AND f.male_plant_ID = pm.ID AND f.filial_n = 1 -- Include only F1 familIEs (root level) AND f.ID <> 1 -- omit the default first family record UNION ALL SELECT f.family_key,st.depth + 1,path || ROW(pf.plant_key,pm.plant_key),ROW(pf.plant_key,pm.plant_key) = ANY(path) FROM family f,plant pm,search_tree st WHERE f.female_plant_ID = pf.ID AND f.male_plant_ID = pm.ID AND f.family_key = st.family_key AND pf.plant_key = st.female_plant AND pm.plant_key = st.male_plant AND f.filial_n <> 1 -- Include only non-F1 familIEs (non-root levels) AND NOT cycle)SELECT * FROM search_tree;
以下是所需的输出：
F1 family1AA=(female1A x male1A) > F2 family4AE=(female4A x male1E) > F3 family7AEAG=(female1AE x male1AG) > F4 family8AEAGAT=(female1AEAG x male1AT) F1 family2AA=(female2A x male2A) > F2 family5AG=(female5A x male1G) > F3 family7AEAG=(female1AE x male1AG) > F4 family8AEAGAT=(female1AEAG x male1AT) F1 family3AA=(female3A x male3A) > F2 family6AT=(female6A x female1T) > F3 family8AEAGAT=(female1AEAG x male1AT)
上面的递归查询显示具有相应F1父项的3行,但该路径不显示下游族/父项.我很感激帮助使递归输出类似于上面列出的所需输出.
我已经根据我的理解调整了查询,不一定是要求的:-)查询从f.ID！= 1 AND f.filial_n = 1定义的三个给定族开始,并递归扩展可用子项.
在什么条件下只选择最后三场比赛是我的理解.也许对于每个起始家庭来说,最长的一系列安慰者？
WITH RECURSIVE expanded_family AS ( SELECT f.ID,f.family_key,pf.ID pd_ID,pf.plant_key pf_key,pf.ID_family pf_family,pm.ID pm_ID,pm.plant_key pm_key,pm.ID_family pm_family,f.filial_n FROM family f JOIN plant pf ON f.female_plant_ID = pf.ID JOIN plant pm ON f.male_plant_ID = pm.ID),search_tree AS ( SELECT f.*,1 depth,ARRAY[f.family_key::text] path FROM expanded_family f WHERE f.ID != 1 AND f.filial_n = 1 UNION ALL SELECT f.*,depth + 1,path || f.family_key::text FROM search_tree st JOIN expanded_family f ON f.pf_family = st.ID OR f.pm_family = st.ID WHERE f.ID <> 1)SELECT family_key,pathFROM search_tree;
结果是：
family_key | depth | path ---------------+-------+------------------------------------------------- family1AA | 1 | {family1AA} family2AA | 1 | {family2AA} family3AA | 1 | {family3AA} family4AE | 2 | {family1AA,family4AE} family5AG | 2 | {family2AA,family5AG} family6AT | 2 | {family3AA,family6AT} family7AEAG | 3 | {family1AA,family4AE,family7AEAG} family7AEAG | 3 | {family2AA,family5AG,family7AEAG} family8AEAGAT | 3 | {family3AA,family6AT,family8AEAGAT} family8AEAGAT | 4 | {family1AA,family7AEAG,family8AEAGAT} family8AEAGAT | 4 | {family2AA,family8AEAGAT} 技术资料：
>我已经删除了循环内容,因为对于干净的数据,它不应该是必要的(恕我直言).
>如果出现一些奇怪的性能问题,可以内联expand_family,但是现在它使递归查询更具可读性.
编辑
稍微修改查询可以过滤这些行,对于每个“根”族(即查询开始的那些),存在最长路径.
我只在search_tree中显示已更改的部分,因此您必须复制上一部分的头部：
-- ...search_tree AS( SELECT f.*,f.ID family_root,-- remember where the row came from. 1 depth,st.family_root,-- propagate the anchestor depth + 1,pathFROM( SELECT rank() over (partition by family_root order by depth desc),family_root,path FROM search_tree) AS rankedWHERE rank = 1;
结果是：
family_key | path ---------------+------------------------------------------------- family8AEAGAT | {family1AA,family8AEAGAT} family8AEAGAT | {family2AA,family8AEAGAT} family8AEAGAT | {family3AA,family8AEAGAT}(3 rows) EDIT2
根据评论我添加了路径的pretty_print版本：
WITH RECURSIVE expanded_family AS ( SELECT f.ID,f.filial_n,f.family_key || '=(' || pf.plant_key || ' x ' || pm.plant_key || ')' pretty_print FROM family f JOIN plant pf ON f.female_plant_ID = pf.ID JOIN plant pm ON f.male_plant_ID = pm.ID),search_tree AS( SELECT f.ID,'F1 ' || f.pretty_print path FROM expanded_family f WHERE f.ID != 1 AND f.filial_n = 1 UNION ALL SELECT f.ID,st.path || ' -> F' || st.depth+1 || ' ' || f.pretty_print FROM search_tree st JOIN expanded_family f ON f.pf_family = st.ID OR f.pm_family = st.ID WHERE f.ID <> 1)SELECT pathFROM( SELECT rank() over (partition by family_root order by depth desc),path FROM search_tree) AS rankedWHERE rank = 1; 结果是
path ---------------------------------------------------------------------------------------------------------------------------------------------------------- F1 family1AA=(female1A x male1A) -> F2 family4AE=(female4A x male1E) -> F3 family7AEAG=(female1AE x male1AG) -> F4 family8AEAGAT=(female1AEAG x male1AT) F1 family2AA=(female2A x male2A) -> F2 family5AG=(female5A x male1G) -> F3 family7AEAG=(female1AE x male1AG) -> F4 family8AEAGAT=(female1AEAG x male1AT) F1 family3AA=(female3A x male3A) -> F2 family6AT=(female6A x female1T) -> F3 family8AEAGAT=(female1AEAG x male1AT)(3 rows)总结
以上是内存溢出为你收集整理的PostgreSQL通过2个父/子表递归递归全部内容，希望文章能够帮你解决PostgreSQL通过2个父/子表递归递归所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）