树结构层级查询
WITH RECURSIVE
注:mysql 8.0版本以上;8.0以下需要使用下属 resultMap+collection 的方法进行递归
Mysql 递归
WITH RECURSIVE cte AS (
SELECT *, 1 as level
FROM test_simple
WHERE id = 1
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
)
SELECT *
FROM cte
首先获取初始值:
SELECT *, 1 as level
FROM test_simple
WHERE id = 1
再将初始值传入递归中:
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
cte即为上一级递归过去的值,需要保证每次返回类型与初始值相同;
示例
-- 查询表中指定项目及其子项目层级
-- 查询当前项目最深层级,查其子,并取最大值
WITH RECURSIVE cte AS (
SELECT *, 1 as level
FROM test_simple
WHERE id = 1
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
)
-- SELECT max(level) --获取1的子的最深层级
SELECT * --获取1的所有子并显示层级
FROM cte
-- 查询表中指定项目及其父项目层级
-- 查询当前项目层级,查其父,并取最大值
WITH RECURSIVE cte AS (
SELECT *, 1 as level
FROM test_simple
WHERE id = 2
UNION ALL
SELECT t.id, t.parent_id, cte.level + 1
FROM test_simple t
JOIN cte ON t.id = cte.parent_id
)
SELECT max(level) --获取2的层级
FROM cte
-- 获取项目子项目数,查所有子--不含自身
WITH RECURSIVE cte AS (
SELECT id, parent_id
FROM test_simple
WHERE parent_id = 1
UNION ALL
SELECT t.id, t.parent_id
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
)
SELECT COUNT(*) AS total_count
FROM cte;
对应mybatis xml语句:
<select id="xxx" resultType="Integer">
<!-- 以上sql语句 -->
</select>
树结构子数量查询
-- 获取项目子项目数,查所有子--含自身
WITH RECURSIVE cte AS (
SELECT id, parent_id
FROM test_simple
WHERE id = 3337037
UNION ALL
SELECT t.id, t.parent_id
FROM test_simple t
JOIN cte ON t.parent_id = cte.id
)
SELECT COUNT(*) AS total_count
FROM cte;
获取树结构数据
resultMap+collection
collection的select属性可以指定子查询;column将父查询返回属性传入子查询中
子查询类型与父查询类型一致时形成多层级树结构数据
<resultMap id="tree" type="Type">
<id property="id" column="id" javaType="java.lang.Integer"/>
<id property="parent_id" column="parentId" javaType="java.lang.Integer"/>
<collection property="children" select="getChildren" column="{parentId=id}"/>
</resultMap>
<select id="getTree" resultMap="tree">
select * from test_simple where id = 1
</select>
<select id="getChildren" resultMap="tree">
select * from test_simple where parent_id = #{parentId}
</select>
父查询参数传递
提取查询参数作为临时表;Type中需要增加这个参数
<resultMap id="tree" type="Type">
<id property="id" column="id" javaType="java.lang.Integer"/>
<id property="parent_id" column="parentId" javaType="java.lang.Integer"/>
<id property="implParam " column="implParam " javaType="java.lang.Integer"/>
<collection property="children" select="getChildren" column="{parentId=id, implParam=implParam}"/>
</resultMap>
<select id="getTree" resultMap="tree">
select *,#{param} as implParam from test_simple where id = 1
</select>
<select id="getChildren" resultMap="tree">
select * from test_simple where parent_id = #{parentId} and my_param=implParam
</select>