select
question_id as survey_log
from surveylog
group by question_id
order by sum(action='answer')/sum(action='show') desc
limit 1;
很有意思的解法,sum中可以带过滤条件
也可以用sum(if(action = ‘answer’, 1, 0))
with t as (
select question_id, count(question_id) as question_count
from surveylog
where action = 'show'
group by question_id
), t1 as (
select question_id, count(answer_id) as answer_count
from surveylog
where action = 'answer'
group by question_id
)
select t.question_id as survey_log
from t join t1
on t.question_id = t1.question_id
order by t1.answer_count / t.question_count desc
limit 1
一开始写的苦逼写法