7. recursive

[mind1949]

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 

[mind1949]

最后一章练习题完成!Y(^o^)Y