之前看到一個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     ●
 ============