mysql单表递归查询(jpa[org.hibernate.QueryException: Space is not allowed afterQuery parameter prefix ':'])

来源：互联网发布：山东外贸职业学院知乎编辑：程序博客网时间：2024/05/22 12:41

组织表字段id，及parentId，数据库mysql5.7

①解决的问题点：需要查出一个组织树结构，根据指定ID获取当前ID的所有子树。

select id,

parent_id
from (select * from hrm_organization
order by parent_id, id) organization,
(select @pv := '18') initialisation
where find_in_set(parent_id, @pv) > 0

and @pv := concat(@pv, ',', id);

②解决的问题点：查出一条线性结构，查询当前ID的所有父级

SELECT
t.id,
@pv := t.parent_id
FROM
(
SELECT
*
FROM
hrm_organization
ORDER BY
id DESC
) t
JOIN (SELECT @pv := 31 from hrm_organization) tmp
WHERE
t.id =@pv;

③解决【：】错误问题：在：前加转义字符【\\】

SELECT @pv \\:= :organizationId

@Query(nativeQuery = true, value="SELECT id,name,budget_number,parent_id,description FROM hrm_organization WHERE del_flag = FALSE ORDER BY parent_id,id) organization, (SELECT @pv \\:= :organizationId) initialisation WHERE find_in_set(parent_id, @pv) > 0 AND @pv \\:= concat(@pv, ',', id) ")

0 0