LATERAL JOIN 案例分享

之前看到一个PPT上的案例 点我前往

他的Table设计不够完整,所以我就补了一下,来进行后续的操作.

create table people (  id serial PRIMARY KEY, name text not null);create table grp (  id serial PRIMARY KEY , name text not null);create table pepl_grp (  id serial PRIMARY KEY , pid integer references people (id), gid integer references grp (id));insert into people (name) values('Tom'),('Andy'),('Jim');insert into grp (name) values('群组1'),('群组2'),('群组3');insert into pepl_grp (pid, gid) values(1,1),(1,3),(2,2),(3,2),(3,3);// --------------------select title || stat as "弟弟你要的"  from (select rn, showp     , title , stat  from (select name, id             , row_number() over (order by id) rn          from people) p1  JOIN LATERAL(        select 2 as showp , '' as title, '' as stat         union all        select 2, '', ''          union all        select 1, p1.name , ''          union all        select 1, '======', '======'         union all        select 1, gname             , case pg.pid is null               when True then ''               else '     ●'              end as people_on_grp         from (select g.id gid                    , g.name gname                    , p.id pid                    , p.name pname                 from grp g                cross join people p                where p.id = p1.id) t1         left join pepl_grp pg           on (pg.pid = t1.pid and pg.gid = t1.gid)         union all        select 1, '======', '======'        ) t2     on true     ) t3 where rn >= showp;           弟弟你要的  -------------- Tom ============ 群组1     ● 群组2 群组3     ● ============   Andy ============ 群组1 群组2     ● 群组3 ============   Jim ============ 群组1 群组2     ● 群组3     ● ============