如何查询组织结构（组织结构树）

创建简单的组织表

• 字段1： 组织ID
• 字段2：组织名称
• 字段3：组织的父级ID

-- 创建组织结构表
CREATE TABLE organization (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(100),
    parent_id VARCHAR(36)
);

插入几条数据

INSERT INTO organization (id, name, parent_id)
VALUES
    ("00", '组织A', NULL),
    ("11", '组织B', NULL),
    ("0001", '子组织A1', "00"),
    ("0002", '子组织A2', "00"),
    ("1101", '子组织B1', "11"),
    ("1102", '子组织B2', "11"),
    ("000101", '子组织A1.1', "0001"),
    ("000102", '子组织A1.2', "0001"),
    ("000201", '子组织A2.1', "0002"),
    ("000202", '子组织A2.2', "0002"),
    ("110101", '子组织B1.1', "1101"),
    ("110102", '子组织B1.2', "1101"),
    ("110201", '子组织B2.1', "1102"),
    ("110202", '子组织B2.2', "1102");

查询组织

1、查询所有三级组织

• sql

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    parent.parent_id IS NULL

• 输出结果
  

2、查询A组织下的所有组织（查第一级下的所有组织）

因为这里的ID都是有规律，前缀可以识别，所以使用用like模糊查询，如果ID不能通过前缀来判别，则不能使用。

• sql

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    parent.parent_id IS NULL  and child2.id like '00%';

• 输出结果
  

3、查询B1组织下的所有组织（查第二级下的所有组织）

还是使用前缀的特点模糊查询。

• sql

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    parent.parent_id IS NULL  and child2.id like '1101%';

• 输出结果

4、查询B1.1组织（具体的第三级组织）

• sql

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    parent.parent_id IS NULL  and child2.id like '110101%';

• 输出结果
  

5、mybatis的写法

使用mybatis的xml写sql时，只需要使用#{ID}进行参数替换，并拼接就行。

<where>
	<if test="id != null and id != ''">
		id like CONCAT(#{id}, '%')
	</if>
</where>

无前缀规律

1、查询B组织下的所有组织

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    parent.id = "11"; -- 要查询的组织B的id

2、查询B1组织下的所有组织

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    child1.id = "1101"; -- 要查询的组织B1的id

3、查询第三级B1.1组织

SELECT 
    parent.id AS parent_id,
    parent.name AS parent_name,
    child1.id AS child1_id,
    child1.name AS child1_name,
    child2.id AS child2_id,
    child2.name AS child2_name
FROM 
    organization AS parent
JOIN 
    organization AS child1 ON parent.id = child1.parent_id
JOIN 
    organization AS child2 ON child1.id = child2.parent_id
WHERE 
    child2.id = "110101"; -- 要查询的组织B1.1的id

4、mybatis的xml的sql写法

这里idOne是第一级ID，idTwo是第二级ID，idThree是第三级ID,并且只能传一个，这样传第一级时才能查出下面的所有组织。

<where>
	<if test="idOne != null and idOne != ''">
		parent.id = #{idOne}
	</if>
	<if test="idTwo != null and idTwo != ''">
		child1.id = #{idTwo}
	</if>
	<if test="idThree != null and idThree != ''">
		child2.id = #{idThree}
	</if>
</where>

这是我目前能想到的方法，大家都更好的解决方案，可以一起交流。

结束！！！！！
hy:19

---

						人而不学，其犹正墙面而立。--《尚书》