Recursive queries in SQL statements

Recursive queries in SQL statements SqlServer2005 and Oracle two versions

Before using Oracle, think of its recursive query is very useful, the study of SQL Server, found that it also supports recursive query in SQL To illustrate: The SqlServer2005 version of SQL is as follows: For example, a table with ID and PID fields, ID is the primary key, PID represents its ancestor node, table structure and data: CREATE TABLE [AAA] ( [ID] [int] NULL, [PID] [INT] Null [Name] [NCHAR] (10) ) GO INSERT into AAA VALUES (1,0, ' a ') INSERT into AAA VALUES (2,0, ' B ') INSERT into AAA VALUES (3,1, ' C ') INSERT into AAA VALUES (4,1, ' d ') INSERT into AAA VALUES (5,2, ' e ') INSERT into AAA VALUES (6,3, ' F ') INSERT into AAA VALUES (7,3, ' G ') INSERT into AAA VALUES (8,4, ' H ') GO --The following SQL is a query of all the child nodes of the 1 node. With My1 as (SELECT * from AAA WHERE id = 1 UNION ALL select Aaa.* from My1, aaa where my1.id = Aaa.pid ) SELECT * FROM my1-the result contains 1 of this record, if not included, can be added at the end: where ID <> 1 --The following SQL is a query of all the parent nodes of the 8 node. With My1 as (SELECT * from AAA where id = 8 UNION ALL select Aaa.* from My1, aaa where my1.pid = aaa.id ) SELECT * from My1; --The following is a recursive deletion of 1 nodes and all sub-nodes of the statement: With My1 as (SELECT * from AAA WHERE id = 1 UNION ALL select Aaa.* from My1, aaa where my1.id = Aaa.pid ) Delete from the AAA where exists (select ID from my1 where my1.id = aaa.id) The Oracle version of SQL is as follows: For example, a table, ID and PID fields, ID is the primary key, PID indicates its ancestor node, table structure and data please refer to SqlServer2005, SQL is as follows: --The following SQL is a query of all the child nodes of the 1 node. SELECT * from AAA START with id = 1 CONNECT by PID = PRIOR ID --The following SQL is a query of all the parent nodes of the 8 node. SELECT * from AAA START with id = 8 CONNECT by PRIOR pid = ID today to help others do a little bit of meaning of SQL, but also with the implementation of recursion, specifically as follows: Suppose you have a sales table like this: CREATE TABLE [TB] ( [QJ] [INT] NULL,--month, this test assumes starting from January, and the data are consecutive months, the middle no partition [JE] [INT] NULL--The actual amount of sales this month [RWE] [INT] NULL,--Sales task volume this month [FLD] [Float] NULL-the rebate point for this month is greater than the amount of the task and the rebate amount is JE*FLD ) on [PRIMARY] It is now required to calculate the rebate amount for each month, as follows: January Sales amount is greater than the rebate amount for the task amount = amount * Rebate Point February Sales amount is greater than the rebate amount for the Mission = (amount-January rebate) * Rebate Point March Sales amount is greater than the rebate amount for the Mission amount = (Amount-1, February rebate) * Rebate Point After the next month and so on, the rebate is 0 when sales are less than the task amount The specific SQL is as follows: With My1 as ( SELECT *, Case When JE > RWE Then (JE * fld) ELSE 0 END Fle, CAST (0 as FLOAT) tmp From TB WHERE QJ = 1 UNION All SELECT tb.*, Case When Tb.je > Tb.rwe then (tb.je-my1.fle-my1.tmp) * TB.FLD ELSE 0 END Fle, My1.fle + my1.tmp tmp--used to accumulate rebates for the previous month From My1, Tb WHERE TB.QJ = my1.qj + 1 ) SELECT * From My1

SQLserver2008 using an expression recursive query

--recursively subordinate by parent
With CTE (Id,parentid,text)
As
(--Parent item
Select Id,parentid,text from treeview where ParentID = 450
UNION ALL
--Subordinates in recursive result sets
Select T.id,t.parentid,t.text from TreeView as T
INNER join CTE as C on t.parentid = C.id
)
Select Id,parentid,text from CTE

---------------------

--Child recursive parent
With CTE (Id,parentid,text)
As
(--Subordinate parent
Select Id,parentid,text from treeview where id = 450
UNION ALL
--Parent in recursive result set
Select T.id,t.parentid,t.text from TreeView as T
INNER join CTE as C on t.id = C.parentid
)
Select Id,parentid,text from CTE

Recursive queries in SQL statements