如何按指定班次、Code次数及Rate总和要求筛选MySQL数据？

我理解你需要从给定的MySQL表中筛选出满足三个核心条件的记录：每个时段（Morning/Afternoon/Evening/Night）各2条、AA和BB记录各4条、rate总和小于100。下面提供两种可行的解决方案：

方案一：单次SQL查询（需手动重复执行直到得到结果） #

这个方案先为每个时段随机选取2条记录，再筛选出符合AA/BB数量和总和要求的结果。如果第一次查询返回空，只需重新执行即可（因为随机选取的结果不同）：

WITH selected_shifts AS (
    -- 每个时段随机取2条记录
    SELECT * FROM your_table_name WHERE shift='Morning' ORDER BY RAND() LIMIT 2
    UNION ALL
    SELECT * FROM your_table_name WHERE shift='Afternoon' ORDER BY RAND() LIMIT 2
    UNION ALL
    SELECT * FROM your_table_name WHERE shift='Evening' ORDER BY RAND() LIMIT 2
    UNION ALL
    SELECT * FROM your_table_name WHERE shift='Night' ORDER BY RAND() LIMIT 2
)
SELECT *
FROM selected_shifts
WHERE 
    -- 确保AA记录恰好4条
    (SELECT COUNT(*) FROM selected_shifts WHERE code='AA') = 4
    -- 确保rate总和小于100
    AND (SELECT SUM(rate) FROM selected_shifts) < 100;

说明： #

• ORDER BY RAND() 用于随机选取每个时段的记录；
• LIMIT 2 保证每个时段只选2条；
• 最后通过子查询筛选出符合AA数量和总和要求的结果集。

方案二：存储过程（自动重复尝试直到找到符合条件的结果） #

如果不想手动重复执行查询，可以创建一个存储过程，它会循环执行随机选取逻辑，直到找到满足所有条件的结果：

DELIMITER //
CREATE PROCEDURE FetchQualifiedRecords()
BEGIN
    DECLARE aa_count INT;
    DECLARE total_rate INT;
    DECLARE @done INT DEFAULT 0;

    -- 循环直到找到符合条件的结果
    REPEAT
        -- 计算当前选取结果的AA数量和rate总和
        SELECT COUNT(*) INTO aa_count FROM (
            SELECT * FROM your_table_name WHERE shift='Morning' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Afternoon' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Evening' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Night' ORDER BY RAND() LIMIT 2
        ) AS temp WHERE code='AA';
        
        SELECT SUM(rate) INTO total_rate FROM (
            SELECT * FROM your_table_name WHERE shift='Morning' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Afternoon' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Evening' ORDER BY RAND() LIMIT 2
            UNION ALL
            SELECT * FROM your_table_name WHERE shift='Night' ORDER BY RAND() LIMIT 2
        ) AS temp;
    UNTIL aa_count = 4 AND total_rate < 100 END REPEAT;

    -- 输出最终符合条件的结果
    SELECT * FROM (
        SELECT * FROM your_table_name WHERE shift='Morning' ORDER BY RAND() LIMIT 2
        UNION ALL
        SELECT * FROM your_table_name WHERE shift='Afternoon' ORDER BY RAND() LIMIT 2
        UNION ALL
        SELECT * FROM your_table_name WHERE shift='Evening' ORDER BY RAND() LIMIT 2
        UNION ALL
        SELECT * FROM your_table_name WHERE shift='Night' ORDER BY RAND() LIMIT 2
    ) AS final_result
    WHERE (SELECT COUNT(*) FROM final_result WHERE code='AA') = 4
      AND (SELECT SUM(rate) FROM final_result) < 100;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL FetchQualifiedRecords();

说明： #

• 存储过程会不断重复随机选取操作，直到找到满足所有条件的结果；
• 适用于需要自动获取符合条件结果的场景，无需手动重复执行查询。

验证可行性 #

从你提供的表数据来看，存在多个符合条件的组合。例如：

• Morning: AA(10)、BB(15)
• Afternoon: AA(25)、BB(2)
• Evening: AA(1)、BB(13)
• Night: AA(2)、BB(2)

这个组合的AA记录共4条，BB记录共4条，rate总和为 10+15+25+2+1+13+2+2=70，完全满足所有要求。

内容的提问来源于stack exchange，提问作者Okeke Chukwuemeka Godwin