如何在SQL中通过模式匹配创建‘父属性名称’新列？

Got it, let's work through adding that Parent Property Name column based on your hierarchical PropertyID structure. Here are practical solutions for common tools you might be using:

SQL Databases (MySQL, SQL Server, PostgreSQL) #

The core idea is to self-join your table: first extract the parent PropertyID from child records, then link back to the table to get the corresponding parent property name.

MySQL Example #

SELECT 
    t1.PropertyID,
    t1.`Property Name`,
    COALESCE(t2.`Property Name`, t1.`Property Name`) AS `Parent Property Name`
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON 
        CASE 
            WHEN LOCATE('-', t1.PropertyID) > 0 THEN SUBSTRING(t1.PropertyID, 1, LOCATE('-', t1.PropertyID) - 1)
            ELSE t1.PropertyID
        END = t2.PropertyID;

SQL Server Example #

SELECT 
    t1.PropertyID,
    t1.[Property Name],
    COALESCE(t2.[Property Name], t1.[Property Name]) AS [Parent Property Name]
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON 
        CASE 
            WHEN CHARINDEX('-', t1.PropertyID) > 0 THEN SUBSTRING(t1.PropertyID, 1, CHARINDEX('-', t1.PropertyID) - 1)
            ELSE t1.PropertyID
        END = t2.PropertyID;

PostgreSQL Example #

SELECT 
    t1.PropertyID,
    t1."Property Name",
    COALESCE(t2."Property Name", t1."Property Name") AS "Parent Property Name"
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON 
        CASE 
            WHEN STRPOS(t1.PropertyID, '-') > 0 THEN SUBSTRING(t1.PropertyID, 1, STRPOS(t1.PropertyID, '-') - 1)
            ELSE t1.PropertyID
        END = t2.PropertyID;

How it works:

1. We use string functions (LOCATE/CHARINDEX/STRPOS) to check for hyphens in PropertyID.
2. For child records (with hyphens), we extract the parent ID using SUBSTRING.
3. A left join links each record to its parent (or itself for parent records).
4. COALESCE ensures we fall back to the record's own name if it's a parent (though the join should always find a match for valid parent IDs).

Excel/Google Sheets #

If you're working with spreadsheet data, use this formula in the first row of your new Parent Property Name column (assuming PropertyID is in column A, Property Name in column B):

=IF(ISNUMBER(SEARCH("-",A2)),VLOOKUP(LEFT(A2,SEARCH("-",A2)-1),A:B,2,FALSE),B2)

How it works:

1. SEARCH("-",A2) checks if the PropertyID has a hyphen.
2. If yes: LEFT(A2,SEARCH("-",A2)-1) extracts the parent ID, then VLOOKUP finds the matching parent name in column B.
3. If no: We just use the record's own Property Name value.

Example Validation #

Using your sample data:

• For A002-01, the formula extracts A002, looks up Madison, and returns that as the parent name.
• For A001, since there's no hyphen, it returns Jefferson directly.

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