當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

CUBRID学习笔记 42 Hierarchical QuerySQL层级查询

發布時間：2025/3/15 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 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]表示查詢的連接條件，樹形菜單的查詢常常是因為記錄與記錄之間存在某種關系，這種關系通常就作為連接條件。

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將查詢出來的結果進行結合起來，形成結果集。

**來看看官方的**CREATE TABLE tree(ID INT, MgrID INT, Name VARCHAR(32), BirthYear INT);

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

Ordering Data with the Hierarchical QuerySELECT id, mgrid, name, birthyear, level

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 2

SELECT 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

SYS_CONNECT_BY_PATH SYS_CONNECT_BY_PATH (column_name, separator_char) 主要用于樹查詢(層次查詢) 以及多列轉行SELECT id, mgrid, name, SYS_CONNECT_BY_PATH(name,'/') as [hierarchy]

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'

單元練習例子 CREATE TABLE tbl(seq INT, id VARCHAR(10), parent VARCHAR(10));

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'

今天之后是明天,明天之后是后天,后天之后是大后天SELECT TO_CHAR(base_month + lvl -1, 'YYYYMMDD') h_date

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之全缓冲