如何实现特定字段含斜杠时拆分记录并复制关联字段内容？

Hey Andrew, let's break down how to solve your serial number splitting and matching challenges in SQLite step by step:

Step 1: Split Slash-Delimited Serial Numbers in Table B #

SQLite doesn’t have a built-in string-splitting function, but we can use a recursive Common Table Expression (CTE) to split combined serials into individual rows while preserving all other field values. Here’s the code tailored to your [RFID TAG SCAN] table:

WITH split_serials AS (
    -- Base case: Extract the first serial before the first slash
    SELECT 
        Record#,
        MaterialNumber,
        Description,
        CASE 
            WHEN INSTR([SERIAL NUMBER], '/') > 0 THEN SUBSTR([SERIAL NUMBER], 1, INSTR([SERIAL NUMBER], '/') - 1)
            ELSE [SERIAL NUMBER]
        END AS split_serial,
        -- Capture remaining serials after the first slash
        CASE 
            WHEN INSTR([SERIAL NUMBER], '/') > 0 THEN SUBSTR([SERIAL NUMBER], INSTR([SERIAL NUMBER], '/') + 1)
            ELSE ''
        END AS remaining_serial
    FROM [RFID TAG SCAN]
    WHERE [SERIAL NUMBER] <> '' AND [SERIAL NUMBER] IS NOT NULL

    UNION ALL

    -- Recursive case: Keep splitting the remaining string until nothing's left
    SELECT 
        Record#,
        MaterialNumber,
        Description,
        CASE 
            WHEN INSTR(remaining_serial, '/') > 0 THEN SUBSTR(remaining_serial, 1, INSTR(remaining_serial, '/') - 1)
            ELSE remaining_serial
        END AS split_serial,
        CASE 
            WHEN INSTR(remaining_serial, '/') > 0 THEN SUBSTR(remaining_serial, INSTR(remaining_serial, '/') + 1)
            ELSE ''
        END AS remaining_serial
    FROM split_serials
    WHERE remaining_serial <> ''
)
-- Final output: Clean split rows without empty serials
SELECT 
    Record#,
    MaterialNumber,
    Description,
    split_serial AS [SERIAL NUMBER]
FROM split_serials
WHERE split_serial <> '';

This will turn your example row (Record#1, 156928, Truck, 5588/01234) into two separate rows:

• (Record#1, 156928, Truck, 5588)
• (Record#1, 156928, Truck, 01234)

Step 2: Join Split Serials with Your Missing Items List #

Now that we have individual serials, we can refine your original join query to reduce false matches. We’ll add optional filters to cut down on mismatches caused by length differences or typos:

WITH split_serials AS (
    -- Reuse the same split CTE from Step 1
    SELECT 
        Record#,
        MaterialNumber,
        Description,
        CASE 
            WHEN INSTR([SERIAL NUMBER], '/') > 0 THEN SUBSTR([SERIAL NUMBER], 1, INSTR([SERIAL NUMBER], '/') - 1)
            ELSE [SERIAL NUMBER]
        END AS split_serial,
        CASE 
            WHEN INSTR([SERIAL NUMBER], '/') > 0 THEN SUBSTR([SERIAL NUMBER], INSTR([SERIAL NUMBER], '/') + 1)
            ELSE ''
        END AS remaining_serial
    FROM [RFID TAG SCAN]
    WHERE [SERIAL NUMBER] <> '' AND [SERIAL NUMBER] IS NOT NULL

    UNION ALL

    SELECT 
        Record#,
        MaterialNumber,
        Description,
        CASE 
            WHEN INSTR(remaining_serial, '/') > 0 THEN SUBSTR(remaining_serial, 1, INSTR(remaining_serial, '/') - 1)
            ELSE remaining_serial
        END AS split_serial,
        CASE 
            WHEN INSTR(remaining_serial, '/') > 0 THEN SUBSTR(remaining_serial, INSTR(remaining_serial, '/') + 1)
            ELSE ''
        END AS remaining_serial
    FROM split_serials
    WHERE remaining_serial <> ''
)
-- Join with missing items using cleaned serials
SELECT 
    a.*,
    b.Record#,
    b.MaterialNumber,
    b.Description,
    b.split_serial AS matched_serial
FROM [MISSING ITEMS LIST] a
LEFT JOIN split_serials b 
    ON b.split_serial LIKE '%' || a.[SERIAL NUMBER] || '%'
    -- Optional filters to reduce false matches
    AND LENGTH(b.split_serial) BETWEEN LENGTH(a.[SERIAL NUMBER]) - 2 AND LENGTH(a.[SERIAL NUMBER]) + 2
WHERE a.[SERIAL NUMBER] <> '' AND a.[SERIAL NUMBER] IS NOT NULL;

Key Matching Improvements: #

• Individual Serial Comparison: No more matching against combined serial strings, which eliminates accidental partial matches across unrelated serials.
• Length Constraints: The LENGTH filter rules out matches where serials are drastically different in length, addressing a major source of your earlier Levenshtein-based false matches.
• Flexible Refinement: Adjust the LIKE condition (e.g., b.split_serial LIKE a.[SERIAL NUMBER] || '%' for prefix matches) if you know serials follow consistent formatting rules.

Step 3: Handling Unmatched Records #

For the 80% of records that still won’t match due to number errors or description inconsistencies, you can add a fuzzy matching layer using a custom Levenshtein function in SQLite. First, define the function:

CREATE FUNCTION levenshtein(s1 TEXT, s2 TEXT) 
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp INTEGER;
    DECLARE s1_char CHAR;
    SET s1_len = LENGTH(s1);
    SET s2_len = LENGTH(s2);
    IF s1_len = 0 THEN RETURN s2_len; END IF;
    IF s2_len = 0 THEN RETURN s1_len; END IF;
    
    -- Temp table to store distance calculations
    CREATE TEMP TABLE IF NOT EXISTS dist (i INTEGER, j INTEGER, d INTEGER);
    DELETE FROM dist;
    FOR i FROM 0 TO s1_len DO
        INSERT INTO dist VALUES(i, 0, i);
    END FOR;
    FOR j FROM 0 TO s2_len DO
        INSERT INTO dist VALUES(0, j, j);
    END FOR;
    
    -- Calculate edit distances
    FOR i FROM 1 TO s1_len DO
        SET s1_char = SUBSTR(s1, i, 1);
        FOR j FROM 1 TO s2_len DO
            SET c = CASE WHEN s1_char = SUBSTR(s2, j, 1) THEN 0 ELSE 1 END;
            SELECT MIN(d + c) INTO c_temp 
            FROM dist 
            WHERE (i = i-1 AND j = j) OR (i = i AND j = j-1) OR (i = i-1 AND j = j-1);
            INSERT INTO dist VALUES(i, j, c_temp);
        END FOR;
    END FOR;
    
    SELECT d INTO c FROM dist WHERE i = s1_len AND j = s2_len;
    DROP TABLE dist;
    RETURN c;
END;

Then add a fuzzy match condition to your join, allowing small typos (e.g., max 2 edits):

ON levenshtein(b.split_serial, a.[SERIAL NUMBER]) <= 2

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