大家好,又见面了,我是你们的朋友全栈君。
一、分组统计每个班的分数前三名(等同于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
题目详见:https://leetcode.com/problems/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;
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/129291.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...