SQL Server层级数据查询：关联多表获取全字段结果

First, let's break down the problem: we need to map each row in Hierarchy_Table to its corresponding record in TableA, TableB, or TableC, while also pulling in parent table details. The key requirement is to handle future field extensions in the business tables without major query rewrites.

Static Query (Works for Fixed/Extendable Fields) #

This approach uses LEFT JOIN to link all business tables to the hierarchy table, then uses conditional logic to pick values based on the Table_Name and Parent_Table columns. It's straightforward and easy to adjust when new fields are added to the business tables.

SELECT
    ht.Selected_ID,
    -- Pull fields from the corresponding business table
    ta.TableA_Name,
    ta.TableA_Desc,
    tb.TableB_Name,
    tb.TableB_Desc,
    tc.TableC_Name,
    tc.TableC_Desc,
    ht.Selected_Parent_ID,
    -- Get parent table's name and description dynamically
    COALESCE(
        CASE WHEN ht.Parent_Table = 'TableA' THEN ta_parent.TableA_Name END,
        CASE WHEN ht.Parent_Table = 'TableB' THEN tb_parent.TableB_Name END,
        CASE WHEN ht.Parent_Table = 'TableC' THEN tc_parent.TableC_Name END
    ) AS Parent_Name,
    COALESCE(
        CASE WHEN ht.Parent_Table = 'TableA' THEN ta_parent.TableA_Desc END,
        CASE WHEN ht.Parent_Table = 'TableB' THEN tb_parent.TableB_Desc END,
        CASE WHEN ht.Parent_Table = 'TableC' THEN tc_parent.TableC_Desc END
    ) AS Parent_Desc
FROM Hierarchy_Table ht
-- Join to the current record's business table
LEFT JOIN TableA ta 
    ON ht.Table_Name = 'TableA' 
    AND ht.PK_Column = 'ID1' 
    AND ta.ID1 = ht.Selected_ID
LEFT JOIN TableB tb 
    ON ht.Table_Name = 'TableB' 
    AND ht.PK_Column = 'ID2' 
    AND tb.ID2 = ht.Selected_ID
LEFT JOIN TableC tc 
    ON ht.Table_Name = 'TableC' 
    AND ht.PK_Column = 'ID3' 
    AND tc.ID3 = ht.Selected_ID
-- Join to the parent record's business table
LEFT JOIN TableA ta_parent 
    ON ht.Parent_Table = 'TableA' 
    AND ht.Parent_Column = 'ID1' 
    AND ta_parent.ID1 = ht.Selected_Parent_ID
LEFT JOIN TableB tb_parent 
    ON ht.Parent_Table = 'TableB' 
    AND ht.Parent_Column = 'ID2' 
    AND tb_parent.ID2 = ht.Selected_Parent_ID
LEFT JOIN TableC tc_parent 
    ON ht.Parent_Table = 'TableC' 
    AND ht.Parent_Column = 'ID3' 
    AND tc_parent.ID3 = ht.Selected_Parent_ID;

How It Works: #

• Business Table Links: Each LEFT JOIN connects Hierarchy_Table to a business table only if the row's Table_Name and PK_Column match the table's identity.
• Parent Data: Uses CASE and COALESCE to fetch the correct parent name/description based on the Parent_Table value.
• Extensibility: When you add a new field to, say, TableA, just add ta.New_Field to the SELECT clause—no changes needed to the JOIN logic.

Dynamic Query (Auto-Adapts to Field Extensions) #

If you want the query to automatically include new fields added to TableA, TableB, or TableC without manual edits, use dynamic SQL. This example is for SQL Server; adjust syntax for other databases (like MySQL/Oracle) as needed.

DECLARE @sql NVARCHAR(MAX);

-- Auto-generate SELECT clauses for all non-primary key fields in business tables
SELECT @sql = STRING_AGG(
    'COALESCE(CASE WHEN ht.Table_Name = ''' + TABLE_NAME + ''' THEN ' + QUOTENAME(COLUMN_NAME) + ' END, NULL) AS ' + QUOTENAME(COLUMN_NAME),
    ', '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('TableA', 'TableB', 'TableC')
AND COLUMN_NAME NOT IN (SELECT PK_Column FROM Hierarchy_Table); -- Exclude primary keys (already covered by Selected_ID)

-- Build the full query
SET @sql = '
SELECT
    ht.Selected_ID,
    ' + @sql + ',
    ht.Selected_Parent_ID,
    COALESCE(
        CASE WHEN ht.Parent_Table = ''TableA'' THEN ta_parent.TableA_Name END,
        CASE WHEN ht.Parent_Table = ''TableB'' THEN tb_parent.TableB_Name END,
        CASE WHEN ht.Parent_Table = ''TableC'' THEN tc_parent.TableC_Name END
    ) AS Parent_Name,
    COALESCE(
        CASE WHEN ht.Parent_Table = ''TableA'' THEN ta_parent.TableA_Desc END,
        CASE WHEN ht.Parent_Table = ''TableB'' THEN tb_parent.TableB_Desc END,
        CASE WHEN ht.Parent_Table = ''TableC'' THEN tc_parent.TableC_Desc END
    ) AS Parent_Desc
FROM Hierarchy_Table ht
LEFT JOIN TableA ta ON ht.Table_Name = ''TableA'' AND ht.PK_Column = ''ID1'' AND ta.ID1 = ht.Selected_ID
LEFT JOIN TableB tb ON ht.Table_Name = ''TableB'' AND ht.PK_Column = ''ID2'' AND tb.ID2 = ht.Selected_ID
LEFT JOIN TableC tc ON ht.Table_Name = ''TableC'' AND ht.PK_Column = ''ID3'' AND tc.ID3 = ht.Selected_ID
LEFT JOIN TableA ta_parent ON ht.Parent_Table = ''TableA'' AND ht.Parent_Column = ''ID1'' AND ta_parent.ID1 = ht.Selected_Parent_ID
LEFT JOIN TableB tb_parent ON ht.Parent_Table = ''TableB'' AND ht.Parent_Column = ''ID2'' AND tb_parent.ID2 = ht.Selected_Parent_ID
LEFT JOIN TableC tc_parent ON ht.Parent_Table = ''TableC'' AND ht.Parent_Column = ''ID3'' AND tc_parent.ID3 = ht.Selected_Parent_ID';

-- Execute the dynamic query
EXEC sp_executesql @sql;

Key Notes: #

• Auto-Field Detection: Uses INFORMATION_SCHEMA.COLUMNS to fetch all non-primary key fields from the business tables, so new fields are automatically included.
• Database-Specific Adjustments: For MySQL, replace STRING_AGG with GROUP_CONCAT and use PREPARE/EXECUTE to run the dynamic SQL. For Oracle, use LISTAGG and EXECUTE IMMEDIATE.

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