Recursive queries in SQL statements

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.