SQL父子-祖孙层级结构：LIST表层级化转换实现指引

Alright, let's break down how to tackle this problem step by step. You've got an unordered table LIST with multi-level parent-child relationships, and you need to transform it into a new table with unique IDs that follow the hierarchical order. Here's a clear, programmatic roadmap to get this done:

The key idea is to first map out the parent-child hierarchy from your LIST table, then traverse that structure in the desired order (depth-first or breadth-first) while assigning unique IDs. Finally, you'll write the ordered, ID-tagged records to a new table.

1. Extract & Map Parent-Child Relationships #

First, pull all data from the LIST table and build a mapping that links each parent node to its list of child nodes. You'll also need to identify root nodes (entries with no parent, or whose parent doesn't exist in the table).

• For in-memory processing (e.g., Python, Java), use a dictionary/hash map where the key is the parent name, and the value is a list of child names.
• For database-native processing, use a recursive Common Table Expression (CTE) to directly build the hierarchy within the database.

2. Generate Hierarchical Order & Unique IDs #

Traverse the mapped hierarchy using either Depth-First Search (DFS) (to go deep into subtrees first) or Breadth-First Search (BFS) (to process all nodes at the same level first). As you traverse:

• Assign unique IDs: You can use simple incrementing integers, or human-readable hierarchical IDs like 1, 1.1, 1.1.1 (great for visualizing hierarchy at a glance).
• Track additional metadata if needed (e.g., hierarchy level, full path from root to current node) for better context in the new table.

3. Write to New Table #

Once you have the ordered list of records with their unique IDs, create a new table (define columns like UniqueID, Name, ParentName, HierarchyLevel, etc.) and insert the processed data. For large datasets, use bulk inserts to optimize performance.

Python + Pandas + SQLite #

If you prefer using a scripting language to handle the logic:

import pandas as pd
from collections import defaultdict
import sqlite3

# Connect to your database and load the LIST table
conn = sqlite3.connect('your_database.db')
df = pd.read_sql("SELECT Name, ParentName FROM LIST", conn)

# Build parent-child map and identify root nodes
parent_map = defaultdict(list)
all_names = set(df['Name'])
root_nodes = []

for _, row in df.iterrows():
    parent = row['ParentName']
    child = row['Name']
    parent_map[parent].append(child)
    # Mark as root if parent isn't present in the Name column
    if parent not in all_names:
        root_nodes.append(parent)

# DFS traversal to generate ordered records with IDs
result = []
current_id = 1

def dfs(node, parent_id, level):
    nonlocal current_id
    result.append({
        'UniqueID': current_id,
        'Name': node,
        'ParentID': parent_id,
        'HierarchyLevel': level
    })
    current_id += 1
    # Recurse through all child nodes
    for child in parent_map.get(node, []):
        dfs(child, current_id - 1, level + 1)

# Process all root nodes
for root in root_nodes:
    dfs(root, None, 1)

# Write to new table
result_df = pd.DataFrame(result)
result_df.to_sql('HIERARCHICAL_LIST', conn, if_exists='replace', index=False)
conn.close()

SQL Recursive CTE (PostgreSQL/MySQL 8+/SQL Server) #

If you want to handle everything directly in the database:

-- Create the new hierarchical table
CREATE TABLE HIERARCHICAL_LIST (
    UniqueID SERIAL PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    ParentName VARCHAR(255),
    HierarchyPath TEXT,
    HierarchyLevel INT
);

-- Use recursive CTE to build and insert ordered data
WITH RECURSIVE HierarchyCTE AS (
    -- Base case: select all root nodes
    SELECT 
        Name,
        ParentName,
        CAST(Name AS TEXT) AS HierarchyPath,
        1 AS HierarchyLevel
    FROM LIST
    WHERE ParentName IS NULL OR ParentName NOT IN (SELECT Name FROM LIST)
    
    UNION ALL
    
    -- Recursive case: join child nodes to their parents
    SELECT 
        child.Name,
        child.ParentName,
        CONCAT(parent.HierarchyPath, ' > ', child.Name) AS HierarchyPath,
        parent.HierarchyLevel + 1 AS HierarchyLevel
    FROM LIST child
    INNER JOIN HierarchyCTE parent ON child.ParentName = parent.Name
)
-- Insert into new table, ordered by hierarchy path
INSERT INTO HIERARCHICAL_LIST (Name, ParentName, HierarchyPath, HierarchyLevel)
SELECT Name, ParentName, HierarchyPath, HierarchyLevel
FROM HierarchyCTE
ORDER BY HierarchyPath;

• Cycle Detection: Check for circular parent-child references (e.g., A → B → A) to avoid infinite loops during traversal. Track visited nodes in your code or add constraints in the database.
• Performance: For very large tables, database-native CTEs are usually more efficient than in-memory processing. If using a script, consider processing data in batches.
• ID Flexibility: Choose an ID format that fits your needs—simple integers work for sorting, while hierarchical IDs make the structure easier to interpret at a glance.

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