基于previous字段的SQL表排序及首项指定方案问询

Hey there! I get why you found the PHP array approach clunky—this linked-list style ordering is perfect for a SQL-based solution, no messy array manipulation needed. Let's break down how to make this work exactly how you want it.

The Core Approach #

Your table is structured as a singly linked list, where each row points to its predecessor via the previous field. We need to recursively traverse this list starting from the item where previous = 0 (your desired first item), then build a sorted sequence step by step.

Step 1: Example Setup (to match your desired order) #

First, let's define a sample table and insert data that matches your expected output:

CREATE TABLE your_table (
    ID INT PRIMARY KEY,
    content TEXT,
    previous INT DEFAULT 0
);

INSERT INTO your_table (ID, content, previous) VALUES
(12753, 'First item content', 0),
(41036, 'Second item content', 12753),
(24985, 'Third item content', 41036),
(31689, 'Fourth item content', 24985),
(54985, 'Fifth item content', 31689);

Step 2: Recursive CTE (Modern Databases: MySQL 8+, PostgreSQL, SQL Server, etc.) #

The cleanest way to do this is with a Recursive Common Table Expression (CTE). This lets us start at the root node (previous=0) and recursively fetch each subsequent item in the chain.

Here's the query:

WITH RECURSIVE sorted_items AS (
    -- Anchor: Grab the starting item (previous = 0) and set initial sort order
    SELECT ID, content, previous, 1 AS sort_order
    FROM your_table
    WHERE previous = 0
    
    UNION ALL
    
    -- Recursive step: Link each item to its successor using the previous field
    SELECT t.ID, t.content, t.previous, si.sort_order + 1
    FROM your_table t
    JOIN sorted_items si ON t.previous = si.ID
)
-- Final output sorted by our generated sort_order
SELECT ID, content
FROM sorted_items
ORDER BY sort_order;

What this does: #

• The sorted_items CTE starts with your first item (where previous=0) and assigns it a sort_order of 1.
• The recursive part joins the CTE back to the original table to find the next item (where previous matches the current item's ID), incrementing the sort_order each time.
• Finally, we select all items and order them by sort_order—giving you exactly the sequence you want: 12753 → 41036 → 24985 → 31689 → 54985.

Step 3: Legacy Database Alternative (MySQL 5.x) #

If you're stuck on an older MySQL version that doesn't support CTEs, you can use user-defined variables to traverse the list:

SELECT ID, content
FROM (
    SELECT 
        t.*,
        @sort_order := @sort_order + 1 AS sort_order
    FROM your_table t
    -- Initialize variables: start with the root item's ID
    JOIN (SELECT @sort_order := 0, @current_id := (SELECT ID FROM your_table WHERE previous = 0)) vars
    -- Traverse the list by matching previous to the current ID, then update current_id
    WHERE t.ID = @current_id OR (t.previous = @current_id AND @current_id := t.ID)
    ORDER BY previous
) sorted
ORDER BY sort_order;

This works, but it's less readable than the CTE approach—so use it only if you have to.

Key Benefits #

• No PHP required: All sorting happens directly in the database, keeping your code cleaner.
• Dynamic adaptation: If you add or rearrange items in the linked list, the query will automatically adjust the order without any changes.
• Scalable: Works for any length of linked list (as long as your database supports recursive queries, which most modern ones do).

内容的提问来源于stack exchange，提问作者Thomas Diot