問題描述

我必須根據(jù)難度級(jí)別從我的問題集中提取 12 個(gè)問題.以下是我寫的查詢.

I have to fetch 12 questions from my question set based on the difficulty level. Following is the query I have written.

(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);

問題是，每當(dāng)我運(yùn)行這個(gè)查詢時(shí)，每次它都會(huì)給我不同數(shù)量的結(jié)果，而不是靜態(tài)的 12.有時(shí)我得到 12，有時(shí) 10，有時(shí) 15.我希望輸出中有 12 行，而不是更少，不多.

The issue is that whenever I run this query, each time it gives me a different number of results instead of the static 12. Sometimes I get 12, sometimes 10, sometimes 15. I expect 12 rows in output, not less, not more.

這個(gè)查詢有什么問題?

推薦答案

使用窗口函數(shù)可以簡化為單個(gè)選擇:

It can be reduced to a single select by using a window function:

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

這篇關(guān)于聯(lián)合返回具有限制 postgresql 的不同輸出的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！