SQL查询求助：如何按指定数量筛选各color_id的特定action_code_is值

你的原SQL存在两个核心问题：

1. 语法错误：WHERE action_code_is in(SELECT * FROM table1 WHERE action_code_is = 'pink' LIMIT 4) 里的子查询用SELECT *会返回所有列，和action_code_is（单个列）不匹配，直接导致SQL无法执行。
2. 逻辑错误：原代码是全局抽取各action_code_is类型的限定条数，再按color_id分区排名，这根本没法实现「每个color_id下分别取指定数量的各类型记录」的需求。

修正后的SQL代码 #

WITH filtered_records AS (
    SELECT 
        *,
        -- 按color_id和action_code_is分组，给每组记录随机编行号
        ROW_NUMBER() OVER (PARTITION BY color_id, action_code_is ORDER BY RAND(101)) AS rn
    FROM table1
    -- 只筛选需要的action类型，减少计算量
    WHERE action_code_is IN ('pink', 'yellow', 'blue', 'green')
)
SELECT *
FROM filtered_records
WHERE 
    (action_code_is = 'pink' AND rn <= 4)
    OR (action_code_is = 'yellow' AND rn <= 5)
    OR (action_code_is = 'blue' AND rn <= 6)
    OR (action_code_is = 'green' AND rn <= 3)

代码说明 #

• 用CTE（公共表表达式）给每个color_id下的每个action_code_is类型单独编行号，通过ORDER BY RAND(101)实现随机抽取（固定种子101可保证结果可重复）。
• 最后通过条件筛选，确保每个color_id下恰好有4条pink、5条yellow、6条blue、3条green，总和正好18条，完全匹配你的需求。

内容的提问来源于stack exchange，提问作者Gintarė Sunn