⭐️⭐️⭐️⭐️⭐️完整数据详见 练习数据·免费⭐️⭐️⭐️⭐️⭐️六十九、查找会员ID 27的向上推荐链问题查找会员ID 27的向上推荐链即推荐该会员的人以及推荐那个人的人依此类推。返回会员ID、名字和姓氏。按会员ID降序排列。预期结果recommenderfirstnamesurname20MatthewGenting5GeraldButters1DarrenSmith[答案与解析]withrecursive recommenders(recommender)as(selectrecommendedbyfromcd.memberswherememid27unionallselectmems.recommendedbyfromrecommenders recsinnerjoincd.members memsonmems.memidrecs.recommender)selectrecs.recommender,mems.firstname,mems.surnamefromrecommenders recsinnerjoincd.members memsonrecs.recommendermems.memidorderbymemiddescWITH RECURSIVE 是一个非常实用的功能但很多开发者并不了解它。它允许你对层次化数据进行查询这在SQL中用其他方法很难实现。这类场景往往让开发者不得不多次往返数据库系统。你之前见过 WITH。WITH 定义的公共表表达式CTE让你能够基于数据生成内联视图。这通常只是语法上的便利但 RECURSIVE 修饰符增加了将已生成的结果再次关联以产生更多结果的能力。递归 WITH 的基本形式如下WITHRECURSIVE NAME(columns)as(初始语句UNIONALL递归语句)初始语句填充初始数据然后递归语句反复执行以生成更多数据。递归的每一步都可以访问 CTE但它只能看到上一次迭代产生的数据。这个过程会重复直到某次迭代不再产生新数据为止。最简单的递归 WITH 示例可能像这样withrecursive increment(num)as(select1unionallselectincrement.num1fromincrementwhereincrement.num5)select*fromincrement;初始语句产生 ‘1’。递归语句的第一次迭代将 increment 的内容视为 ‘1’并产生 ‘2’。下一次迭代将 increment 的内容视为 ‘2’依此类推。当递归语句不再产生新数据时执行终止。了解了基本概念后解释我们的答案就比较容易了。初始语句获取推荐了我们关注的会员的那个人的ID。递归语句接收初始语句的结果并找到推荐他们的人的ID。这个值会被传递到下一次迭代依此类推。现在我们已经构建了 recommenders CTE主 SELECT 语句只需要从 recommenders 中获取会员ID并与 members 表关联以找出他们的姓名即可。七十、查找会员ID 1的向下推荐链问题查找会员ID 1的向下推荐链即该会员推荐的成员、这些成员再推荐的成员依此类推。返回会员ID和姓名按会员ID升序排列。预期结果memidfirstnamesurname4JaniceJoplette5GeraldButters7NancyDare10CharlesOwen11DavidJones14JackSmith20MatthewGenting21AnnaMackenzie26DouglasJones27HenriettaRumney[答案与解析]withrecursive recommendeds(memid)as(selectmemidfromcd.memberswhererecommendedby1unionallselectmems.memidfromrecommendeds recsinnerjoincd.members memsonmems.recommendedbyrecs.memid)selectrecs.memid,mems.firstname,mems.surnamefromrecommendeds recsinnerjoincd.members memsonrecs.memidmems.memidorderbymemid这道题和上一题相比变化不大。本质区别在于我们现在是往相反方向遍历。值得注意的是与上一个例子不同这个CTE每次迭代会产生多行数据因为我们是沿着推荐树向下遍历追踪所有分支而不是向上。七十一、生成一个可以为任意会员返回向上推荐链的CTE问题生成一个可以为任意会员返回向上推荐链的CTE。你应该能够通过select recommender from recommenders where memberx来查询。通过获取会员12和22的推荐链来演示。结果表应包含member和recommender按member升序、recommender降序排列。预期结果memberrecommenderfirstnamesurname129PonderStibbons126BurtonTracy2216TimothyBaker2213JemimaFarrell[答案与解析]withrecursive recommenders(recommender,member)as(selectrecommendedby,memidfromcd.membersunionallselectmems.recommendedby,recs.memberfromrecommenders recsinnerjoincd.members memsonmems.memidrecs.recommender)selectrecs.member member,recs.recommender,mems.firstname,mems.surnamefromrecommenders recsinnerjoincd.members memsonrecs.recommendermems.memidwhererecs.member22orrecs.member12orderbyrecs.memberasc,recs.recommenderdesc这道题要求我们生成一个CTE能够计算任意用户的向上推荐链。解题的关键在于意识到我们需要让CTE产生两列一列包含我们要查询的会员另一列包含其推荐树中的会员。本质上我们是在生成一个将推荐层次结构扁平化的表。由于我们要为每个用户生成推荐链初始语句需要为每个用户选择数据他们的ID以及推荐他们的人。随后我们在每次迭代中传递member字段而不改变它同时获取下一个推荐人。可以看到语句的递归部分并没有太大变化只是多传递了’member’字段。
