层级结构-叶子层级数据及SQL分支合并场景技术问询

Hey folks, let's dive into this temp table setup for branch merging—it's a solid approach, and I'll break down how it works, its strengths, and some useful queries you can run with it.

First, let's look at the full SQL code you've provided, formatted for clarity:

CREATE TABLE #Temp ( 
    ID INT, 
    BranchID_New INT, 
    BranchID_Old INT, 
    DateCreated DATETIME2, 
    PRIMARY KEY ( [ID], [BranchID_New], [BranchID_Old] ) 
) ;

INSERT INTO #Temp 
SELECT 1, 1, 1, '2018-04-11 00:00:00.0000000' 
UNION ALL 
SELECT 1, 2, 2, '2018-04-11 00:00:00.0000000' 
UNION ALL 
SELECT 1, 3, 3, '2018-04-11 00:00:00.0000000' 
UNION ALL 
SELECT 1, 4, 1, '2018-04-11 12:00:00.0000000' -- BranchID_New 1 and 2 merged into BranchID_New 4
UNION ALL 
SELECT 1, 4, 2, '2018-04-11 12:00:00.0000000';

What this does:

• The temp table tracks relationships between old branch IDs and new branch IDs for a given ID (likely a parent entity like a user or project), plus a timestamp for when the relationship was created.
• The initial insert sets up three independent branches: ID=1 has branches 1, 2, 3 (each new branch maps directly to its old counterpart) created at midnight.
• Later that day (noon), branches 1 and 2 are merged into a new branch 4—so we add two records linking the new branch 4 to both old branches 1 and 2.

Let's highlight why this setup works well for branch merging:

• Composite Primary Key: The (ID, BranchID_New, BranchID_Old) PK ensures you can't have duplicate mappings for the same entity, new branch, and old branch. This prevents accidental duplicate inserts, which is critical when merging branches where one new branch links to multiple old ones.
• Historical Tracking: The DateCreated field lets you trace when branch relationships were established. You can easily distinguish between the original standalone branches and the merged branch created later.

If you need to work with this data, here are a couple of common scenarios and their solutions:

Get the Latest Branch Mapping for Each Old Branch #

This query returns the most current branch relationship for each old branch (so merged branches take precedence over older standalone ones):

WITH RankedBranches AS (
    SELECT 
        ID, 
        BranchID_New, 
        BranchID_Old, 
        DateCreated,
        -- Rank mappings for each old branch by creation time (newest first)
        ROW_NUMBER() OVER (PARTITION BY ID, BranchID_Old ORDER BY DateCreated DESC) AS rn
    FROM #Temp
)
SELECT ID, BranchID_New, BranchID_Old, DateCreated
FROM RankedBranches
WHERE rn = 1;

For your sample data, this will return:

• ID=1, BranchID_New=4, BranchID_Old=1 (merged mapping)
• ID=1, BranchID_New=4, BranchID_Old=2 (merged mapping)
• ID=1, BranchID_New=3, BranchID_Old=3 (original standalone mapping)

Find All Merged Branch Records #

If you want to identify which new branches are the result of merging multiple old branches:

SELECT 
    BranchID_New, 
    STRING_AGG(BranchID_Old, ', ') AS Merged_Old_Branches,
    MIN(DateCreated) AS Merge_Timestamp
FROM #Temp
GROUP BY BranchID_New
HAVING COUNT(BranchID_Old) > 1;

This will output:

• Index for Merging Queries: If you frequently run queries that filter or group by BranchID_New, add a nonclustered index to speed things up:

  CREATE NONCLUSTERED INDEX IX_Temp_BranchMerge ON #Temp (BranchID_New) INCLUDE (BranchID_Old, DateCreated);
  

• Cleanup Old Mappings (If Needed): If your business doesn't require keeping historical branch relationships, you can delete the old standalone records after inserting the merged ones. Just be sure this aligns with your data retention policies!

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