7. Recursive.md

Intro

Common Table Expressions allow us to, effectively, create our own temporary tables for the duration of a query - they're largely a convenience to help us make more readable SQL. Using the WITH RECURSIVE modifier, however, it's possible for us to create recursive queries. This is enormously advantageous for working with tree and graph-structured data - imagine retrieving all of the relations of a graph node to a given depth, for example. This category shows you some basic recursive queries that are possible using our dataset.

Q1: find the upward recommendation chain for member ID 27

Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.

-- with recursive 递归查询
with recursive recommenders as (
  select memid as recommender, firstname, surname, recommendedby
  	from cd.members where memid = 27
  union
  	select m.memid, m.firstname, m.surname, m.recommendedby
  		from cd.members as m, recommenders -- 原始表格与递归的表格都要有
  		where m.memid = recommenders.recommendedby -- 指定递归条件
  )
 select recommender, firstname, surname
 	from recommenders
	where recommender != 27
order by recommender desc

Q2: find the downward recommendation chain for member ID 1

Find the downward recommendation chain for member ID 1: that is, the members they recommended, the members those members recommended, and so on. Return member ID and name, and order by ascending member id.

with recursive recommenders as (
  select memid, firstname, surname, recommendedby
  	from cd.members
  	where memid = 1
  union
  	select m.memid, m.firstname, m.surname, m.recommendedby
  		from cd.members as m, recommenders
  		where m.recommendedby = recommenders.memid -- 倒序的递归条件
  )
select memid, firstname, surname
	from recommenders
	where memid != 1
order by memid;

解析

正序递归与倒序递归的区别是递归条件的不同

Q3: produce a CTE that can return the upward recommendation chain for any member

Produce a CTE that can return the upward recommendation chain for any member. You should be able to select recommender from recommenders where member=x. Demonstrate it by getting the chains for members 12 and 22. Results table should have member and recommender, ordered by member ascending, recommender descending.

-- 这个答案是标准答案 , 但我感觉不好, 不清晰
with recursive recommenders(recommender, member) as (
	select recommendedby, memid
		from cd.members
	union all
	select mems.recommendedby, recs.member
		from recommenders recs
		inner join cd.members mems
			on mems.memid = recs.recommender
)
select recs.member member, recs.recommender, mems.firstname, mems.surname
	from recommenders recs
	inner join cd.members mems		
		on recs.recommender = mems.memid
	where recs.member = 22 or recs.member = 12
order by recs.member asc, recs.recommender desc