多表查询sql语句面试题
⽐较复杂的sql⾯试题
⼀、分组统计每个班的分数前三名（等同于LeetCode：185. Department Top Three Salaries）表结构：
create table student(
id varchar(20),-- 编号
class varchar(20),-- 年级
score int-- 分数
);
insert student values('1','⼀年级',82);
insert student values('2','⼀年级',95);
insert student values('3','⼀年级',82);
insert student values('4','⼀年级',40);
insert student values('5','⼀年级',20);
insert student values('6','⼆年级',95);
insert student values('7','⼆年级',40);
insert student values('8','⼆年级',3);
insert student values('9','⼆年级',60);
insert student values('10','⼆年级',10);
insert student values('11','三年级',70);
insert student values('12','三年级',60);
insert student values('13','三年级',40);
insert student values('14','三年级',90);
sql：
select a.class,a.score
from student a
where (select count(*) from student where class=a.class and a.score<score)<3
order by a.class,a.score desc;
⼆、LeetCode：262. Trips and Users
sql：
SELECT Request_at Day,
ROUND(SUM(IF(Status = 'completed', 0, 1)) / COUNT(*), 2) 'Cancellation Rate' FROM  Trips t
LEFT  JOIN Users t1 ON t.Client_Id = t1.Users_Id
WHERE  t1.Banned = 'No' AND Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP  BY t.Request_at;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。