多级分销数据库

CREATE TABLE distributors (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    parent_id INT,
    commission_rate DECIMAL(5, 2)
);

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    distributor_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (distributor_id) REFERENCES distributors(id)
);

DELIMITER //

CREATE PROCEDURE CalculateCommissions()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE d_id INT;
    DECLARE d_commission_rate DECIMAL(5, 2);
    DECLARE s_amount DECIMAL(10, 2);
    DECLARE cur_distributors CURSOR FOR SELECT id, commission_rate FROM distributors;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_distributors;

    read_loop: LOOP
        FETCH cur_distributors INTO d_id, d_commission_rate;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE distributors
        SET commission = commission + (
            SELECT SUM(s.amount * d_commission_rate)
            FROM sales s
            WHERE s.distributor_id = d_id
        )
        WHERE id = d_id;

        -- 递归计算上级分销商的佣金
        IF d_parent_id IS NOT NULL THEN
            CALL CalculateCommissionsForParent(d_parent_id);
        END IF;
    END LOOP;

    CLOSE cur_distributors;
END //

DELIMITER ;