问题描述:

want to select user_answer.status only if user_answer.user_id = 10

I use this SQL query, to return results from multiple tables (question, q_t, tag, 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)

result:

text text tt

where is England? Geography,Continent 33

how many ...? sport,Europe 2

I need to add new select user_answer.status from user_answer table, with condition (only applied to retrieve this selection):

select user_answer.status

where user_answer.user_id = 10

how to add this condition?

thanks,

网友答案:

You can do this with 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);

If you want status to be applied to all questions you can use 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);
网友答案:
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)
相关阅读:
Top