☆ Get all the table names of the database, all the column names of the table
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 query data
Recursive queries in SQL statements SqlServer2005 and Oracle two versions
Using Oracle before, and finding it useful for recursive queries, I studied SQL Server and found that it also supports recursive queries in SQL
An example is provided:
The SqlServer2005 version of SQL is as follows:
For example, a table with ID and PID fields, ID is primary key, PID represents 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 is a query for all the sub 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, and if you don't want to include it, you can add it at the end: where ID <> 1
- -The following SQL is a query for 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;
--Here are the statements that recursively deletes 1 nodes and all the 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 IDs from my1 where my1.id = aaa.id)
the Oracle version of SQL is as follows:
For example a table, has the ID and the PID field, the ID is the primary key, the PID represents its ancestor node, the table structure and the data please refer to the SqlServer2005, the SQL is as follows:
- -The following SQL is a query for all the sub nodes of the 1 node.
SELECT * from AAA
START with id = 1
CONNECT by PID = PRIOR ID
- -The following SQL is a query for 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 bit of the meaning of SQL, but also with recursive implementation, specifically as follows:
Suppose there is a sales table as follows:
CREATE TABLE [TB] (
[QJ] [INT] NULL,--month, this test assumes starting from January, and the data are consecutive months, there is no partition in the middle
[JE] [INT] NULL,-actual amount sold this month
[RWE] [INT] NULL--Sales task amount for this month
[FLD] [Float] NULL-the rebate point when the amount of this month is greater than the task amount, and the rebate is JE*FLD
) on [PRIMARY]
It is now required to calculate the rebate amount for each month, as follows:
January Sales amount greater than mission rebate = amount * Rebate Point
February Sales is greater than the amount of mission rebate = (amount-January rebate) * Rebate Point
March Sales amount greater than mission rebate = (Amount-1, February rebate) * Rebate Point
After the month, and so on, the sales are less than the task amount, the rebate is 0
the specific SQL is as follows:
Copy Code code 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 add the rebate of the preceding month
From My1,
Tb
WHERE TB.QJ = my1.qj + 1
)
SELECT *
From My1
SQLserver2008 using an expression recursive query
--recursive subordinate by parent
With CTE (Id,parentid,text)
As
(--The parent
Select Id,parentid,text from treeview where ParentID = 450
UNION ALL
--Subordinate 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
---------------------
--child-level 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
