0
SELECT
p1.res_id,
p1.res_name,
p1.res_type,
p1.file_id,
p1.create_time,
p1.parent_id,
p1.parent_path,
t4.fullname AS create_user,
t5.orgshortname org_name,
t3.label_describe,
( CASE WHEN p1.res_type IN ( 'file' ) THEN NULL ELSE p3.oss_down_path END ) oss_down_path,
p3.file_size,
GROUP_CONCAT( p2.res_name ORDER BY p1.n SEPARATOR '/' ) AS all_parent_name 
FROM
    (
    SELECT
        t2.*,
        SUBSTRING_INDEX( SUBSTRING_INDEX( t2.parent_path, ',', t1.n ), ',', - 1 ) AS linkid,
        t1.n 
    FROM
        tmp t1
        CROSS JOIN tb_vk_resources t2 
    WHERE
        t1.n BETWEEN 1 
        AND (
        SELECT
            1 + LENGTH( parent_path ) - LENGTH(
            REPLACE ( parent_path, ',', '' )))) p1
    LEFT OUTER JOIN tb_vk_resources p2 ON ( p1.linkid = p2.res_id )
    LEFT OUTER JOIN tb_vk_res_basics t3 ON p1.relation_id = t3.res_basics_id
    LEFT OUTER JOIN tb_sys_user t4 ON p1.create_user = t4.userid
    LEFT OUTER JOIN tb_idm_org t5 ON t3.org_name = t5.orgid
    LEFT OUTER JOIN tb_oss_file_details p3 ON ( p1.file_id = p3.id ) 
WHERE
    p1.parent_id = '1'  
GROUP BY
    p1.res_id 
    LIMIT 100 OFFSET 0;

The sql reported a mistake in MySQL5.7 and got the result I wanted in MySQL8.0, which I know is due to 'with recursive as'. But I don't know how to modify it so that he can get the results I want in mysql5.7.

sunny
  • 1
  • Does this answer your question? [Recursive search in Mysql 5.7.30](https://stackoverflow.com/questions/66348533/recursive-search-in-mysql-5-7-30) – Nico Haase Sep 26 '21 at 08:27
  • 2
    CTEs are supported in MySQL 8+ only. Your query doesn't contain a cte though, so it's not clear if this is already your failed attempt to rewrite it, or if you have a different problem. Without sample data, expected and wrong result and probably your actual working query, we cannot help you though. General ways to build a hierarchical query in MySQL 5.x are described in [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/q/20215744) – Solarflare Sep 26 '21 at 08:49

0 Answers0