Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH RECURSIVE traversed (id, name, path, `left`, `right`) AS (
- SELECT id,
- name,
- CAST(JSON_ARRAY(id) AS JSON),
- `left`,
- `right`
- FROM binary_tree
- WHERE id = 1
- UNION
- SELECT b.id,
- b.name,
- JSON_ARRAY_APPEND(t.path, '$', b.id),
- b.`left`,
- b.`right`
- FROM traversed t
- LEFT JOIN binary_tree b
- ON b.`id` = t.`left`
- WHERE t.`left` IS NOT NULL
- UNION
- SELECT b.id,
- b.name,
- JSON_ARRAY_APPEND(t.path, '$', b.id),
- b.`left`,
- b.`right`
- FROM traversed t
- LEFT JOIN binary_tree b
- ON b.`id` = t.`right`
- WHERE t.`right` IS NOT NULL
- )
- SELECT json_length(path) AS depth,
- id,
- name,
- path,
- `left`,
- `right`
- FROM traversed;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement