In-depth SQL oracle recursive query

☆Retrieve all table names and column names of the database
Select name from sysobjects where xtype = 'U'
Select name from syscolumns where id = (select max (id) from sysobjects where xtype = 'U' and name = 'table name ')

☆Recursive Data Query
Recursive query of SqlServer2005 and Oracle in SQL statements
I used Oracle before and thought that its recursive query is very useful. I studied SqlServer and found that it also supports recursive query in SQL.
Example:
The SQL statement of SqlServer2005 is as follows:
For example, a table has the id and pId fields. id is the primary key. pid indicates its parent 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 statement is used to query all the subnodes of Node 1.
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. If you do not want to include this record, you can add: where id <> 1 at the end.
-- The following SQL statement queries all the parent nodes of the eight nodes.
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 statements recursively delete 1 node and all child nodes:
With my1 as (select * from aaa where id = 1
Union all select aaa. * from my1, aaa where my1.id = aaa. pid
)
Delete from aaa where exists (select id from my1 where my1.id = aaa. id)
The Oracle version of SQL is as follows:
For example, a table has the id and pId fields. id is the primary key. pid indicates its parent node. For table structure and data, see SqlServer2005. The SQL is as follows:
-- The following SQL statement is used to query all the subnodes of Node 1.
SELECT * FROM aaa
Start with id = 1
Connect by pid = PRIOR id
-- The following SQL statement queries all the parent nodes of the eight nodes.
SELECT * FROM aaa
Start with id = 8
Connect by prior pid = id
Today, I made an interesting SQL statement for others, which is also implemented using recursion, as follows:
Assume that a sales table is as follows:
Create table [tb] (
[Qj] [int] NULL, -- month. This test assumes that the data starts from January 1, January and the data is continuous months, with no partitions in the middle.
[Je] [int] NULL, -- actual sales amount this month
[Rwe] [int] NULL, -- Sales Task amount for this month
[Distinct] [float] NULL -- the rebate of the current month when the amount is greater than the task amount, and the rebate is je * percent
) ON [PRIMARY]
You must calculate the rebate amount for each month. The rules are as follows:
The sales amount in February is greater than the task amount. The rebate amount = The amount * the rebate.
The sales amount in February is greater than the task amount and the rebate amount = (amount-the rebate amount in February) * rebate
The sales amount in February is greater than the task amount's rebate amount = (amount-1, 2 months rebate amount) * rebate
The following month and so on. When the sales volume is less than the task amount, the rebate is 0.
The specific SQL statement is as follows:
Copy codeThe Code is as follows:
WITH my1 (
SELECT *,
CASE
WHEN je> rwe THEN (je * Quit)
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. Large
ELSE 0
END fle,
My1.fle + my1.tmp tmp -- used to accumulate the rebates of the previous month
FROM my1,
Tb
WHERE tb. qj = my1.qj + 1
)
SELECT *
FROM my1

SQLserver2008 recursive query using expressions
-- Recursive lower-level by parent item
With cte (id, parentid, text)
As
(-- Parent item
Select id, parentids, text from treeview where parentid = 450
Union all
-- Lower level in recursive result set
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
---------------------
-- Recursive parent item by child
With cte (id, parentid, text)
As
(-- Parent item
Select id, parentid, text from treeview where id = 450
Union all
-- Parent item in the 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