CUBRID学习笔记 42 Hierarchical QuerySQL层级查询
cubrid的中sql查詢語法Hierarchical QuerySQL層級查詢
------ 官方文檔是英文的,看不明白可以參看ocracle的同類函數說明.很多都是一樣的.
ORACLE中CONNECT BY...START WITH
和ocracle的差不多 ,下面的說明就直接抄襲過來
http://www.iwwenbo.com/oracle-start-with-connect-by/
其中,[where 條件1] 可以不需要,[where 條件1]是對根據[start with 條件2 connect by 條件3]選擇出來的記錄進行過濾,是針對單條記錄的過濾,不會考慮樹形查詢的樹結構;
[start with 條件2 ]限定作為搜索起始點的條件,表示從滿足什么條件的記錄開始查詢;[connect by 條件3]表示查詢的連接條件,樹形菜單的查詢常常是因為記錄與記錄之間存在某種關系,這種關系通常就作為連接條件。
補充:connect by子句,通常跟關鍵字“PRIOR”一起使用,“PRIOR”關鍵字的位置通常有兩種,”CONNECT BY PRIOR ID = PID”和”CONNECT BY ID = PRIOR PID”,關鍵字位置的不同,相應的會導致查詢結果的不同。
示例:
--創建表
create table tb_menu(
id number(10) not null,--主鍵ID
pid number(10) not null,--父菜單ID
title varchar2(50),--菜單名稱
);
--添加數據
--父菜單
insert into tb_menu(id, pid,title ) values(1,父菜單1',0);
insert into tb_menu(id, pid,title ) values(2,父菜單2',0);
insert into tb_menu(id, pid,title ) values(3,父菜單3',0);
insert into tb_menu(id, pid,title ) values(4,父菜單4',0);
insert into tb_menu(id, pid,title ) values(5,父菜單5',0);
--一級菜單
insert into tb_menu(id, pid,title) values(6,1,'一級菜單6');
insert into tb_menu(id, pid,title) values(7,1,'一級菜單7');
insert into tb_menu(id, pid,title) values(8,1,'一級菜單8');
insert into tb_menu(id, pid,title) values(9,2,'一級菜單9');
insert into tb_menu(id, pid,title) values(10, 2, '一級菜單10');
insert into tb_menu(id, pid,title) values(11, 2, '一級菜單11');
insert into tb_menu(id, pid,title) values(12, 3,'一級菜單12');
insert into tb_menu(id, pid,title) values(13, 3,'一級菜單13');
insert into tb_menu(id, pid,title) values(14, 3,'一級菜單14');
insert into tb_menu(id, pid,title) values(15, 4,'一級菜單15');
insert into tb_menu(id, pid,title) values(16, 4,'一級菜單16');
insert into tb_menu(id, pid,title) values(17, 4,'一級菜單17');
insert into tb_menu(id, pid,title) values(18, 5,'一級菜單18');
insert into tb_menu(id, pid,title) values(19, 5,'一級菜單19');
insert into tb_menu(id, pid,title) values(20, 5,'一級菜單20');
--二級菜單
insert into tb_menu(id, title, pid) values(21, '二級菜單21',6);
insert into tb_menu(id, title, pid) values(22, '二級菜單22',6);
insert into tb_menu(id, title, pid) values(23, '二級菜單23',7);
insert into tb_menu(id, title, pid) values(24, '二級菜單24',7);
insert into tb_menu(id, title, pid) values(25, '二級菜單25',8);
insert into tb_menu(id, title, pid) values(26, '二級菜單26',9);
insert into tb_menu(id, title, pid) values(27, '二級菜單27',10);
insert into tb_menu(id, title, pid) values(28, '二級菜單28',11);
insert into tb_menu(id, title, pid) values(29, '二級菜單29',12);
insert into tb_menu(id, title, pid) values(30, '二級菜單30',13);
insert into tb_menu(id, title, pid) values(31, '二級菜單31',14);
insert into tb_menu(id, title, pid) values(32, '二級菜單32',15);
insert into tb_menu(id, title, pid) values(33, '二級菜單33',16);
insert into tb_menu(id, title, pid) values(34, '二級菜單34',17);
insert into tb_menu(id, title, pid) values(35, '二級菜單35',18);
insert into tb_menu(id, title, pid) values(36, '二級菜單36',19);
insert into tb_menu(id, title, pid) values(37, '二級菜單37',20);
--三級菜單
insert into tb_menu(id, title, pid) values(38, '三級菜單38',21);
insert into tb_menu(id, title, pid) values(39, '三級菜單39',22);
insert into tb_menu(id, title, pid) values(40, '三級菜單40',23);
insert into tb_menu(id, title, pid) values(41, '三級菜單41',24);
insert into tb_menu(id, title, pid) values(42, '三級菜單42',25);
insert into tb_menu(id, title, pid) values(43, '三級菜單43',26);
insert into tb_menu(id, title, pid) values(44, '三級菜單44',27);
insert into tb_menu(id, title, pid) values(45, '三級菜單45',28);
insert into tb_menu(id, title, pid) values(46, '三級菜單46',28);
insert into tb_menu(id, title, pid) values(47, '三級菜單47',29);
insert into tb_menu(id, title, pid) values(48, '三級菜單48',30);
insert into tb_menu(id, title, pid) values(49, '三級菜單49',31);
insert into tb_menu(id, title, pid) values(50, '三級菜單50',31);
commit;
說明:pid字段存儲的是當前菜單節點的上級id,如果菜單節點是頂級節點,該菜單沒有上級菜單,則pid應為null,然而在表中記錄最好不要為null,建議使用0代替,因為null記錄會引起全文掃描。
2.語法說明
oracle中遞歸查詢(樹形查詢)主要依托于:
select * from table_name [where 條件1] start with 條件2 connect by 條件3;
其中,[where 條件1] 可以不需要,[where 條件1]是對根據[start with 條件2 connect by 條件3]選擇出來的記錄進行過濾,是針對單條記錄的過濾,不會考慮樹形查詢的樹結構;
[start with 條件2 ]限定作為搜索起始點的條件,表示從滿足什么條件的記錄開始查詢;[connect by 條件3]表示查詢的連接條件,樹形菜單的查詢常常是因為記錄與記錄之間存在某種關系,這種關系通常就作為連接條件。
補充:connect by子句,通常跟關鍵字“PRIOR”一起使用,“PRIOR”關鍵字的位置通常有兩種,”CONNECT BY PRIOR ID = PID”和”CONNECT BY ID = PRIOR PID”,關鍵字位置的不同,相應的會導致查詢結果的不同。
3.遞歸查詢(樹形查詢)實踐
只有上面的文字說明可能還是搞不清楚樹形查詢,不過動手來操作一遍就清楚了。
我們從最基本的操作,逐步列出常見的樹查詢操作,所有的查詢以家族中的輩分作比方。
1)查詢樹中的所有頂級父節點(家族中輩分最大的那一代人)。假如這個樹是個目錄結構,那么第一個操作總是找出所有的頂級節點,然后再逐個根據頂級節點找到其子節點。
select * from tb_menu m where m.pid = 0;
以上查詢得到的就是樹的所有頂級節點,也就是家族中輩分最大的那一代人。
2)查找一個節點的直屬子節點(家族中某個長輩的所有兒子),此時查找的是直屬的子類節點,也是用不到樹形查詢的。
select * from tb_menu m where m.pid=1;
3)查找一個節點的所有直屬子節點(家族中某個長輩的所有直系子孫)。
select * from tb_menu m start with m.id=1 connect by m.pid=prior m.id;
4)查找一個節點的直屬父節點(家族中的父親),此時查找的是節點的直屬父節點,也是用不到樹形查詢的
select c.id, c.title, p.id parent_id, p.title parent_title
from tb_menu c, tb_menu p
where c.pid=p.id and c.id=6
5)查找一個節點的所有直屬父節點(家族中一個孩子的所有直系長輩祖先,比如父親,祖父,……)
select * from tb_menu m start with m.id=38 connect by prior m.pid=m.id;
說明:
上面的3)和5),這兩條查詢都是樹形查詢,區別在于”prior”關鍵字的位置不同,所以決定了查詢方式的不同,查詢結果也不同。當 pid = prior id時,數據庫會根據當前的id迭代出pid與該id相同的記錄,所以查詢的結果是迭代出了所有的子類記錄;而prior pid = id時,數據庫會跟據當前的pid來迭代出與當前的pid相同的id的記錄,所以查詢出來的結果就是所有的父類結果。
仔細看一下數據庫的查詢結果,可以發現,其實3)和5)的查詢順序也是不同的,3)是自上向下檢索,5)是自下向上檢索。
以下是一系列針對樹結構的更深層次的查詢,這里的查詢不一定是最優的查詢方式,或許只是其中的一種實現而已。
6)查詢一個節點的兄弟節點(親兄弟)
--m.parent=m2.parent-->同一個父親
select * from tb_menu m
where exists (select * from tb_menu m2 where m.pid=m2.pid and m2.id=6)
7)查詢與一個節點同級的節點(族兄弟)。 如果在表中設置了級別的字段,那么在做這類查詢時會很輕松,同一級別的就是與那個節點同級的,在這里列出不使用該字段時的實現!
with tmp as
(select a., level leaf
from tb_menu a
start with a.pid = 0
connect by a.pid = prior a.id)
select from tmp where leaf = (select leaf from tmp where id = 50);
這里使用兩個技巧,一個是使用了level來標識每個節點在表中的級別,還有就是使用with語法模擬出了一張帶有級別的臨時表。
8)查詢一個節點的父節點的的兄弟節點(伯父與叔父)
with tmp as(
select tb_menu.*, level lev
from tb_menu
start with pid=0
connect by pid = prior id)
select b.
from tmp b,(select
from tmp
where id = 21 and lev = 2) a
where b.lev = 1
union all
select
from tmp
where pid = (select distinct x.id
from tmp x, --祖父
tmp y, --父親
(select
from tmp
where id = 21 and lev > 2) z --兒子
where y.id = z.pid and x.id = y.pid);
這里查詢分成以下幾步。
首先,和第7個一樣,將全表都使用臨時表加上級別;
其次,根據級別來判斷有幾種類型,以上文中舉的例子來說,有三種情況:
(1)當前節點為頂級節點,即查詢出來的lev值為1,那么它沒有上級節點,不予考慮。
(2)當前節點為2級節點,查詢出來的lev值為2,那么就只要保證lev級別為1的就是其上級節點的兄弟節點。
(3)其它情況就是3以及以上級別,那么就要選查詢出來其上級的上級節點(祖父),再來判斷祖父的下級節點都是屬于該節點的上級節點的兄弟節點。
最后,就是使用union將查詢出來的結果進行結合起來,形成結果集。
INSERT INTO tree VALUES (1,NULL,'Kim', 1963);
INSERT INTO tree VALUES (2,NULL,'Moy', 1958);
INSERT INTO tree VALUES (3,1,'Jonas', 1976);
INSERT INTO tree VALUES (4,1,'Smith', 1974);
INSERT INTO tree VALUES (5,2,'Verma', 1973);
INSERT INTO tree VALUES (6,2,'Foster', 1972);
INSERT INTO tree VALUES (7,6,'Brown', 1981);
CREATE TABLE tree2(id int, treeid int, job varchar(32));
INSERT INTO tree2 VALUES(1,1,'Partner');
INSERT INTO tree2 VALUES(2,2,'Partner');
INSERT INTO tree2 VALUES(3,3,'Developer');
INSERT INTO tree2 VALUES(4,4,'Developer');
INSERT INTO tree2 VALUES(5,5,'Sales Exec.');
INSERT INTO tree2 VALUES(6,6,'Sales Exec.');
INSERT INTO tree2 VALUES(7,7,'Assistant');
INSERT INTO tree2 VALUES(8,null,'Secretary');
SELECT t.id,t.name,t2.job,level
FROM tree t INNER JOIN tree2 t2 ON t.id=t2.treeid
START WITH t.mgrid is null
CONNECT BY prior t.id=t.mgrid
ORDER BY t.id;
結果 能看到層次
id name job level
==============================================================
1 'Kim' 'Partner' 1
2 'Moy' 'Partner' 1
3 'Jonas' 'Developer' 2
4 'Smith' 'Developer' 2
5 'Verma' 'Sales Exec.' 2
6 'Foster' 'Sales Exec.' 2
7 'Brown' 'Assistant' 3
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY birthyear;
id mgrid name birthyear level
2 NULL 'Moy' 1958 1 6 2 'Foster' 1972 2 7 6 'Brown' 1981 3 5 2 'Verma' 1973 2 1 NULL 'Kim' 1963 1 4 1 'Smith' 1974 2 3 1 'Jonas' 1976 2SELECT id, mgrid, name, birthyear, level
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY birthyear;
level關鍵字
理解上面的,就理解下面的了
SELECT id, mgrid, name, LEVEL
FROM tree
WHERE LEVEL=2
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
還可以加條件
SELECT LEVEL FROM db_root CONNECT BY LEVEL <= 10;
CONNECT_BY_ISLEAF 關鍵字
connect_by_isleaf是葉節點1 不是0
SELECT id, mgrid, name, CONNECT_BY_ISLEAF
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
CONNECT_BY_ISCYCLE關鍵字
這個偽列功能是揪出那些“違反倫理道德”的人。
例如發現一個人既是另外一個人的孫子又是他的爸爸,這顯然是不合倫理的,需要盡快發現并進行撥亂反正.
CREATE TABLE tree_cycle(ID INT, MgrID INT, Name VARCHAR(32));
INSERT INTO tree_cycle VALUES (1,NULL,'Kim');
INSERT INTO tree_cycle VALUES (2,11,'Moy');
INSERT INTO tree_cycle VALUES (3,1,'Jonas');
INSERT INTO tree_cycle VALUES (4,1,'Smith');
INSERT INTO tree_cycle VALUES (5,3,'Verma');
INSERT INTO tree_cycle VALUES (6,3,'Foster');
INSERT INTO tree_cycle VALUES (7,4,'Brown');
INSERT INTO tree_cycle VALUES (8,4,'Lin');
INSERT INTO tree_cycle VALUES (9,2,'Edwin');
INSERT INTO tree_cycle VALUES (10,9,'Audrey');
INSERT INTO tree_cycle VALUES (11,10,'Stone');
-- Checking a CONNECT_BY_ISCYCLE value
SELECT id, mgrid, name, CONNECT_BY_ISCYCLE
FROM tree_cycle
START WITH name in ('Kim', 'Moy')
CONNECT BY NOCYCLE PRIOR id=mgrid
ORDER BY id;
id mgrid name connect_by_iscycle
==================================================
1 NULL 'Kim' 0
2 11 'Moy' 0
3 1 'Jonas' 0
4 1 'Smith' 0
5 3 'Verma' 0
6 3 'Foster' 0
7 4 'Brown' 0
8 4 'Lin' 0
9 2 'Edwin' 0
10 9 'Audrey' 0
11 10 'Stone' 1
CONNECT_BY_ROOT 關鍵字
同一個節點下的節點的connect_by_root是一樣的
SELECT id, mgrid, name, CONNECT_BY_ROOT id
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
PRIOR關鍵字
下面的說明來自oracle
運算符PRIOR被放置于等號前后的位置,決定著查詢時的檢索順序。
PRIOR被置于CONNECT BY子句中等號的前面時,則強制從根節點到葉節點的順序檢索,即由父節點向子節點方向通過樹結構,我們稱之為自頂向下的方式。如:
CONNECT BY PRIOR EMPNO=MGR
PIROR運算符被置于CONNECT BY 子句中等號的后面時,則強制從葉節點到根節點的順序檢索,即由子節點向父節點方向通過樹結構,我們稱之為自底向上的方式。例如:
CONNECT BY EMPNO=PRIOR MGR
在這種方式中也應指定一個開始的節點。
官方例子
SELECT id, mgrid, name, PRIOR id as "prior_id"
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name prior_id
=====================================================
1 NULL 'Kim' NULL
2 NULL 'Moy' NULL
3 1 'Jonas' 1
4 1 'Smith' 1
5 2 'Verma' 2
6 2 'Foster' 2
7 6 'Brown' 6
FROM tree
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER BY id;
id mgrid name hierarchy
==============================================================
1 NULL 'Kim' '/Kim'
2 NULL 'Moy' '/Moy'
3 1 'Jonas' '/Kim/Jonas'
4 1 'Smith' '/Kim/Smith'
5 2 'Verma' '/Moy/Verma'
6 2 'Foster' '/Moy/Foster'
7 6 'Brown' '/Moy/Foster/Brown'
INSERT INTO tbl VALUES (1, 'a', null);
INSERT INTO tbl VALUES (2, 'b', 'a');
INSERT INTO tbl VALUES (3, 'b', 'c');
INSERT INTO tbl VALUES (4, 'c', 'b');
INSERT INTO tbl VALUES (5, 'c', 'b');
SELECT seq, id, parent, LEVEL,
CONNECT_BY_ISCYCLE AS iscycle,
CAST(SYS_CONNECT_BY_PATH(id,'/') AS VARCHAR(10)) AS idpath
FROM tbl
START WITH PARENT is NULL
CONNECT BY NOCYCLE PARENT = PRIOR id;
seq id parent level iscycle idpath
=============================================================================
1 'a' NULL 1 0 '/a'
2 'b' 'a' 2 0 '/a/b'
4 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
5 'c' 'b' 5 1 '/a/b/c/b/c'
5 'c' 'b' 3 0 '/a/b/c'
3 'b' 'c' 4 1 '/a/b/c/b'
4 'c' 'b' 5 1 '/a/b/c/b/c'
FROM (
SELECT LEVEL lvl, base_month
FROM (
SELECT TO_DATE('201303', 'YYYYMM') base_month FROM db_root
)
CONNECT BY LEVEL <= LAST_DAY(base_month) - base_month + 1
);
h_date
======================
'20130301'
'20130302'
'20130303'
'20130304'
'20130305'
'20130306'
'20130307'
'20130308'
'20130309'
'20130310'
'20130311'
'20130312'
'20130313'
'20130314'
'20130315'
'20130316'
'20130317'
'20130318'
'20130319'
'20130320'
'20130321'
'20130322'
'20130323'
'20130324'
'20130325'
'20130326'
'20130327'
'20130328'
'20130329'
'20130330'
'20130331'
轉載于:https://www.cnblogs.com/wang2650/p/5291759.html
總結
以上是生活随笔為你收集整理的CUBRID学习笔记 42 Hierarchical QuerySQL层级查询的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: [Python]网络爬虫(11):亮剑!
- 下一篇: Linux系统编程21:基础IO之全缓冲