试试这个：

WITH cte AS
(
  SELECT 
    cat_id, 
    cat_parent_id,
    cat_name,
    0 AS level
  FROM category
  WHERE cat_parent_id = 0
  UNION ALL
  SELECT 
    cat.cat_id, 
    cat.cat_parent_id,
    cat.cat_name,
    (level + 1) AS level
  FROM category cat
  INNER JOIN cte c
    ON c.cat_id = cat.cat_parent_id
)

SELECT 
  cat_id, 
  cat_parent_id,
  cat_name,
  level
FROM cte
--ORDER BY level;