mysql - 联合返回不同的输出与限制 postgresql

Question

我必须根据难度级别从问题集中提取 12 个问题。以下是我编写的查询。

(SELECT q.question_text, q.option_a, q.option_b, q.option_c, q.option_d,
        q.correct_answer, q.image_link, q.question_type
        FROM questions_bank q
        JOIN sports_type st ON st.id = q.sports_type_id
        JOIN difficulty_level dl ON dl.id = q.difficulty_level_id
        WHERE st.game_type = LOWER('cricket') AND dl.value = 'E'
        ORDER BY random()
        LIMIT 7) 
UNION 
(SELECT q.question_text, q.option_a, q.option_b, q.option_c, q.option_d,
        q.correct_answer, q.image_link, q.question_type
        FROM questions_bank q
        JOIN sports_type st ON st.id = q.sports_type_id
        JOIN difficulty_level dl ON dl.id = q.difficulty_level_id
        WHERE st.game_type = LOWER('cricket') AND dl.value = 'M'
        ORDER BY random()
        LIMIT 4)
UNION 
(SELECT q.question_text, q.option_a, q.option_b, q.option_c, q.option_d, 
        q.correct_answer, q.image_link, q.question_type
        FROM questions_bank q
        JOIN sports_type st ON st.id = q.sports_type_id
        JOIN difficulty_level dl ON dl.id = q.difficulty_level_id
        WHERE st.game_type = LOWER('cricket') AND dl.value = 'H'
        ORDER BY random()
        LIMIT 1);

问题是每当我运行这个查询时，每次它都会给我不同数量的结果而不是静态的 12。有时我得到 12，有时 10，有时 15。我希望输出中有 12 行，不少于，不多。

这个查询有什么问题？

Answer 1

可以使用窗口函数将其简化为单选:

select *
from (
    select
        row_number() over (partition by dl.value order by random()) as rn,
        dl.value,
        q.question_text, q.option_a, q.option_b, q.option_c, q.option_d,
        q.correct_answer, q.image_link, q.question_type
    from
        questions_bank q
        inner join
        sports_type st on st.id = q.sports_type_id
        inner join
        difficulty_level dl on dl.id = q.difficulty_level_id
    where st.game_type = lower('cricket') and dl.value in ('E','M','H')
) s
where 
    value = 'E' and rn <= 7 or
    value = 'M' and rn <= 4 or
    value = 'H' and rn = 1