PostgreSQL的 - 条件在where子句中使用联接
我想添加特定条件,我的查询联接:PostgreSQL的 - 条件在where子句中使用联接
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
所以,如果值=“E”,这些问题50%(7)应该来自“一般”问题类别。
像 “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
联接)
对于其他值(M/H),不应该有与question_category任何加入
参考question查看原始问题。
UPDATE:
我试图做的事:
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'。 (当值='E'时,来自rnc的4个和来自rn的4个)。我错过了什么?
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
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 IN ('E','M','H')
AND (dl.value = 'E' -- No extra condition for 'E'
OR EXISTS -- Extra condition for non-'E'
(SELECT * FROM question_category qc
WHERE qc.id = q.question_category_id
)
)
) s
WHERE value = 'E' AND rn <= 7
or value = 'M' AND rn <= 4
or value = 'H' AND rn = 1
;
我给了general
类别1
id。根据需要更换。
select *
from (
select
row_number() over (partition by dl.value order by random()) as rn,
row_number() over (
partition by dl.value, question_category_id = 1
order by random()
) as rnc,
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
inner join
question_category qc on qc.id = q.question_category_id
where st.game_type = lower('cricket') and dl.value in ('E','M','H')
) s
where
value = 'E' and rn <= 4 or
value = 'M' and rnc <= 4 or
value = 'H' and rn = 1
总共只有12个问题。但没有。 E中的问题需要包含一般类别的50%(E)。 –
@AnkitaGupta 7的50%是3.5。该怎么办? –
它会四舍五入到一个整数,所以,4。 –
我能想出的办法,
以下是我想出了。
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
当dl.value = 'E',RN应该是7只,但在这7,4个问题应该是从question_category = '一般' –
你应该把该进入正题。目前在问题中没有关于'question_category ='general''的任何信息。 – joop