如何将子表扁平至父表？数据库父子关系转全父级需求

Got it, let's break this down. You're looking to flatten a parent-child hierarchy (Thing → Widget) into a single "all-parent-level" list, where both Things and their associated Widgets appear in a unified view—while preserving the original sort order based on thing_rank and widget_rank. Here's a practical, database-friendly solution:

Step 1: Define the Problem with Sample Data #

First, let's ground this in concrete example tables to make the solution tangible. Suppose we have two tables:

• thing: Stores top-level items with their sort rank
• widget: Stores child items linked to Things, with their own sort rank under their parent

-- Sample Thing table
CREATE TABLE thing (
    thing_id INT PRIMARY KEY,
    thing_name VARCHAR(50),
    thing_rank INT
);

INSERT INTO thing VALUES
(1, 'Thing A', 2),
(2, 'Thing B', 1),
(3, 'Thing C', 3);

-- Sample Widget table (child of Thing)
CREATE TABLE widget (
    widget_id INT PRIMARY KEY,
    thing_id INT FOREIGN KEY REFERENCES thing(thing_id),
    widget_name VARCHAR(50),
    widget_rank INT
);

INSERT INTO widget VALUES
(101, 2, 'Widget B1', 1),
(102, 2, 'Widget B2', 2),
(103, 1, 'Widget A1', 1);

Step 2: Flatten the Hierarchy with SQL #

We'll use UNION ALL to combine both tables into a single result set, then sort using a dual-rank system to maintain the desired order:

• Primary sort: thing_rank (ensures all items from the same Thing group together)
• Secondary sort: A custom rank where Things come first (rank 0) followed by their Widgets (using widget_rank)

SELECT 
    COALESCE(t.thing_id, w.thing_id) AS parent_thing_id,
    'Thing' AS item_type,
    t.thing_name AS item_name,
    t.thing_rank AS primary_sort_rank,
    0 AS secondary_sort_rank
FROM thing t

UNION ALL

SELECT 
    w.thing_id AS parent_thing_id,
    'Widget' AS item_type,
    w.widget_name AS item_name,
    t.thing_rank AS primary_sort_rank,
    w.widget_rank AS secondary_sort_rank
FROM widget w
JOIN thing t ON w.thing_id = t.thing_id

ORDER BY primary_sort_rank, secondary_sort_rank;

Step 3: Result Explanation #

The query returns a flattened list that matches the user-facing requirement:

Key details:

• item_type lets you distinguish between top-level Things and their child Widgets (remove this column if you don't need to display the type)
• COALESCE ensures we always have a valid parent Thing ID for every row
• The dual sort guarantees Things appear before their Widgets, and all items follow the original thing_rank and widget_rank order

Optional Adjustments #

• If you don't need the sort rank columns in the final output, just exclude them from the SELECT clause
• For databases that support CTEs (like PostgreSQL, SQL Server), you can wrap the union in a CTE for better readability, but UNION ALL is the most performant approach for this use case

内容的提问来源于stack exchange，提问作者broc.seib