如何编写SQL UPDATE语句按字母序更新汇总表颜色列

核心需求 #

更新Summary表，将每个车型对应的所有颜色按字母顺序拼接为逗号分隔的字符串，且需在Inventory表数据变更（插入、更新、删除）时自动执行。

分数据库实现方案 #

不同数据库的字符串聚合函数语法存在差异，以下是主流数据库的具体实现：

1. MySQL/MariaDB

基础更新语句

UPDATE Summary s
JOIN (
    SELECT 
        Car,
        GROUP_CONCAT(DISTINCT Color ORDER BY Color ASC SEPARATOR ', ') AS Colors
    FROM Inventory
    GROUP BY Car
) i ON s.Car = i.Car
SET s.Colors = i.Colors;

自动同步触发器（覆盖插入/更新/删除操作）

-- 处理INSERT操作的触发器
DELIMITER //
CREATE TRIGGER trg_inventory_after_insert
AFTER INSERT ON Inventory
FOR EACH ROW
BEGIN
    UPDATE Summary s
    JOIN (
        SELECT 
            Car,
            GROUP_CONCAT(DISTINCT Color ORDER BY Color ASC SEPARATOR ', ') AS Colors
        FROM Inventory
        WHERE Car = NEW.Car
        GROUP BY Car
    ) i ON s.Car = i.Car
    SET s.Colors = i.Colors;
END //
DELIMITER ;

-- 处理UPDATE操作的触发器
DELIMITER //
CREATE TRIGGER trg_inventory_after_update
AFTER UPDATE ON Inventory
FOR EACH ROW
BEGIN
    -- 同步旧车型与新车型的汇总数据（若车型字段被修改）
    UPDATE Summary s
    JOIN (
        SELECT 
            Car,
            GROUP_CONCAT(DISTINCT Color ORDER BY Color ASC SEPARATOR ', ') AS Colors
        FROM Inventory
        WHERE Car IN (OLD.Car, NEW.Car)
        GROUP BY Car
    ) i ON s.Car = i.Car
    SET s.Colors = i.Colors;
END //
DELIMITER ;

-- 处理DELETE操作的触发器
DELIMITER //
CREATE TRIGGER trg_inventory_after_delete
AFTER DELETE ON Inventory
FOR EACH ROW
BEGIN
    UPDATE Summary s
    JOIN (
        SELECT 
            Car,
            GROUP_CONCAT(DISTINCT Color ORDER BY Color ASC SEPARATOR ', ') AS Colors
        FROM Inventory
        WHERE Car = OLD.Car
        GROUP BY Car
    ) i ON s.Car = i.Car
    SET s.Colors = i.Colors;
END //
DELIMITER ;

2. SQL Server

基础更新语句

UPDATE s
SET s.Colors = i.Colors
FROM Summary s
JOIN (
    SELECT 
        Car,
        STRING_AGG(DISTINCT Color, ', ') WITHIN GROUP (ORDER BY Color ASC) AS Colors
    FROM Inventory
    GROUP BY Car
) i ON s.Car = i.Car;

自动同步触发器

CREATE TRIGGER trg_inventory_sync_summary
ON Inventory
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    UPDATE s
    SET s.Colors = i.Colors
    FROM Summary s
    JOIN (
        SELECT 
            Car,
            STRING_AGG(DISTINCT Color, ', ') WITHIN GROUP (ORDER BY Color ASC) AS Colors
        FROM Inventory
        WHERE Car IN (SELECT Car FROM INSERTED UNION SELECT Car FROM DELETED)
        GROUP BY Car
    ) i ON s.Car = i.Car;
END;

3. PostgreSQL

基础更新语句

UPDATE Summary s
SET Colors = i.Colors
FROM (
    SELECT 
        Car,
        STRING_AGG(DISTINCT Color, ', ' ORDER BY Color ASC) AS Colors
    FROM Inventory
    GROUP BY Car
) i
WHERE s.Car = i.Car;

自动同步触发器

-- 创建汇总同步函数
CREATE OR REPLACE FUNCTION sync_summary_colors()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE Summary s
    SET Colors = (
        SELECT STRING_AGG(DISTINCT Color, ', ' ORDER BY Color ASC)
        FROM Inventory
        WHERE Car = COALESCE(NEW.Car, OLD.Car)
    )
    WHERE s.Car = COALESCE(NEW.Car, OLD.Car);
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到Inventory表
CREATE TRIGGER trg_inventory_after_change
AFTER INSERT OR UPDATE OR DELETE ON Inventory
FOR EACH ROW
EXECUTE FUNCTION sync_summary_colors();

4. Oracle

基础更新语句（使用MERGE语法）

MERGE INTO Summary s
USING (
    SELECT 
        Car,
        LISTAGG(DISTINCT Color, ', ') WITHIN GROUP (ORDER BY Color ASC) AS Colors
    FROM Inventory
    GROUP BY Car
) i
ON (s.Car = i.Car)
WHEN MATCHED THEN
    UPDATE SET s.Colors = i.Colors;

自动同步触发器

CREATE OR REPLACE TRIGGER trg_inventory_sync_summary
AFTER INSERT OR UPDATE OR DELETE ON Inventory
FOR EACH ROW
DECLARE
    v_car VARCHAR2(100);
BEGIN
    v_car := COALESCE(:NEW.Car, :OLD.Car);
    
    MERGE INTO Summary s
    USING (
        SELECT 
            Car,
            LISTAGG(DISTINCT Color, ', ') WITHIN GROUP (ORDER BY Color ASC) AS Colors
        FROM Inventory
        WHERE Car = v_car
        GROUP BY Car
    ) i
    ON (s.Car = i.Car)
    WHEN MATCHED THEN
        UPDATE SET s.Colors = i.Colors;
END;
/

注意事项 #

• 使用DISTINCT避免同一车型的重复颜色出现在汇总字符串中
• 针对UPDATE操作的触发器需处理车型字段被修改的场景，确保旧/新车型的汇总数据都能同步更新
• 若Summary表存在Inventory中无对应记录的车型，可根据业务需求调整逻辑（如清空Colors字段或保留原有值）

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