表結(jié)構(gòu)
CREATE TABLE `t_organization` (
`id` int(8) NOT NULL AUTO_INCREMENT COMMENT '組織id',
`name` varchar(50) NOT NULL COMMENT '組織名稱',
`pid` int(8) DEFAULT NULL COMMENT '組織上級(jí)id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
案例一：需要獲取組織乡摹，以及根據(jù)組織的上級(jí)id獲取上級(jí)組織名稱
這種情況可以通過(guò)左連接實(shí)現(xiàn)
SELECT
t1.id,
t1.`name`,
t1.pid,
t2.`name`
FROM
t_organization t1
LEFT JOIN t_organization t2 ON t1.pid = t2.id
可以看到左連接是以左表為基準(zhǔn)蛉抓，通過(guò)關(guān)聯(lián)關(guān)系id = pid去找到對(duì)應(yīng)的上級(jí)組織記錄悯恍，所以空的id找不到對(duì)應(yīng)的記錄，返回空
案例二：需要獲取組織穆趴，以及根據(jù)組織的完整路徑
有時(shí)候我們需要獲取某個(gè)組織的完整路徑 如
部門C/部門C_2/部門C_2_1/部門C_2_1_1
編寫(xiě)存儲(chǔ)過(guò)程饿凛，生成一個(gè)臨時(shí)表tmpLst，按照層級(jí)把每一條記錄插入到臨時(shí)表剖笙，然后每次從臨時(shí)表查當(dāng)前層級(jí)的組織卵洗，循環(huán)去查組織表的上級(jí)組織，直到結(jié)果ROW_COUNT = 0為止弥咪，代表當(dāng)前層級(jí)下的所有組織已經(jīng)是最后一級(jí)
CREATE PROCEDURE proce ( IN rootid INTEGER )
BEGIN
DECLARE LEVEL INT;
DROP TABLE IF EXISTS tmpLst;
CREATE TABLE tmpLst (
id INT,
nLevel INT,
orgName VARCHAR ( 8000 )
);
SET LEVEL = 0;
INSERT INTO tmpLst
SELECT
id,
LEVEL,
`name`
FROM
t_organization;
WHILE
ROW_COUNT()> 0
DO
SET LEVEL = LEVEL + 1;
INSERT INTO tmpLst SELECT
A.ID,
LEVEL,
concat( B.orgName, '/' ,A.name )
FROM
t_organization A,
tmpLst B
WHERE
A.PID = B.ID
AND B.nLevel = LEVEL - 1;
END WHILE;
END;
查詢到的結(jié)果忌怎，大家可以自行優(yōu)化一下顯示方式和查詢的字段
當(dāng)然還有另一種方式，從設(shè)計(jì)上解決
如新加一個(gè)唯一約束酪夷，把組織的約束定義為 ORG_001_ORG_001_002_ORG_001_003 這樣的形式
當(dāng)需要查詢ORG_001所有的下級(jí)時(shí)榴啸，只需要查詢約束 like ORG_001% 即可
當(dāng)需要查詢ORG_001_002所有上級(jí)時(shí)，只需要查詢約束 like %ORG_001_002
不過(guò)問(wèn)題在于如果組織的存在架構(gòu)調(diào)整晚岭，如鸥印，ORG_001_002調(diào)整到了 ORG_002下，因?yàn)闃?shù)型結(jié)構(gòu)變化了坦报，直接用like無(wú)法查詢到正確數(shù)據(jù)库说，這個(gè)時(shí)候要考慮是否允許調(diào)整或者調(diào)整后修改對(duì)應(yīng)的唯一約束
