社区首页 >问答首页 >将字段有条件地添加到查询的输出

问将字段有条件地添加到查询的输出
EN

Stack Overflow用户

提问于 2014-09-01 02:50:02

回答 2查看 56关注 0票数 0

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

我使用这个SQL查询来返回多个表的结果(问题、q_t、标记、user_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表中添加新的select user_answer表，条件(仅适用于检索此选择)：

select user_answer.status    
where user_answer.user_id = 10

如何添加此条件？

谢谢,

mysql

sql

CDN&音视频通信出海专场

提供游戏出海、社交娱乐等方案，助力企业抢占出海市场

回答 2

Stack Overflow用户

回答已采纳

发布于 2014-09-01 03:19:28

在以下情况下，您可以这样做：

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

票数 1

Stack Overflow用户

发布于 2014-09-01 03:25:29

select question.text,group_concat(tag.text), count(user_answer.question_id) as tt
    ,if((user_answer.id=10),(select user_answer.status),(''))as status
    from question`enter code here`
    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)