如何将Table1的一行记录拆分插入到Table2的多行中（动态适配）

Got it, let's tackle this problem of dynamically splitting the value field from Table1 into multiple rows for Table2, regardless of how many ||-separated segments there are. The core idea is to split the string into individual elements, pair up the corresponding Field/Value entries, and insert them efficiently—no hardcoding required.

Below are solutions for the most common databases, optimized for scalability with large datasets.

PostgreSQL Solution #

This uses string_to_array to split the string, unnest to expand into rows, and window functions to track element positions:

WITH split_values AS (
    SELECT
        t1.ID,
        t1.Date,
        t1.User,
        unnest(string_to_array(t1.value, '||')) AS element,
        row_number() OVER (PARTITION BY t1.ID) - 1 AS idx -- 0-based index
    FROM Table1 t1
),
total_counts AS (
    SELECT
        ID,
        COUNT(*) AS total_elements
    FROM split_values
    GROUP BY ID
    HAVING COUNT(*) % 2 = 0 -- Skip records with odd number of elements (adjust if needed)
),
field_elements AS (
    SELECT
        sv.ID,
        sv.Date,
        sv.User,
        sv.element AS Field,
        sv.idx
    FROM split_values sv
    JOIN total_counts tc ON sv.ID = tc.ID
    WHERE sv.idx < tc.total_elements/2
),
value_elements AS (
    SELECT
        sv.ID,
        sv.element AS Value,
        sv.idx - tc.total_elements/2 AS idx -- Align index with field elements
    FROM split_values sv
    JOIN total_counts tc ON sv.ID = tc.ID
    WHERE sv.idx >= tc.total_elements/2
)
INSERT INTO Table2 (ID, Date, User, Field, Value)
SELECT
    fe.ID,
    fe.Date,
    fe.User,
    fe.Field,
    ve.Value
FROM field_elements fe
JOIN value_elements ve ON fe.ID = ve.ID AND fe.idx = ve.idx;

SQL Server Solution #

SQL Server uses OPENJSON to split strings with index tracking (works on SQL Server 2016+):

WITH split_values AS (
    SELECT
        t1.ID,
        t1.Date,
        t1.[User],
        -- Convert to JSON array for OPENJSON parsing
        CAST('["' + REPLACE(t1.value, '||', '","') + '"]' AS NVARCHAR(MAX)) AS json_array
    FROM Table1 t1
),
json_elements AS (
    SELECT
        sv.ID,
        sv.Date,
        sv.[User],
        CAST(j.[key] AS INT) AS idx, -- 0-based index
        j.[value] AS element
    FROM split_values sv
    CROSS APPLY OPENJSON(sv.json_array) j
),
total_counts AS (
    SELECT
        ID,
        COUNT(*) AS total_elements
    FROM json_elements
    GROUP BY ID
    HAVING COUNT(*) % 2 = 0
),
field_elements AS (
    SELECT
        je.ID,
        je.Date,
        je.[User],
        je.element AS Field,
        je.idx
    FROM json_elements je
    JOIN total_counts tc ON je.ID = tc.ID
    WHERE je.idx < tc.total_elements/2
),
value_elements AS (
    SELECT
        je.ID,
        je.element AS Value,
        je.idx - tc.total_elements/2 AS idx
    FROM json_elements je
    JOIN total_counts tc ON je.ID = tc.ID
    WHERE je.idx >= tc.total_elements/2
)
INSERT INTO Table2 (ID, Date, [User], Field, Value)
SELECT
    fe.ID,
    fe.Date,
    fe.[User],
    fe.Field,
    ve.Value
FROM field_elements fe
JOIN value_elements ve ON fe.ID = ve.ID AND fe.idx = ve.idx;

MySQL 8.0+ Solution #

MySQL uses JSON_TABLE to split strings and generate indexed rows:

WITH split_values AS (
    SELECT
        t1.ID,
        t1.Date,
        t1.User,
        j.element,
        j.idx - 1 AS idx -- Convert to 0-based index
    FROM Table1 t1
    CROSS JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t1.value, '||', '","'), '"]'),
        '$[*]' COLUMNS (
            idx FOR ORDINALITY, -- 1-based index from JSON_TABLE
            element VARCHAR(255) PATH '$'
        )
    ) j
),
total_counts AS (
    SELECT
        ID,
        COUNT(*) AS total_elements
    FROM split_values
    GROUP BY ID
    HAVING COUNT(*) % 2 = 0
),
field_elements AS (
    SELECT
        sv.ID,
        sv.Date,
        sv.User,
        sv.element AS Field,
        sv.idx
    FROM split_values sv
    JOIN total_counts tc ON sv.ID = tc.ID
    WHERE sv.idx < tc.total_elements/2
),
value_elements AS (
    SELECT
        sv.ID,
        sv.element AS Value,
        sv.idx - tc.total_elements/2 AS idx
    FROM split_values sv
    JOIN total_counts tc ON sv.ID = tc.ID
    WHERE sv.idx >= tc.total_elements/2
)
INSERT INTO Table2 (ID, Date, User, Field, Value)
SELECT
    fe.ID,
    fe.Date,
    fe.User,
    fe.Field,
    ve.Value
FROM field_elements fe
JOIN value_elements ve ON fe.ID = ve.ID AND fe.idx = ve.idx;

Key Notes & Edge Cases #

• Even Element Validation: The solutions include a check to skip records with an odd number of segments (since we need equal Fields and Values). Adjust the HAVING clause if you need to handle odd counts differently (e.g., ignore the last segment).
• Performance: Using JOINs instead of correlated subqueries ensures better performance for large datasets. Make sure Table1's ID column is indexed to speed up partitioning and joins.
• P_ID Generation: If Table2's P_ID isn't an auto-incrementing primary key, add row_number() OVER (ORDER BY fe.ID, fe.idx) AS P_ID to the final SELECT to generate sequential global IDs.
• Data Types: Modify VARCHAR(255) and other data types to match your actual schema requirements.

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