Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Select all countries that can be found by recursively adding all neighbouring countries (PostgreSQL)
-- SO question http://stackoverflow.com/questions/43136590/finding-all-entries-that-can-be-found-by-recursive-selection create table borders( Country1 varchar(100), Country2 varchar(100) ); insert into borders(Country1,Country2) values ('Sweden','Finland'), ('Norway','Sweden'), ('Norway','Finland'), ('Norway','Russia'), ('Russia','Ukraine'), ('Russia','Finland'), ('Russia','China'), ('Canada','United States') ; WITH RECURSIVE neighbours AS ( -- start with 'Sweden' if any neighbour of it exists SELECT DISTINCT 'Sweden' as Name, ARRAY[cast('Sweden' as varchar)] as path FROM borders b WHERE 'Sweden' IN (b.Country1, b.Country2) -- UNION ALL SELECT CASE WHEN nb.Name = b.Country1 THEN b.Country2 ELSE b.Country1 END, path || CASE WHEN nb.Name = b.Country1 THEN b.Country2 ELSE b.Country1 END FROM borders b JOIN neighbours nb ON nb.Name IN(b.Country1, b.Country2) AND NOT(b.Country1 = ANY(path) AND b.Country2 = ANY(path)) ) SELECT DISTINCT Name FROM neighbours;
run
|
edit
|
history
|
help
0
SQL social network practice by Han Wang 20200720
Testing psql
first
PostgreSQL aggregate JSON recordset keys by row
Demo
Stack
Limit & union
tegs and users
Yy
Assignment 1(SET A)