Postgresql - where子句带联接的条件
Postgresql - where子句带联接的条件
提问于 2017-02-10 13:17:02
我试图为查询添加一个特定条件的联接：
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查看原始问题。
更新：
我想做的是：
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‘的额外行。(4来自rnc，4来自rn当值=‘E’)。我遗漏了什么？
回答 3
Stack Overflow用户
发布于 2017-02-10 14:10:45
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
;Stack Overflow用户
发布于 2017-02-10 15:57:40
我给了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 = 1Stack Overflow用户
发布于 2017-02-14 05:51:22
我能找到路，
以下是我想出的。
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42160359
复制相关文章
相似问题
腾讯云开发者
