SQL - 9

13.当选者

需求：编写 sql 语句来找到当选者（CandidateId）的名字。

效果展示：

Name

B

建表语句：

Create table If Not Exists Candidate (id int, Name varchar(255));
Create table If Not Exists Vote (id int, CandidateId int);
Truncate table Candidate;
insert into Candidate (id, Name) values (1, 'A');
insert into Candidate (id, Name) values (2, 'B');
insert into Candidate (id, Name) values (3, 'C');
insert into Candidate (id, Name) values (4, 'D');
insert into Candidate (id, Name) values (5, 'E');
Truncate table Vote;
insert into Vote (id, CandidateId) values (1, 2);
insert into Vote (id, CandidateId) values (2, 4);
insert into Vote (id, CandidateId) values (3, 3);
insert into Vote (id, CandidateId) values (4, 2);
insert into Vote (id, CandidateId) values (5, 5);

最终SQL：

select 
	name as "name"
from 
	Candidate ca
join 
	(select
     	CandidateId
     from 
     	Vote
     group by 
     	CandidateId
     order by 
     	count(*) desc
     limit 1
    ) as winner
where ca.id =winer.CandidateId;    

14.最高回答率

需求：请编写SQL查询来找到具有最高回答率的问题。

效果展示：

survey_log

285

说明：从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。id 表示用户 id；action 有以下几种值："show"，"answer"，"skip"；当 action 值为 "answer" 时 answer_id 非空，而 action 值为 "show" 或者 "skip" 时 answer_id 为空；q_num 表示当前会话中问题的编号。

建表语句：

Create table If Not Exists survey_log (uid int, action varchar(255), question_id int, answer_id int, q_num int, timestamp int);
Truncate table survey_log;
insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 285, null, 1, 123);
insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'answer', 285, 124124, 1, 124);
insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'show', 369, null, 2, 125);
insert into survey_log (uid, action, question_id, answer_id, q_num, timestamp) values (5, 'skip', 369, null, 2, 126);

方法1：

select
	question_id as survey_log
from 
	(select
    	question_id,
     	sum(case when action = "answer" then 1 else 0 end) as num_answer,
     	sum(case when action = "show" then 1 else 0 end )as num_show
     from 
     	survey_log
     group by
     	question_id
    ) as tbkl
order by 
	(num_answer/num_show) desc 
limit 1;

方法2：

select 
	question_id as "survey_log"
from 
	survey_log
group by
	question_id 
order by
	count(answer_id) / count(if(action ='show',1,0)) desc
limit 1;