如何校验component关联产品的Sector一致性以确定component的Sector值

一、违规数据查询 #

执行如下SQL即可筛选出所有不符合规则的记录，用于现有数据校验：

WITH component_meta AS (
    SELECT 
        component,
        COUNT(DISTINCT ProductName) AS product_cnt,
        COUNT(DISTINCT Sector) AS sector_cnt,
        MAX(Sector) AS standard_sector
    FROM Product
    GROUP BY component
)
SELECT 
    p.*,
    cm.standard_sector AS expected_sector
FROM Product p
JOIN component_meta cm ON p.component = cm.component
WHERE 
    (cm.product_cnt = 1 OR cm.sector_cnt = 1)
    AND p.Sector <> cm.standard_sector;

逻辑说明：先统计每个component关联的去重产品数、去重Sector数，以及唯一对应的标准Sector值，再匹配原表找出满足触发校验条件但Sector填写错误的记录。

二、批量修正现有违规数据 #

如果需要直接修正表中不符合规则的数据，执行如下SQL即可：

WITH component_meta AS (
    SELECT 
        component,
        COUNT(DISTINCT ProductName) AS product_cnt,
        COUNT(DISTINCT Sector) AS sector_cnt,
        MAX(Sector) AS standard_sector
    FROM Product
    GROUP BY component
)
UPDATE Product p
JOIN component_meta cm ON p.component = cm.component
SET p.Sector = cm.standard_sector
WHERE (cm.product_cnt = 1 OR cm.sector_cnt = 1) AND p.Sector <> cm.standard_sector;

三、数据库层面强制约束实现 #

如果需要新增/修改数据时自动校验，阻止违规数据写入，可根据你使用的数据库类型选择对应方案：

1. PostgreSQL/Oracle等支持函数式CHECK约束的数据库 #

先创建校验函数，再给表加CHECK约束：

CREATE OR REPLACE FUNCTION check_component_sector(p_component varchar, p_sector varchar)
RETURNS boolean AS $$
DECLARE
    v_product_cnt int;
    v_sector_cnt int;
    v_standard_sector varchar;
BEGIN
    SELECT COUNT(DISTINCT ProductName), COUNT(DISTINCT Sector), MAX(Sector)
    INTO v_product_cnt, v_sector_cnt, v_standard_sector
    FROM Product
    WHERE component = p_component;
    -- 关联多个产品且跨多个Sector的component无需校验
    IF v_product_cnt > 1 AND v_sector_cnt > 1 THEN
        RETURN true;
    END IF;
    -- 符合校验触发条件的需保证Sector一致
    RETURN p_sector = v_standard_sector;
END;
$$ LANGUAGE plpgsql;

ALTER TABLE Product ADD CONSTRAINT chk_component_sector CHECK (check_component_sector(component, Sector));

2. MySQL等不支持函数式CHECK约束的数据库 #

用触发器实现写入/更新前校验：

DELIMITER //
-- 新增数据触发器
CREATE TRIGGER trg_before_insert_product BEFORE INSERT ON Product
FOR EACH ROW
BEGIN
    DECLARE v_product_cnt INT;
    DECLARE v_sector_cnt INT;
    DECLARE v_standard_sector VARCHAR(255);
    SELECT COUNT(DISTINCT ProductName), COUNT(DISTINCT Sector), MAX(Sector)
    INTO v_product_cnt, v_sector_cnt, v_standard_sector
    FROM Product
    WHERE component = NEW.component;
    IF (v_product_cnt = 1 OR v_sector_cnt = 1) AND NEW.Sector <> v_standard_sector THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'component所属Sector不符合规则';
    END IF;
END //

-- 更新数据触发器，逻辑和新增完全一致
CREATE TRIGGER trg_before_update_product BEFORE UPDATE ON Product
FOR EACH ROW
BEGIN
    DECLARE v_product_cnt INT;
    DECLARE v_sector_cnt INT;
    DECLARE v_standard_sector VARCHAR(255);
    SELECT COUNT(DISTINCT ProductName), COUNT(DISTINCT Sector), MAX(Sector)
    INTO v_product_cnt, v_sector_cnt, v_standard_sector
    FROM Product
    WHERE component = NEW.component;
    IF (v_product_cnt = 1 OR v_sector_cnt = 1) AND NEW.Sector <> v_standard_sector THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'component所属Sector不符合规则';
    END IF;
END //
DELIMITER ;

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