Yuming Wang created SPARK-24497: ----------------------------------- Summary: Support recursive SQL query Key: SPARK-24497 URL: https://issues.apache.org/jira/browse/SPARK-24497 Project: Spark Issue Type: New Feature Components: SQL Affects Versions: 2.4.0 Reporter: Yuming Wang
h3. *Examples* Here is an example for {{WITH RECURSIVE}} clause usage. Table "department" represents the structure of an organization as an adjacency list. {code:sql} CREATE TABLE department ( id INTEGER PRIMARY KEY, -- department ID parent_department INTEGER REFERENCES department, -- upper department ID name TEXT -- department name ); INSERT INTO department (id, parent_department, "name") VALUES (0, NULL, 'ROOT'), (1, 0, 'A'), (2, 1, 'B'), (3, 2, 'C'), (4, 2, 'D'), (5, 0, 'E'), (6, 4, 'F'), (7, 5, 'G'); -- department structure represented here is as follows: -- -- ROOT-+->A-+->B-+->C -- | | -- | +->D-+->F -- +->E-+->G {code} To extract all departments under A, you can use the following recursive query: {code:sql} WITH RECURSIVE subdepartment AS ( -- non-recursive term SELECT * FROM department WHERE name = 'A' UNION ALL -- recursive term SELECT d.* FROM department AS d JOIN subdepartment AS sd ON (d.parent_department = sd.id) ) SELECT * FROM subdepartment ORDER BY name; {code} More details: [http://wiki.postgresql.org/wiki/CTEReadme] [https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1141-160K/lqe1472241402390.html] -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org