深入sql oracle遞歸查詢
更新時(shí)間:2013年05月30日 10:17:46 作者:
本篇文章是對(duì)sql oracle 遞歸查詢進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下
☆ 獲取數(shù)據(jù)庫(kù)所有表名,表的所有列名
select name from sysobjects where xtype='u'
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
☆ 遞歸查詢數(shù)據(jù)
Sql語(yǔ)句里的遞歸查詢 SqlServer2005和Oracle 兩個(gè)版本
以前使用Oracle,覺(jué)得它的遞歸查詢很好用,就研究了一下SqlServer,發(fā)現(xiàn)它也支持在Sql里遞歸查詢
舉例說(shuō)明:
SqlServer2005版本的Sql如下:
比如一個(gè)表,有id和pId字段,id是主鍵,pid表示它的上級(jí)節(jié)點(diǎn),表結(jié)構(gòu)和數(shù)據(jù):
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
--下面的Sql是查詢出1結(jié)點(diǎn)的所有子結(jié)點(diǎn)
with my1 as(select * from aaa where id = 1
union all select aaa.* from my1, aaa where my1.id = aaa.pid
)
select * from my1 --結(jié)果包含1這條記錄,如果不想包含,可以在最后加上:where id <> 1
--下面的Sql是查詢出8結(jié)點(diǎn)的所有父結(jié)點(diǎn)
with my1 as(select * from aaa where id = 8
union all select aaa.* from my1, aaa where my1.pid = aaa.id
)
select * from my1;
--下面是遞歸刪除1結(jié)點(diǎn)和所有子結(jié)點(diǎn)的語(yǔ)句:
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)
Oracle版本的Sql如下:
比如一個(gè)表,有id和pId字段,id是主鍵,pid表示它的上級(jí)節(jié)點(diǎn),表結(jié)構(gòu)和數(shù)據(jù)請(qǐng)參考SqlServer2005的,Sql如下:
--下面的Sql是查詢出1結(jié)點(diǎn)的所有子結(jié)點(diǎn)
SELECT * FROM aaa
START WITH id = 1
CONNECT BY pid = PRIOR id
--下面的Sql是查詢出8結(jié)點(diǎn)的所有父結(jié)點(diǎn)
SELECT * FROM aaa
START WITH id = 8
CONNECT BY PRIOR pid = id
今天幫別人做了一個(gè)有點(diǎn)意思的sql,也是用遞歸實(shí)現(xiàn),具體如下:
假設(shè)有個(gè)銷(xiāo)售表如下:
CREATE TABLE [tb](
[qj] [int] NULL, -- 月份,本測(cè)試假設(shè)從1月份開(kāi)始,并且數(shù)據(jù)都是連續(xù)的月份,中間沒(méi)有隔斷
[je] [int] NULL, -- 本月銷(xiāo)售實(shí)際金額
[rwe] [int] NULL, -- 本月銷(xiāo)售任務(wù)額
[fld] [float] NULL -- 本月金額大于任務(wù)額時(shí)的返利點(diǎn),返利額為je*fld
) ON [PRIMARY]
現(xiàn)在要求計(jì)算每個(gè)月的返利金額,規(guī)則如下:
1月份銷(xiāo)售金額大于任務(wù)額 返利額=金額*返利點(diǎn)
2月份銷(xiāo)售金額大于任務(wù)額 返利額=(金額-1月份返利額)*返利點(diǎn)
3月份銷(xiāo)售金額大于任務(wù)額 返利額=(金額-1,2月份返利額)*返利點(diǎn)
以后月份依次類(lèi)推,銷(xiāo)售額小于任務(wù)額時(shí),返利為0
具體的Sql如下:
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 -- 用于累加前面月份的返利
FROM my1,
tb
WHERE tb.qj = my1.qj + 1
)
SELECT *
FROM my1
SQLserver2008使用表達(dá)式遞歸查詢
--由父項(xiàng)遞歸下級(jí)
with cte(id,parentid,text)
as
(--父項(xiàng)
select id,parentid,text from treeview where parentid = 450
union all
--遞歸結(jié)果集中的下級(jí)
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
---------------------
--由子級(jí)遞歸父項(xiàng)
with cte(id,parentid,text)
as
(--下級(jí)父項(xiàng)
select id,parentid,text from treeview where id = 450
union all
--遞歸結(jié)果集中的父項(xiàng)
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
select name from sysobjects where xtype='u'
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
☆ 遞歸查詢數(shù)據(jù)
Sql語(yǔ)句里的遞歸查詢 SqlServer2005和Oracle 兩個(gè)版本
以前使用Oracle,覺(jué)得它的遞歸查詢很好用,就研究了一下SqlServer,發(fā)現(xiàn)它也支持在Sql里遞歸查詢
舉例說(shuō)明:
SqlServer2005版本的Sql如下:
比如一個(gè)表,有id和pId字段,id是主鍵,pid表示它的上級(jí)節(jié)點(diǎn),表結(jié)構(gòu)和數(shù)據(jù):
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
--下面的Sql是查詢出1結(jié)點(diǎn)的所有子結(jié)點(diǎn)
with my1 as(select * from aaa where id = 1
union all select aaa.* from my1, aaa where my1.id = aaa.pid
)
select * from my1 --結(jié)果包含1這條記錄,如果不想包含,可以在最后加上:where id <> 1
--下面的Sql是查詢出8結(jié)點(diǎn)的所有父結(jié)點(diǎn)
with my1 as(select * from aaa where id = 8
union all select aaa.* from my1, aaa where my1.pid = aaa.id
)
select * from my1;
--下面是遞歸刪除1結(jié)點(diǎn)和所有子結(jié)點(diǎn)的語(yǔ)句:
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)
Oracle版本的Sql如下:
比如一個(gè)表,有id和pId字段,id是主鍵,pid表示它的上級(jí)節(jié)點(diǎn),表結(jié)構(gòu)和數(shù)據(jù)請(qǐng)參考SqlServer2005的,Sql如下:
--下面的Sql是查詢出1結(jié)點(diǎn)的所有子結(jié)點(diǎn)
SELECT * FROM aaa
START WITH id = 1
CONNECT BY pid = PRIOR id
--下面的Sql是查詢出8結(jié)點(diǎn)的所有父結(jié)點(diǎn)
SELECT * FROM aaa
START WITH id = 8
CONNECT BY PRIOR pid = id
今天幫別人做了一個(gè)有點(diǎn)意思的sql,也是用遞歸實(shí)現(xiàn),具體如下:
假設(shè)有個(gè)銷(xiāo)售表如下:
CREATE TABLE [tb](
[qj] [int] NULL, -- 月份,本測(cè)試假設(shè)從1月份開(kāi)始,并且數(shù)據(jù)都是連續(xù)的月份,中間沒(méi)有隔斷
[je] [int] NULL, -- 本月銷(xiāo)售實(shí)際金額
[rwe] [int] NULL, -- 本月銷(xiāo)售任務(wù)額
[fld] [float] NULL -- 本月金額大于任務(wù)額時(shí)的返利點(diǎn),返利額為je*fld
) ON [PRIMARY]
現(xiàn)在要求計(jì)算每個(gè)月的返利金額,規(guī)則如下:
1月份銷(xiāo)售金額大于任務(wù)額 返利額=金額*返利點(diǎn)
2月份銷(xiāo)售金額大于任務(wù)額 返利額=(金額-1月份返利額)*返利點(diǎn)
3月份銷(xiāo)售金額大于任務(wù)額 返利額=(金額-1,2月份返利額)*返利點(diǎn)
以后月份依次類(lèi)推,銷(xiāo)售額小于任務(wù)額時(shí),返利為0
具體的Sql如下:
復(fù)制代碼 代碼如下:
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 -- 用于累加前面月份的返利
FROM my1,
tb
WHERE tb.qj = my1.qj + 1
)
SELECT *
FROM my1
SQLserver2008使用表達(dá)式遞歸查詢
--由父項(xiàng)遞歸下級(jí)
with cte(id,parentid,text)
as
(--父項(xiàng)
select id,parentid,text from treeview where parentid = 450
union all
--遞歸結(jié)果集中的下級(jí)
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
---------------------
--由子級(jí)遞歸父項(xiàng)
with cte(id,parentid,text)
as
(--下級(jí)父項(xiàng)
select id,parentid,text from treeview where id = 450
union all
--遞歸結(jié)果集中的父項(xiàng)
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
相關(guān)文章
Oracle針對(duì)數(shù)據(jù)庫(kù)某一行進(jìn)行操作的時(shí)候,如何將這一行加行鎖
Oracle針對(duì)數(shù)據(jù)庫(kù)某一行進(jìn)行操作的時(shí)候,如何將這一行加行鎖的實(shí)現(xiàn)方法2009-02-02
Oracle 給rac創(chuàng)建單實(shí)例dg并做主從切換功能
這篇文章主要介紹了Oracle 給rac創(chuàng)建單實(shí)例dg并做主從切換功能,通過(guò)實(shí)例代碼給大家介紹rac搭建過(guò)程,需要的朋友可以參考下2019-12-12
Windows10系統(tǒng)中Oracle完全卸載正確步驟
自己剛到公司就是熟悉數(shù)據(jù)庫(kù)的安裝卸載,所以分享一下學(xué)到的,下面這篇文章主要給大家介紹了關(guān)于Windows10系統(tǒng)中Oracle完全卸載正確步驟的相關(guān)資料,文章通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-04-04
oracle遠(yuǎn)程連接服務(wù)器數(shù)據(jù)庫(kù)圖文教程
這篇文章主要為大家詳細(xì)介紹了oracle遠(yuǎn)程連接服務(wù)器數(shù)據(jù)庫(kù)的圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-09-09
自動(dòng)備份Oracle數(shù)據(jù)庫(kù)
自動(dòng)備份Oracle數(shù)據(jù)庫(kù)...2007-03-03
Oracle數(shù)據(jù)庫(kù)中SQL語(yǔ)句的優(yōu)化技巧
這篇文章主要介紹了Oracle數(shù)據(jù)庫(kù)中SQL語(yǔ)句的優(yōu)化技巧的相關(guān)資料,需要的朋友可以參考下2016-07-07
詳解Oracle在out參數(shù)中訪問(wèn)光標(biāo)
這篇文章主要介紹了詳解Oracle在out參數(shù)中訪問(wèn)光標(biāo)的相關(guān)資料,這里提供實(shí)例代碼幫助大家學(xué)習(xí)理解這部分內(nèi)容,希望能幫助到大家,需要的朋友可以參考下2017-08-08

