题目难度
中等
题目描述
从 survey_log 表中获得回答率最高的问题,survey_log 表包含这些列:uid, action, question_id, answer_id, q_num, timestamp。
uid 表示用户 id;action 有以下几种值:“show”,“answer”,“skip”;当 action 值为 “answer” 时 answer_id 非空,而 action 值为 “show” 或者 “skip” 时 answer_id 为空;q_num 表示当前会话中问题的编号。
请编写SQL查询来找到具有最高回答率(answer个数/show个数)的问题。
正确答案
SELECT question_id AS survey_log
FROM
(select
question_id ,
sum(case when action='show' then 1 else 0 end ) as show_num,
sum(case when action='answer' then 1 else 0 end) as answer_num
from survey_log
group by s.question_id) AS a
ORDER BY answer_num/show_num DESC
LIMIT 1
这里有一点不懂的是,为什么ORDER BY answer_num/show_num放在内层a表中的时候是报错的??order by 应该是在select后的运算顺序啊。。