mysql带权重的随机算法

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    weight INT
);

-- 插入示例数据
INSERT INTO users (id, name, weight) VALUES
(1, 'Alice', 3),
(2, 'Bob', 1),
(3, 'Charlie', 2);

-- 带权重的随机选择
SELECT id, name, weight
FROM (
    SELECT id, name, weight,
           @rownum := @rownum + 1 AS rownum,
           @total_weight := SUM(weight) OVER () AS total_weight,
           @cumulative_weight := IFNULL(@cumulative_weight, 0) + weight AS cumulative_weight
    FROM users, (SELECT @rownum := 0, @cumulative_weight := 0) r
    ORDER BY RAND()
) t
WHERE RAND() * total_weight <= cumulative_weight
ORDER BY cumulative_weight DESC
LIMIT 1;