基于查找表在SQL中替换列值：员工金属喜好表处理需求

Got it, let's tackle this column value replacement using a lookup table in SQL. First, let's align on the setup—since you didn't provide the lookup table structure, I'll start with a common, flexible lookup table design that fits your employee metal preference data.

First, create a lookup table to map raw values (like lowercase bronze or typos like business) to standardized values. This makes future updates way easier than hardcoding replacements.

-- Create the lookup table (adjust data types if needed for your database)
CREATE TABLE Metal_Lookup (
    Original_Value VARCHAR(50),
    Standard_Value VARCHAR(50)
);

-- Populate it with your desired mappings (customize this to your actual needs!)
INSERT INTO Metal_Lookup (Original_Value, Standard_Value)
VALUES
    ('bronze', 'Bronze'),
    ('platinum', 'Platinum'),
    ('copper', 'Copper'),
    ('Silver', 'Silver'), -- Fix inconsistent capitalization
    ('business', 'Steel'), -- Correct a typo example
    ('Gold', 'Gold'); -- Keep already standardized values as-is

If you just want to retrieve standardized data without modifying the original table, use one of these methods:

Option 1: CASE Expressions (Good for Small Mappings) #

This works well if you only have a few values to replace, but gets verbose with many mappings.

SELECT
    Employee_number,
    -- Use COALESCE to keep original values if no match exists
    COALESCE((SELECT Standard_Value FROM Metal_Lookup WHERE Original_Value = Rank_1), Rank_1) AS Rank_1,
    COALESCE((SELECT Standard_Value FROM Metal_Lookup WHERE Original_Value = Rank_2), Rank_2) AS Rank_2,
    COALESCE((SELECT Standard_Value FROM Metal_Lookup WHERE Original_Value = Rank_3), Rank_3) AS Rank_3,
    COALESCE((SELECT Standard_Value FROM Metal_Lookup WHERE Original_Value = Rank_4), Rank_4) AS Rank_4,
    COALESCE((SELECT Standard_Value FROM Metal_Lookup WHERE Original_Value = Rank_5), Rank_5) AS Rank_5
FROM Employee;

Option 2: LEFT JOINs (Better for Large Mappings) #

This is more scalable—add new mappings to the lookup table instead of rewriting your query.

SELECT
    e.Employee_number,
    COALESCE(m1.Standard_Value, e.Rank_1) AS Rank_1,
    COALESCE(m2.Standard_Value, e.Rank_2) AS Rank_2,
    COALESCE(m3.Standard_Value, e.Rank_3) AS Rank_3,
    COALESCE(m4.Standard_Value, e.Rank_4) AS Rank_4,
    COALESCE(m5.Standard_Value, e.Rank_5) AS Rank_5
FROM Employee e
-- Join the lookup table once per Rank column
LEFT JOIN Metal_Lookup m1 ON e.Rank_1 = m1.Original_Value
LEFT JOIN Metal_Lookup m2 ON e.Rank_2 = m2.Original_Value
LEFT JOIN Metal_Lookup m3 ON e.Rank_3 = m3.Original_Value
LEFT JOIN Metal_Lookup m4 ON e.Rank_4 = m4.Original_Value
LEFT JOIN Metal_Lookup m5 ON e.Rank_5 = m5.Original_Value;

If you want to permanently standardize the data in the Employee table, use an UPDATE statement. Here's the JOIN-based version (syntax varies slightly by database—this works for SQL Server; adjust for MySQL/PostgreSQL if needed):

UPDATE e
SET
    Rank_1 = COALESCE(m1.Standard_Value, e.Rank_1),
    Rank_2 = COALESCE(m2.Standard_Value, e.Rank_2),
    Rank_3 = COALESCE(m3.Standard_Value, e.Rank_3),
    Rank_4 = COALESCE(m4.Standard_Value, e.Rank_4),
    Rank_5 = COALESCE(m5.Standard_Value, e.Rank_5)
FROM Employee e
LEFT JOIN Metal_Lookup m1 ON e.Rank_1 = m1.Original_Value
LEFT JOIN Metal_Lookup m2 ON e.Rank_2 = m2.Original_Value
LEFT JOIN Metal_Lookup m3 ON e.Rank_3 = m3.Original_Value
LEFT JOIN Metal_Lookup m4 ON e.Rank_4 = m4.Original_Value
LEFT JOIN Metal_Lookup m5 ON e.Rank_5 = m5.Original_Value;

• Case Sensitivity: If your database distinguishes between uppercase and lowercase (e.g., PostgreSQL), use LOWER() to match values consistently: ON LOWER(e.Rank_1) = LOWER(m1.Original_Value)
• NULL Handling: All methods preserve NULL values (like employee 10's empty ranks) since COALESCE returns NULL when the original column is NULL.
• Extensibility: To add new replacements later, just insert a new row into Metal_Lookup—no need to rewrite your queries/updates.

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