PostgreSQL-带连接的where子句的条件 [英] Postgresql - Condition on where clause with join

问题描述

我正在尝试为查询添加特定条件的联接:

I am trying to add a join for a particular condition to my query:

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

因此，如果value ='E'，则这些问题(7)中的50％应该来自一般"问题类别.

So, if value = 'E', 50% of these questions (7) should be from a 'general' question category.

类似"case when dl.value='E' then rn=4 (50% of 7) from question_category='general', 3 (7-4) else 7 end"

(我需要添加一个连接，例如INNER JOIN question_category qc ON qc.id = q.question_category_id)

(I need to add a join like INNER JOIN question_category qc ON qc.id = q.question_category_id)

对于其他值(M/H)，不应与question_category进行任何连接

For other values (M/H), there shouldn't be any join with question_category

请参阅问题以查看原始问题.

Refer question to see the original question.

更新:

我正在尝试做

select *                                                          
   from (
          select
                row_number() over (partition by dl.value order by random()) as rn,
                row_number() over (partition by dl.value, LOWER(qc.value) = LOWER('general') order by random()) as rnc,
                dl.value, qc.value as question_category,
                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
                question_category qc on qc.id = q.question_category_id
                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 rnc <= 4) or (value = 'E' and rn <= 3)or
        value = 'M' and rn <= 3 or
        value = 'H' and rn <= 2;

，但这将返回值='E'的额外行. (当value ='E'时，4个来自rnc，4个来自rn).我想念什么?

but this is returning extra rows for value ='E'. (4 from rnc and 4 from rn when value='E'). What am I missing?

推荐答案

我想出了办法，

以下是我的想法.

select *
                from (
                    select
                        row_number() over (partition by dl.value order by random()) as rn,
                        row_number() over (partition by dl.value, LOWER(qc.value) = LOWER('general') order by random()) as rnc,
                        row_number() over (partition by dl.value, LOWER(qc.value) != LOWER('general') order by random()) as rnq,
                        dl.value, qc.value as question_category,
                        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
                        question_category qc on qc.id = q.question_category_id
                        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 rnq <= 4 and LOWER(question_category) != LOWER('general')) or
                    (value = 'E' and rnc <= 3 and LOWER(question_category) = LOWER('general')) or
                    value = 'M' and rn <= 3 or
                    value = 'H' and rn <= 2

这篇关于PostgreSQL-带连接的where子句的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！