如何按年龄拆分规则将Table A数据Insert into空Table B？

To achieve the desired row splitting based on your age rules, a recursive Common Table Expression (CTE) is the most straightforward and efficient approach in SQL. Here's how to implement it:

Step-by-Step SQL Implementation #

WITH RecursiveAgeSplit AS (
    -- Base case: Initialize with all rows from Table A
    SELECT 
        Name, 
        phoneNumber,
        age AS original_age,
        CASE WHEN age >= 10 THEN 10 ELSE age END AS split_age,
        CASE WHEN age >= 10 THEN age - 10 ELSE 0 END AS remaining_age
    FROM TableA
    
    UNION ALL
    
    -- Recursive step: Generate rows until remaining age is 0
    SELECT 
        Name, 
        phoneNumber,
        original_age,
        CASE WHEN remaining_age >= 10 THEN 10 ELSE remaining_age END AS split_age,
        CASE WHEN remaining_age >= 10 THEN remaining_age - 10 ELSE 0 END AS remaining_age
    FROM RecursiveAgeSplit
    WHERE remaining_age > 0
)
-- Insert the split rows into Table B
INSERT INTO TableB (Name, age, phoneNumber)
SELECT Name, split_age, phoneNumber
FROM RecursiveAgeSplit
ORDER BY Name, split_age DESC; -- Optional: Matches the sample output order

How This Works #

Let's break down the logic using your sample data:

• Base Case: For each row in Table A, we first create an initial row. If the age is ≥10, we set split_age to 10 and calculate remaining_age as age -10. If age <10, split_age is the original age and remaining_age is 0.
• Recursive Step: We keep generating new rows using the remaining_age from the previous iteration. Each time, we split off another 10 (if possible) and update the remaining age until it hits 0.
• Insert: Finally, we select all the generated split_age rows and insert them into Table B.

Example Walkthrough #

For row A (26, 12345):

1. Base case: split_age=10, remaining_age=16 → row added
2. Recursive iteration 1: split_age=10, remaining_age=6 → row added
3. Recursive iteration 2: split_age=6, remaining_age=0 → row added
   Result: 3 rows for A, matching your sample.

Notes #

• This works in most modern SQL databases (PostgreSQL, SQL Server, MySQL 8.0+, etc.) that support recursive CTEs.
• If you're working with an older database that doesn't support recursion, you could use a numbers table approach (pre-generate numbers from 1 to max possible age/10) to join against, but recursion is cleaner here.

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