I have a table as below:

I'm sure you can already see the pattern - each Page can have any number of "SubPages" defined by the ParentPageId

I've been trying to get a query that can produce the following ordered output (without using the LinkSlug alphabetical ordering because they can be anything):

I tried doing some self-joins and grouping but ended up with only one level of recursion so it was no good for the 3rd and potentially nth degree sub pages, and then also tried using a CTE as I understand they're good for recursive queries but somehow ended up producing the same table I started with and am now at a loss!

The more I try the worse it gets - I know I need to effectively select top levels (with null ParentPageId) ordered by [Order], then inject wherever there's sub pages ordering by [Order], and repeat until there are no children left - but no idea how to do this in SQL.

CodePudding user response：

You can try this:

WITH cte_org (n, id, 
    LinkSlug,
    ParentPageId) AS (
SELECT
    CAST([order] as CHAR(200)),
    id, 
    LinkSlug,
    ParentPageId
FROM       
    pages
WHERE ParentPageId IS NULL
UNION ALL
SELECT 
    o.n || '_' || e.[order],
    e.id,
    e.LinkSlug,
    e.ParentPageId
FROM 
    pages e
    INNER JOIN cte_org o 
        ON o.id = e.ParentPageId)

SELECT * FROM cte_org order by n;

Note: that in MS SQL you need to use concat instead of ||; in MySQL -