gpt4 book ai didi

mysql - 有条件地将字段添加到查询的输出

转载 作者:行者123 更新时间:2023-11-29 12:44:44 25 4
gpt4 key购买 nike

仅当 user_answer.user_id = 10 时才选择 user_answer.status

我使用此 SQL 查询从多个表返回结果(questionq_ttaguser_answer)

SQL:

select question.text,group_concat(tag.text), count(user_answer.question_id) as tt
from question
left join q_t on question.id = q_t.wall_id
left join user_answer on question.id = user_answer.question_id
left join tag on q_t.tag_id = tag.id
where question.id in (1000001,1000002,1000003,1000004,1000005)
group by question.text
order by field(question.id,1000001,1000002,1000003,1000004,1000005)

结果:

text                         text                  tt
where is England? Geography,Continent 33
how many ...? sport,Europe 2

我需要从 user_answer 表中添加新的选择 user_answer.status,并附带条件(仅适用于检索此选择):

select user_answer.status    
where user_answer.user_id = 10

如何添加这个条件?

谢谢

最佳答案

您可以使用 CASE WHEN 来执行此操作:

select 
question.text,group_concat(tag.text),
count(user_answer.question_id) as tt,
CASE WHEN user_answer.user_id = 10 THEN user_answer.status ELSE NULL END as status
from question
left join q_t on question.id = q_t.wall_id
left join user_answer on question.id = user_answer.question_id
left join tag on q_t.tag_id = tag.id
where question.id in (1000001,1000002,1000003,1000004,1000005)
group by
question.text,
CASE WHEN user_answer.user_id = 10 THEN user_answer.status ELSE NULL END
order by field(question.id,1000001,1000002,1000003,1000004,1000005);

如果您希望状态应用于所有问题,您可以使用 MAX:

select 
question.text,group_concat(tag.text),
count(user_answer.question_id) as tt,
MAX(CASE
WHEN user_answer.user_id = 10
THEN user_answer.status
ELSE NULL
END) as status
from question
left join q_t on question.id = q_t.wall_id
left join user_answer on question.id = user_answer.question_id
left join tag on q_t.tag_id = tag.id
where question.id in (1000001,1000002,1000003,1000004,1000005)
group by
question.text
order by field(question.id,1000001,1000002,1000003,1000004,1000005);

关于mysql - 有条件地将字段添加到查询的输出,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/25604168/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com