Advertisement
Guest User

Untitled

a guest
Mar 21st, 2019
83
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.79 KB | None | 0 0
  1. WITH RECURSIVE traversed (id, name, path, `left`, `right`) AS (
  2. SELECT id,
  3. name,
  4. CAST(JSON_ARRAY(id) AS JSON),
  5. `left`,
  6. `right`
  7. FROM binary_tree
  8. WHERE id = 1
  9. UNION
  10. SELECT b.id,
  11. b.name,
  12. JSON_ARRAY_APPEND(t.path, '$', b.id),
  13. b.`left`,
  14. b.`right`
  15. FROM traversed t
  16. LEFT JOIN binary_tree b
  17. ON b.`id` = t.`left`
  18. WHERE t.`left` IS NOT NULL
  19. UNION
  20. SELECT b.id,
  21. b.name,
  22. JSON_ARRAY_APPEND(t.path, '$', b.id),
  23. b.`left`,
  24. b.`right`
  25. FROM traversed t
  26. LEFT JOIN binary_tree b
  27. ON b.`id` = t.`right`
  28. WHERE t.`right` IS NOT NULL
  29. )
  30. SELECT json_length(path) AS depth,
  31. id,
  32. name,
  33. path,
  34. `left`,
  35. `right`
  36. FROM traversed;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement