SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Files ( id INT, name VARCHAR(40), parent_directory_id INT ); INSERT INTO Files (id, name, parent_directory_id) VALUES (1, 'desktop', NULL), (2, 'test', 1), (3, 'Картинки', 2), (4, '1.jpg', 3), (5, 'avatar.png', 3), (6, 'certificate.png', 3), (7, 'py.png', 3), (8, 'World_Time_Zones_Map.png', 3), (9, 'Снимок экрана.png', 3), (10, 'Неравенства.djvu', 2), (11, 'Программы', 2), (12, 'image_util.py', 11), (13, 'sort.py', 11), (14, 'Разные файлы', 2), (15, 'astros.json', 14); with recursive Mapping as ( select id as node_id, parent_directory_id as parent_node_id, name as node_name from Files ), Levels as ( select node_id, parent_node_id, node_name, cast(parent_node_id as char(2000)) as parents, cast(node_name as char(2000)) as full_path, 0 as node_level from Mapping where parent_node_id is null union select Mapping.node_id, Mapping.parent_node_id, Mapping.node_name, concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as char)), concat_ws(char(9), prev.full_path, Mapping.node_name), prev.node_level + 1 from Levels as prev inner join Mapping on Mapping.parent_node_id = prev.node_id ), Branches as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, case when parent_node_id is not null then case when node_id = last_value(node_id) over WindowByParents then '└── ' else '├── ' end else '' end as node_branch, case when parent_node_id is not null then case when node_id = last_value(node_id) over WindowByParents then ' ' else '│ ' end else '' end as branch_through from Levels window WindowByParents as ( partition by parents order by node_name rows between current row and unbounded following ) order by full_path ), Tree as ( select node_id, parent_node_id, node_name, parents, full_path, node_level, node_branch, cast(branch_through as char(2000)) as all_through from Branches where parent_node_id is null union select Branches.node_id, Branches.parent_node_id, Branches.node_name, Branches.parents, Branches.full_path, Branches.node_level, Branches.node_branch, concat(prev.all_through, Branches.branch_through) from Tree as prev inner join Branches on Branches.parent_node_id = prev.node_id ), FineTree as ( select tr.node_id, tr.parent_node_id, tr.node_name, tr.parents, tr.full_path, tr.node_level, concat(coalesce(parent.all_through, ''), tr.node_branch, tr.node_name) as fine_tree from Tree as tr left join Tree as parent on parent.node_id = tr.parent_node_id order by tr.full_path ) select fine_tree from FineTree ;