今日算法之_练习SQL
前言
Github:https://github.com/HealerJean
1、大的国家
1.1、题目
这里有张 World 表
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。
编写一个SQL查询,输出表中所有大国家的名称、人口和面积。
例如,根据上表,我们应该输出:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
1.2、数据准备
drop table if exists World;
create table World(
name varchar(32),
continent varchar(32),
area int,
population int,
gdp int
);
insert into World(name, continent, area, population, gdp) values ('Afghanistan', 'Asia', 652230, 25500100, 20343000);
insert into World(name, continent, area, population, gdp) values ('Albania', 'Europe', 28748, 2831741, 12960000);
insert into World(name, continent, area, population, gdp) values ('Algeria', 'Africa', 2381741, 37100000, 188681000);
insert into World(name, continent, area, population, gdp) values ('Algeria', 'Europe', 468, 78115, 3712000);
insert into World(name, continent, area, population, gdp) values ('Angola', 'Africa', 1246700, 20609294, 100990000);
select * from World;
1.2、答案
select name, population, area from World where area > 3000000 or population > 25000000;
2、第n高的薪水
2.1、题目
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
2.2、数据准备
drop table if exists employee;
create table employee
(
id int(11),
salary int(11)
)
select * from employee;
insert into employee (id, salary) values (1, 100);
insert into employee (id, salary) values (2, 200);
insert into employee (id, salary) values (3, 300);
2.3、答案
select distinct(a.salary)
from employee a
where (select count(distinct b.salary) from employee b where b.salary >= a.salary) = n
3、分数排名
3.1、题目
编写一个 SQL 查询来实现分数排名。
如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
3.2、数据准备
drop table if exists Scores ;
create table Scores
(
Id int(11) ,
Score decimal(16,2)
);
truncate Scores;
insert into Scores (Id, Score) values (1, 3.50);
insert into Scores (Id, Score) values (2, 3.65);
insert into Scores (Id, Score) values (3, 4.00);
insert into Scores (Id, Score) values (4, 3.85);
insert into Scores (Id, Score) values (5, 4.00);
insert into Scores (Id, Score) values (6, 3.65);
3.3、答案
3.3.1、答案1
解析:子查询查询大于A中等于(唯一,因为会有重复的分数比它大,重复的分数看做是一个排名)分数的的个数 也就是排名
select A.Score as score,
(select count(distinct B.Score) from Scores B where B.Score >= A.Score) as Rank
from Scores A
order by Score
desc;
3.3.2、答案2
SELECT
Score,
case
when @curScore = Score then @curRank
when @curScore := Score THEN @curRank := @curRank + 1
end as Rank
FROM
Scores,
(SELECT @curRank := 0 , @curScore) r
ORDER BY
Score DESC;
4、连续出现的数字
4.1、题目
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
4.2、数据准备
drop table if exists Logs ;
create table Logs
(
Id int(11) ,
Num int(11)
);
select * from Logs ;
insert into logs (Id, Num) values (1, 1);
insert into logs (Id, Num) values (2, 1);
insert into logs (Id, Num) values (3, 1);
insert into logs (Id, Num) values (4, 2);
insert into logs (Id, Num) values (5, 1);
insert into logs (Id, Num) values (6, 2);
insert into logs (Id, Num) values (7, 2);
4.3、答案
select distinct (a.Num) as ConsecutiveNums
from logs a
join logs b on a.Num = b.Num
join logs c on a.Num = c.Num
where a.Id = b.Id - 1
and b.Id = c.Id - 1;
5、超过经理收入的员工
5.1、题目
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
5.2、数据准备
drop table if exists Employee;
create table Employee
(
Id int(11),
Name varchar(20),
Salary decimal(16, 2),
ManagerId int(11)
)
5.3、答案
select e.Name as Employee
from Employee e join Employee m on e.ManagerId = m.Id
where e.Salary > m.Salary;
6、从不订购的客户
6.1、题目
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers 表:
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Orders 表:
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
例如给定上述表格,你的查询应返回:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
6.2、数据准备
drop table if exists Customers ;
create table Customers(
Id int(11),
Name varchar(20)
);
drop table if exists Orders ;
create table Orders(
Id int(11),
CustomerId int(11)
);
6.3、答案
select C.Name AS Customers
from Customers C
where C.Id not in (select distinct (O.CustomerId) from Orders O);
7、部门工资最高的员工
7.1、题目
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
7.2、数据准备
drop table if exists Employee;
create table Employee
(
Id int(11),
Name varchar(20),
Salary decimal(20, 0),
DepartmentId int(11)
);
drop table if exists Department;
create table Department
(
Id int(11),
Name varchar(20)
);
select * from Employee ;
insert into employee (Id, Name, Salary, DepartmentId) values (1, 'Joe', 70000, 1);
insert into employee (Id, Name, Salary, DepartmentId) values (2, 'Henry', 80000, 2);
insert into employee (Id, Name, Salary, DepartmentId) values (3, 'Sam', 60000, 2);
insert into employee (Id, Name, Salary, DepartmentId) values (4, 'Max', 90000, 1);
select * from Department ;
insert into department (Id, Name) values (1, 'IT');
insert into department (Id, Name) values (2, 'Sales');
7.3、答案
7.3.1、答案1
select D.Name as Department, A.Name as Employee, A.Salary
from Employee A
join Department D on A.DepartmentId = D.Id
join (select max(Salary) as MaxSalary, B.DepartmentId from Employee B group by DepartmentId) C
on C.DepartmentId = A.DepartmentId
where A.Salary = C.MaxSalary;
7.3.2、答案2
SELECT D.Name AS 'Department',
E.Name AS 'Employee',
E.Salary
FROM Employee E
JOIN
Department D ON E.DepartmentId = D.Id
WHERE (SELECT COUNT(DISTINCT A.Salary)
FROM Employee A
WHERE A.Salary > E.Salary
AND A.DepartmentId = E.DepartmentId
) < 1;
8、部门工资前三高的所有员工
8.1、题目
Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工。例如,根据上述给定的表,查询结果应返回:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
解释:
IT 部门中,Max 获得了最高的工资,Randy 和 Joe 都拿到了第二高的工资,Will 的工资排第三。销售部门(Sales)只有两名员工,Henry 的工资最高,Sam 的工资排第二。
8.2、数据准备
drop table if exists Employee;
create table Employee
(
Id int(11),
Name varchar(20),
Salary decimal(20, 0),
DepartmentId int(11)
);
drop table if exists Department;
create table Department
(
Id int(11),
Name varchar(20)
);
select * from Employee ;
insert into employee (Id, Name, Salary, DepartmentId) values (1, 'Joe', 85000, 1);
insert into employee (Id, Name, Salary, DepartmentId) values (2, 'Henry', 80000, 2);
insert into employee (Id, Name, Salary, DepartmentId) values (3, 'Sam', 60000, 2);
insert into employee (Id, Name, Salary, DepartmentId) values (4, 'Max', 90000, 1);
insert into employee (Id, Name, Salary, DepartmentId) values (5, 'Janet', 69000, 1);
insert into employee (Id, Name, Salary, DepartmentId) values (6, 'Randy', 85000, 1);
insert into employee (Id, Name, Salary, DepartmentId) values (7, 'Will', 70000, 1);
select * from Department ;
insert into department (Id, Name) values (1, 'IT');
insert into department (Id, Name) values (2, 'Sales');
8.3、答案
-- 解析:最重要的是后面的 < 3 注意里面的 DISTINCT(可能出现重复的人,所以用了)
select D.Name as Department, E.Name as Employee, E.Salary
from Employee E
join Department D on D.Id = E.DepartmentId
WHERE (
SELECT count(DISTINCT Salary)
from Employee A
where A.Salary > E.Salary
and A.DepartmentId = E.DepartmentId)
< 3
order by Department, Salary desc;
9、求男生的平均分大于女的班级
9.1、题目
9.2、数据准备
drop table if exists student;
create table student
(
Id int,
className varchar(32),
sex varchar(8),
score int
)
select * from student;
INSERT INTO student (Id, className, sex, score) VALUES (1, 'A', '男', 40);
INSERT INTO student (Id, className, sex, score) VALUES (2, 'A', '女', 36);
INSERT INTO student (Id, className, sex, score) VALUES (3, 'A', '男', 90);
INSERT INTO student (Id, className, sex, score) VALUES (4, 'A', '男', 77);
INSERT INTO student (Id, className, sex, score) VALUES (5, 'A', '女', 30);
INSERT INTO student (Id, className, sex, score) VALUES (6, 'B', '女', 35);
INSERT INTO student (Id, className, sex, score) VALUES (7, 'B', '女', 35);
INSERT INTO student (Id, className, sex, score) VALUES (8, 'C', '男', 90);
INSERT INTO student (Id, className, sex, score) VALUES (9, 'C', '女', 35);
9.3、答案
select className
from student
group by className
having avg(case sex when '男' then score end) > avg(case sex when '女' then score end);
10、求出不同班级中男生和女生的数量
10.1、题目
10.2、数据准备
drop table if exists student;
create table student
(
Id int,
className varchar(32),
sex varchar(8),
score int
)
select * from student;
INSERT INTO student (Id, className, sex, score) VALUES (1, 'A', '男', 40);
INSERT INTO student (Id, className, sex, score) VALUES (2, 'A', '女', 36);
INSERT INTO student (Id, className, sex, score) VALUES (3, 'A', '男', 90);
INSERT INTO student (Id, className, sex, score) VALUES (4, 'A', '男', 77);
INSERT INTO student (Id, className, sex, score) VALUES (5, 'A', '女', 40);
INSERT INTO student (Id, className, sex, score) VALUES (6, 'B', '女', 35);
INSERT INTO student (Id, className, sex, score) VALUES (7, 'B', '女', 35);
INSERT INTO student (Id, className, sex, score) VALUES (8, 'C', '男', 90);
INSERT INTO student (Id, className, sex, score) VALUES (9, 'C', '女', 35);
10.3、答案
select className,
sum(case when sex = '男' then 1 else 0 end) as numOfMan,
sum(case when sex = '女' then 1 else 0 end) as numOfWoman
from student
group by className;
11、交换工资
11.1、题目
给定一个 salary 表,如下所示,有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求只使用一个更新(Update)语句,并且没有中间的临时表。
注意,您必只能写一个 Update 语句,请不要编写任何 Select 语句。
例如
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后,将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
11.2、数据准备
drop table if exists salary;
create table salary(
id int,
name varchar(32),
sex varchar(8),
salary int
);
select * from salary;
INSERT INTO hlj_sql.salary (id, name, sex, salary) VALUES (1, 'A', 'm', 2500);
INSERT INTO hlj_sql.salary (id, name, sex, salary) VALUES (2, 'B', 'f', 1500);
INSERT INTO hlj_sql.salary (id, name, sex, salary) VALUES (3, 'C', 'm', 5500);
INSERT INTO hlj_sql.salary (id, name, sex, salary) VALUES (4, 'D', 'f', 500);
11.3、答案
update salary
set sex = case sex when 'f' then 'm' else 'f' end;
12、上升的温度
12.1、题目
给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
例如,根据上述给定的 Weather 表格,返回如下 Id:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
12.2、数据准备
drop table if exists Weather;
create table Weather
(
Id int,
RecordDate date,
Temperature int
);
select * from Weather;
INSERT INTO weather (Id, RecordDate, Temperature) VALUES (1, '2015-01-01', 10);
INSERT INTO weather (Id, RecordDate, Temperature) VALUES (2, '2015-01-02', 25);
INSERT INTO weather (Id, RecordDate, Temperature) VALUES (3, '2015-01-03', 20);
INSERT INTO weather (Id, RecordDate, Temperature) VALUES (4, '2015-01-04', 30);
select * from Weather;
12.3、答案
12.3.1、答案1
-- 解析、子查询 慢
select a.Id
from weather a
where (select b.Temperature from weather b where b.RecordDate = date_sub(a.RecordDate, INTERVAL 1 DAY))
< a.Temperature;
12.3.1、答案2
-- 解析、关联查询 快
select a.Id
from weather a
join weather b on b.RecordDate = date_sub(a.RecordDate, INTERVAL 1 DAY)
where a.Temperature > b.Temperature;
13、有趣的电影
13.1、题目
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+
对于上面的例子,则正确的输出是为:
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+
13.2、数据准备
drop table if exists cinema;
create table cinema(
id int,
movie varchar(32),
description varchar(32),
rating decimal(2,1)
)
select * from cinema;
INSERT INTO cinema (id, movie, description, rating) VALUES (1, 'War', 'great 3D', 8.9);
INSERT INTO cinema (id, movie, description, rating) VALUES (2, 'Science', 'fiction', 8.5);
INSERT INTO cinema (id, movie, description, rating) VALUES (3, 'irish', 'boring', 6.2);
INSERT INTO cinema (id, movie, description, rating) VALUES (4, 'Ice song', 'Fantacy', 8.6);
INSERT INTO cinema (id, movie, description, rating) VALUES (5, 'House card', 'Interesting', 9.1);
13.2、答案
select id, movie, description, rating from cinema where description != 'boring' and id % 2 != 0 order by rating desc ;
14、换座位
14.1、题目
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
14.2、数据准备
drop table if exists seat;
create table seat (
id int,
student varchar(32)
);
select * from seat;
INSERT INTO seat (id, student) VALUES (1, 'Abbot');
INSERT INTO seat (id, student) VALUES (2, 'Doris');
INSERT INTO seat (id, student) VALUES (3, 'Emerson');
INSERT INTO seat (id, student) VALUES (4, 'Green');
INSERT INTO seat (id, student) VALUES (5, 'Jeames');
14.3、答案
# 解析,if判断 select if( 1 > 0 ,1 ,0 ) ; 第二个if要判断末尾是存在 a.id + 1
select a.id,
if(a.id % 2 = 0, (select b.student from seat b where b.id = a.id - 1),
if((select d.id from seat d where d.id = a.id + 1) is not null,
(select c.student from seat c where c.id = a.id + 1),
a.student)) as student
from seat a;
15、体育馆的人流量
15.1、题目
X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于100。
例如,表 stadium:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
对于上面的示例数据,输出为:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
15.2、数据准备
drop table stadium;
create table stadium(
id int,
visit_date date,
people int
);
select * from stadium;
INSERT INTO stadium (id, visit_date, people) VALUES (1, '2017-01-01', 10);
INSERT INTO stadium (id, visit_date, people) VALUES (2, '2017-01-02', 109);
INSERT INTO stadium (id, visit_date, people) VALUES (3, '2017-01-03', 150);
INSERT INTO stadium (id, visit_date, people) VALUES (4, '2017-01-04', 99);
INSERT INTO stadium (id, visit_date, people) VALUES (5, '2017-01-05', 145);
INSERT INTO stadium (id, visit_date, people) VALUES (6, '2017-01-06', 1455);
INSERT INTO stadium (id, visit_date, people) VALUES (7, '2017-01-07', 199);
INSERT INTO stadium (id, visit_date, people) VALUES (8, '2017-01-08', 188);
15.3、答案
15.3.1、答案1
-- 解析:join方式
select * from
(select a.*
-- t1 t2 t3
from stadium a
join stadium b on b.id = a.id + 1
join stadium c on c.id = a.id + 2 where a.people >= 100 and b.people >= 100 and c.people >= 100
union
-- t2 t1 t3
select a.*
from stadium a
join stadium b on b.id = a.id - 1
join stadium c on c.id = a.id + 1 where a.people >= 100 and b.people >= 100 and c.people >= 100
union
-- t2 t3 t1
select a.*
from stadium a
join stadium b on b.id = a.id - 2
join stadium c on c.id = a.id - 1 where a.people >= 100 and b.people >= 100 and c.people >= 100) m
order by id
;
15.3.2、答案2
-- 解析:多个表同时查询方式
select distinct t1.*
from stadium t1
where t1.people >= 100
;
select distinct t1.*
from stadium t1,
stadium t2,
stadium t3
where t1.people >= 100
and t2.people >= 100
and t3.people >= 100
;
select distinct t1.*
from stadium t1,
stadium t2,
stadium t3
where t1.people >= 100
and t2.people >= 100
and t3.people >= 100
and (
(t1.id + 1 = t2.id and t2.id + 1 = t3.id) -- t1 t2 t3
or (t1.id - 1 = t2.id and t2.id + 2 = t3.id) -- t2 t1 t3
or (t1.id - 2 = t2.id and t2.id + 1 = t3.id) -- t2 t3 t1
) order by id
;
16、超过5名学生的课
16.1、题目
有一个courses 表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
应该输出:
+---------+
| class |
+---------+
| Math |
+---------+
Note: 学生在每个课中不应被重复计算。
16.2、数据准备
drop table if exists courses;
create table courses (
student varchar(32),
class varchar(32)
);
select * from courses;
insert into courses(student, class) values ('A', 'Math');
insert into courses(student, class) values ('B', 'English');
insert into courses(student, class) values ('C', 'Math');
insert into courses(student, class) values ('D', 'Biology');
insert into courses(student, class) values ('E', 'Math');
insert into courses(student, class) values ('F', 'Computer');
insert into courses(student, class) values ('G', 'Math');
insert into courses(student, class) values ('H', 'Math');
insert into courses(student, class) values ('I', 'Math');
16.3、答案
-- 解析,可能会有重复的 student 所以使用了 distinct
select class from courses group by class having count(distinct student) >= 5 ;
17、行程和用户
17.1、题目
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id,Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。
取消率的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
17.2、数据准备
# 2、数据准备
drop table if exists Trips;
create table Trips (
Id int,
Client_Id int,
Driver_Id int,
City_Id int,
Status varchar(32),
Request_at date
);
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (1, 1, 10, 1, 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (3, 3, 12, 6, 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (4, 4, 13, 6, 'cancelled_by_client', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (5, 1, 10, 1, 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (6, 2, 11, 6, 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (7, 3, 12, 6, 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (8, 2, 12, 12, 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (9, 3, 10, 12, 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at)values (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');
select * from Trips ;
drop table if exists Users;
create table Users (
Users_Id int,
Banned varchar(8),
Role varchar(8)
);
insert into Users(Users_Id, Banned, Role) VALUES (1 , 'No' ,'client');
insert into Users(Users_Id, Banned, Role) VALUES (2 , 'Yes' ,'client');
insert into Users(Users_Id, Banned, Role) VALUES (3 , 'No' ,'client');
insert into Users(Users_Id, Banned, Role) VALUES (4 , 'No' ,'client');
insert into Users(Users_Id, Banned, Role) VALUES (10 , 'No' ,'driver');
insert into Users(Users_Id, Banned, Role) VALUES (11 , 'No' ,'driver');
insert into Users(Users_Id, Banned, Role) VALUES (12 , 'No' ,'driver');
insert into Users(Users_Id, Banned, Role) VALUES (13 , 'No' ,'driver');
select * from Users;
17.3、答案
SELECT t.request_at as 'Day',
ROUND(
SUM(
IF((t.Status != 'completed'), 1, 0))
/ COUNT(t.Status),
2)
as 'Cancellation Rate'
FROM trips t
JOIN users u1 ON (t.client_id = u1.users_id AND u1.banned = 'No')
JOIN users u2 ON (t.driver_id = u2.users_id AND u2.banned = 'No')
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at ;
18、当选者
18.1、题目
表: Candidate
+-----+---------+
| id | Name |
+-----+---------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+-----+---------+
123456789
表: Vote ,id 是自动递增的主键,CandidateId 是 Candidate 表中的 id.
+-----+--------------+
| id | CandidateId |
+-----+--------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
+-----+--------------+
请编写 sql 语句来找到当选者的名字,上面的例子将返回当选者 B.
+------+
| Name |
+------+
| B |
+------+
注意:你可以假设没有平局,换言之,最多只有一位当选者。
18.2、数据准备
drop table if exists Candidate;
create table Candidate
(
id int,
Name varchar(32)
);
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');
select * from Candidate;;
drop table if exists Vote;
create table Vote
(
id int,
CandidateId int
);
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);
select * from Vote;;
18.3、答案
18.3.1、答案1
select c.Name
from Candidate c
join Vote v on c.id = v.CandidateId
group by c.Name
order by count(*) desc
limit 1;
18.3.2、答案2
select Name
from Candidate c
join (select v.CandidateId from Vote v group by v.CandidateId order by count(*) desc limit 1
) r on r.CandidateId = c.id;
19、 删除重复的电子邮箱
19.1、题目
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
提示:
执行 SQL 之后,输出是整个 Person 表。
使用 delete 语句。
19.2、数据准备
drop table if exists Person;
create table Person(
Id int,
Email varchar(64)
);
insert into Person(id, email) values (1, 'john@example.com');
insert into Person(id, email) values (2, 'bob@example.com');
insert into Person(id, email) values (3, 'john@example.com');
select * from Person;
19.3、答案
19.3.1、答案 1
-- 解析 土方法
delete p.*
from Person p
where (p.Id) not in (select id from (select min(m.id) as id from Person m group by m.Email) r);
19.3.2、答案 2
select p1.id
from Person p1
join Person p2 on p1.Email = p2.Email
where p1.id > p2.Id;
20、小众书籍
20.1、题目
书籍表 Books:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| book_id | int |
| name | varchar |
| available_from | date |
+----------------+---------+
book_id 是这个表的主键。
订单表 Orders:
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| order_id | int |
| book_id | int |
| quantity | int |
| dispatch_date | date |
+----------------+---------+
order_id 是这个表的主键。
book_id 是 Books 表的外键。
你需要写一段 SQL 命令,筛选出过去一年中订单总量 少于10本 的 书籍 。
注意:不考虑 上架(available from)距今 不满一个月 的书籍。并且 假设今天是 2019-06-23 。
下面是样例输出结果:
Books 表:
+---------+--------------------+----------------+
| book_id | name | available_from |
+---------+--------------------+----------------+
| 1 | "Kalila And Demna" | 2010-01-01 |
| 2 | "28 Letters" | 2012-05-12 |
| 3 | "The Hobbit" | 2019-06-10 |
| 4 | "13 Reasons Why" | 2019-06-01 |
| 5 | "The Hunger Games" | 2008-09-21 |
+---------+--------------------+----------------+
Orders 表:
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1 | 1 | 2 | 2018-07-26 |
| 2 | 1 | 1 | 2018-11-05 |
| 3 | 3 | 8 | 2019-06-11 |
| 4 | 4 | 6 | 2019-06-05 |
| 5 | 4 | 5 | 2019-06-20 |
| 6 | 5 | 9 | 2009-02-02 |
| 7 | 5 | 8 | 2010-04-13 |
+----------+---------+----------+---------------+
Result 表:
+-----------+--------------------+
| book_id | name |
+-----------+--------------------+
| 1 | "Kalila And Demna" |
| 2 | "28 Letters" |
| 5 | "The Hunger Games" |
+-----------+--------------------+
20.2、数据准备
drop table if exists Books;
create table Books(
book_id int,
name varchar(32),
available_from date
);
insert into Books(book_id, name, available_from) values (1, 'Kalila And Demna' ,'2010-01-01');
insert into Books(book_id, name, available_from) values (2, '28 Letters' ,'2012-05-12');
insert into Books(book_id, name, available_from) values (3, 'The Hobbit' ,'2019-06-10');
insert into Books(book_id, name, available_from) values (4, '13 Reasons Why' ,'2019-06-01');
insert into Books(book_id, name, available_from) values (5, 'The Hunger Games' ,'2008-09-21');
select * from Books;
drop table if exists Orders;
create table Orders(
order_id int,
book_id int,
quantity int,
dispatch_date date
);
insert into Orders(order_id, book_id, quantity, dispatch_date) values (1,1,2,'2018-07-26');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (2,1,1,'2018-11-05');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (3,3,8,'2019-06-11');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (4,4,6,'2019-06-05');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (5,4,5,'2019-06-20');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (6,5,9,'2009-02-02');
insert into Orders(order_id, book_id, quantity, dispatch_date) values (7,6,8,'2010-04-13');
select * from Orders;
20.3、答案
-- 1、先筛选符合基本条件的
select b.book_id, b.Name
from Books b
where b.available_from < date_sub('2019-06-23', INTERVAL 1 MONTH);
-- 2、根据书籍分组选择 订单量的 (注意反向思考,如果1年内没有订单将不会出现在订单表中,这样就很难join关联查)
select o.book_id
from Orders o
where o.dispatch_date > date_sub('2019-06-23', INTERVAL 1 YEAR)
group by o.book_id
having sum(quantity) >= 10;
-- 3、使用left join连接,因为可能会有一个订单都没有的书籍,所以使用left join
select b.book_id, b.Name
from Books b
where b.available_from < date_sub('2019-06-23', INTERVAL 1 MONTH)
and b.book_id not in (select o.book_id from Orders o where o.dispatch_date > date_sub('2019-06-23', INTERVAL 1 YEAR) group by o.book_id having sum(quantity) >= 10);
;
21、树节点
21.1、题目
给定一个表 tree,id 是树节点的编号, p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1 | null |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
+----+------+
树中每个节点属于以下三种类型之一:
叶子:如果这个节点没有任何孩子节点。
根:如果这个节点是整棵树的根,即没有父节点。
内部节点:如果这个节点既不是叶子节点也不是根节点。
写一个查询语句,输出所有节点的编号和节点的类型,并将结果按照节点编号排序。上面样例的结果为:
+----+------+
| id | Type |
+----+------+
| 1 | Root |
| 2 | Inner|
| 3 | Leaf |
| 4 | Leaf |
| 5 | Leaf |
+----+------+
节点 '1' 是根节点,因为它的父节点是 NULL ,同时它有孩子节点 '2' 和 '3' 。
节点 '2' 是内部节点,因为它有父节点 '1' ,也有孩子节点 '4' 和 '5' 。
节点 '3', '4' 和 '5' 都是叶子节点,因为它们都有父节点同时没有孩子节点。
21.2、数据准备
drop table if exists tree;
create table tree(
id int,
p_id int
);
INSERT INTO tree (id, p_id) VALUES (5, 2);
INSERT INTO tree (id, p_id) VALUES (4, 2);
INSERT INTO tree (id, p_id) VALUES (3, 1);
INSERT INTO tree (id, p_id) VALUES (2, 1);
INSERT INTO tree (id, p_id) VALUES (1, null);
select * from tree;
21.3、答案
select t.id,
case
when t.p_id is null then 'Root'
when t.id not in (select t2.p_id from tree t2 where t2.p_id is not null ) then 'Leaf'
else 'Inner' end as Type
from tree t order by t.id;
22、不同国家的天气类型
22.1、题目
国家表:Countries
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| country_id | int |
| country_name | varchar |
+---------------+---------+
country_id 是这张表的主键。 该表的每行有 country_id 和 country_name 两列。
天气表:Weather
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| country_id | int |
| weather_state | varchar |
| day | date |
+---------------+---------+
(country_id, day) 是该表的复合主键。
该表的每一行记录了某个国家某一天的天气情况。
写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。
天气类型的定义如下:当 weather_state 的平均值小于或等于15返回 Cold,当 weather_state 的平均值大于或等于 25 返回 Hot,否则返回 Warm。
你可以以任意顺序返回你的查询结果。
查询结果格式如下所示:
Countries table:
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 2 | USA |
| 3 | Australia |
| 7 | Peru |
| 5 | China |
| 8 | Morocco |
| 9 | Spain |
+------------+--------------+
Weather table:
+------------+---------------+------------+
| country_id | weather_state | day |
+------------+---------------+------------+
| 2 | 15 | 2019-11-01 |
| 2 | 12 | 2019-10-28 |
| 2 | 12 | 2019-10-27 |
| 3 | -2 | 2019-11-10 |
| 3 | 0 | 2019-11-11 |
| 3 | 3 | 2019-11-12 |
| 5 | 16 | 2019-11-07 |
| 5 | 18 | 2019-11-09 |
| 5 | 21 | 2019-11-23 |
| 7 | 25 | 2019-11-28 |
| 7 | 22 | 2019-12-01 |
| 7 | 20 | 2019-12-02 |
| 8 | 25 | 2019-11-05 |
| 8 | 27 | 2019-11-15 |
| 8 | 31 | 2019-11-25 |
| 9 | 7 | 2019-10-23 |
| 9 | 3 | 2019-12-23 |
+------------+---------------+------------+
Result table:
+--------------+--------------+
| country_name | weather_type |
+--------------+--------------+
| USA | Cold |
| Austraila | Cold |
| Peru | Hot |
| China | Warm |
| Morocco | Hot |
+--------------+--------------+
USA 11 月的平均 weather_state 为 (15) / 1 = 15 所以天气类型为 Cold。
Australia 11 月的平均 weather_state 为 (-2 + 0 + 3) / 3 = 0.333 所以天气类型为 Cold。
Peru 11 月的平均 weather_state 为 (25) / 1 = 25 所以天气类型为 Hot。
China 11 月的平均 weather_state 为 (16 + 18 + 21) / 3 = 18.333 所以天气类型为 Warm。
Morocco 11 月的平均 weather_state 为 (25 + 27 + 31) / 3 = 27.667 所以天气类型为 Hot。
我们并不知道 Spain 在 11 月的 weather_state 情况所以无需将他包含在结果中。
22.2、数据准备
drop table if exists Countries;
create table Countries
(
country_id int,
country_name varchar(32)
);
drop table if exists Weather;
create table Weather
(
country_id int,
weather_state varchar(32),
day date
);
select * from Countries;;
INSERT INTO countries (country_id, country_name) VALUES (2, 'USA');
INSERT INTO countries (country_id, country_name) VALUES (3, 'Australia');
INSERT INTO countries (country_id, country_name) VALUES (7, 'Peru');
INSERT INTO countries (country_id, country_name) VALUES (5, 'China');
INSERT INTO countries (country_id, country_name) VALUES (8, 'Morocco');
INSERT INTO countries (country_id, country_name) VALUES (9, 'Spain');
INSERT INTO weather (country_id, weather_state, day) VALUES (2, '15', '2019-11-01');
INSERT INTO weather (country_id, weather_state, day) VALUES (2, '12', '2019-10-28');
INSERT INTO weather (country_id, weather_state, day) VALUES (2, '12', '2019-10-27');
INSERT INTO weather (country_id, weather_state, day) VALUES (3, '-2', '2019-11-10');
INSERT INTO weather (country_id, weather_state, day) VALUES (3, '0', '2019-11-11');
INSERT INTO weather (country_id, weather_state, day) VALUES (3, '3', '2019-11-12');
INSERT INTO weather (country_id, weather_state, day) VALUES (5, '16', '2019-11-07');
INSERT INTO weather (country_id, weather_state, day) VALUES (5, '18', '2019-11-09');
INSERT INTO weather (country_id, weather_state, day) VALUES (5, '21', '2019-11-23');
INSERT INTO weather (country_id, weather_state, day) VALUES (7, '25', '2019-11-28');
INSERT INTO weather (country_id, weather_state, day) VALUES (7, '22', '2019-12-01');
INSERT INTO weather (country_id, weather_state, day) VALUES (7, '20', '2019-12-02');
INSERT INTO weather (country_id, weather_state, day) VALUES (8, '25', '2019-11-05');
INSERT INTO weather (country_id, weather_state, day) VALUES (8, '27', '2019-11-15');
INSERT INTO weather (country_id, weather_state, day) VALUES (8, '31', '2019-11-25');
INSERT INTO weather (country_id, weather_state, day) VALUES (9, '7', '2019-10-23');
INSERT INTO weather (country_id, weather_state, day) VALUES (9, '3', '2019-12-23');
select * from Weather;;
22.3、答案
-- 1、普通条件筛选
select c.country_name from Countries c ;
-- 2.1、条件判断,找出11月的数据
select w.* from Weather w where date_format(w.day, '%Y-%m') = '2019-11' ;
-- 2.2、根据国家分组
select w.country_id, avg(weather_state) from Weather w where date_format(w.day, '%Y-%m') = '2019-11' group by w.country_id ;
-- 3、 join 连接
select c.country_name,
case
when avgState <= 15 then 'Cold'
when avgState >= 25 then 'Hot'
else 'Warm'
end as weather_type
from Countries c
join (
select w.country_id, avg(weather_state) as avgState
from Weather w
where date_format(w.day, '%Y-%m') = '2019-11'
group by w.country_id
) m on c.country_id = m.country_id;
23、按年度列出销售总额
23.1、题目
Product 表:
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| product_name | varchar |
+---------------+---------+
product_id 是这张表的主键。
product_name 是产品的名称。
Sales 表:
+---------------------+---------+
| Column Name | Type |
+---------------------+---------+
| product_id | int |
| period_start | varchar |
| period_end | date |
| average_daily_sales | int |
+---------------------+---------+
product_id 是这张表的主键。
period_start 和 period_end 是该产品销售期的起始日期和结束日期,且这两个日期包含在销售期内。
average_daily_sales 列存储销售期内该产品的日平均销售额。
编写一段SQL查询每个产品每年的总销售额,并包含 product_id, product_name 以及 report_year 等信息。
销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按 product_id 和 report_year 排序。
查询结果格式如下例所示:
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 1 | LC Phone |
| 2 | LC T-Shirt |
| 3 | LC Keychain |
+------------+--------------+
Sales table:
+------------+--------------+-------------+---------------------+
| product_id | period_start | period_end | average_daily_sales |
+------------+--------------+-------------+---------------------+
| 1 | 2019-01-25 | 2019-02-28 | 100 |
| 2 | 2018-12-01 | 2020-01-01 | 10 |
| 3 | 2019-12-01 | 2020-01-31 | 1 |
+------------+--------------+-------------+---------------------+
Result table:
+------------+--------------+-------------+--------------+
| product_id | product_name | report_year | total_amount |
+------------+--------------+-------------+--------------+
| 1 | LC Phone | 2019 | 3500 |
| 2 | LC T-Shirt | 2018 | 310 |
| 2 | LC T-Shirt | 2019 | 3650 |
| 2 | LC T-Shirt | 2020 | 10 |
| 3 | LC Keychain | 2019 | 31 |
| 3 | LC Keychain | 2020 | 31 |
+------------+--------------+-------------+--------------+
LC Phone 在 2019-01-25 至 2019-02-28 期间销售,该产品销售时间总计35天。销售总额 35*100 = 3500。
LC T-shirt 在 2018-12-01 至 2020-01-01 期间销售,该产品在2018年、2019年、2020年的销售时间分别是31天、365天、1天,2018年、2019年、2020年的销售总额分别是31*10=310、365*10=3650、1*10=10。
LC Keychain 在 2019-12-01 至 2020-01-31 期间销售,该产品在2019年、2020年的销售时间分别是:31天、31天,2019年、2020年的销售总额分别是31*1=31、31*1=31。
23.2、数据准备
drop table if exists Product;
create table Product
(
product_id int,
product_name varchar(32)
);
drop table if exists Sales;
create table Sales
(
product_id int,
period_start varchar(32),
period_end date,
average_daily_sales int
);
INSERT INTO product (product_id, product_name) VALUES (1, 'LC Phone ');
INSERT INTO product (product_id, product_name) VALUES (2, 'LC T-Shirt');
INSERT INTO product (product_id, product_name) VALUES (3, 'LC Keychain');
select * from Product;
INSERT INTO sales (product_id, period_start, period_end, average_daily_sales) VALUES (1, '2019-01-25', '2019-02-28', 100);
INSERT INTO sales (product_id, period_start, period_end, average_daily_sales) VALUES (2, '2018-12-01', '2020-01-01', 10);
INSERT INTO sales (product_id, period_start, period_end, average_daily_sales) VALUES (3, '2019-12-01', '2020-01-31', 1);
select * from Sales;
23.2、答案
-- 1、简单查询
select p.product_id, p.product_name from Product p ;
-- 2、日销售额转年销售额
select s.product_id from Sales s;
select date('2020-01-01');
-- 3、如果起始日期小于等于2019-01-01, 此时如果截止日期比 2018-12-31大的话,则end就是 2018-12-31
(select Sales.product_id,
product_name,
'2018' as report_year,
if(period_start <= '2018-12-31', (datediff(if(period_end > '2018-12-31', '2018-12-31', period_end),
if(period_start < '2018-01-01', '2018-01-01' , period_start)) +
1) * average_daily_sales, 0) as total_amount
from Product
join Sales on Sales.product_id = Product.product_id
# # 防止计算后有负数以及不存在的请
having total_amount > 0
)
union
(
select Sales.product_id,
product_name,
'2019' as report_year,
if(period_start <= '2019-12-31', (datediff(if(period_end > '2019-12-31', '2019-12-31', period_end),
if(period_start < '2019-01-01', '2019-01-01', period_start)) +
1) * average_daily_sales, 0) as total_amount
from Product
join Sales on Sales.product_id = Product.product_id
having total_amount > 0
)
union
(
select Sales.product_id,
product_name,
'2020' as report_year,
if(period_start <= '2020-12-31', (datediff(if(period_end > '2020-12-31', '2020-12-31', period_end),
if(period_start < '2020-01-01','2020-01-01', period_start)) +
1) * average_daily_sales, 0) as total_amount
from Product
join Sales on Sales.product_id = Product.product_id
having total_amount > 0
)
order by product_id, report_year;
24、判断三角形
24.1、题目
一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。 然而,这个作业非常繁重,因为有几百组线段需要判断。
假设表 triangle 保存了所有三条线段的三元组 x, y, z ,你能帮 Tim 写一个查询语句,来判断每个三元组是否可以组成一个三角形吗?
| x | y | z |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |
对于如上样例数据,你的查询语句应该返回如下结果:
| x | y | z | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No |
| 10 | 20 | 15 | Yes |
24.2、数据准备
drop table if exists triangle;
create table triangle (
x int,
y int,
z int
);
INSERT INTO triangle (x, y, z) VALUES (13, 15, 30);
INSERT INTO triangle (x, y, z) VALUES (10, 20, 15);
select * from triangle;
24.3、答案
select x, y, z, if((x + y <= z || x + z <= y || z + y <= x ) , 'No','Yes') as triangle from triangle;
25、查询球队积分
25.1、题目
Table: Teams
+---------------+----------+
| Column Name | Type |
+---------------+----------+
| team_id | int |
| team_name | varchar |
+---------------+----------+
此表的主键是 team_id,表中的每一行都代表一支独立足球队。
Table: Matches
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| match_id | int |
| host_team | int |
| guest_team | int |
| host_goals | int |
| guest_goals | int |
+---------------+---------+
此表的主键是 match_id,表中的每一行都代表一场已结束的比赛,比赛的主客队分别由它们自己的 id 表示,他们的进球由 host_goals 和 guest_goals 分别表示。
积分规则如下:
赢一场得三分; 平一场得一分;输一场不得分。
写出一条SQL语句以查询每个队的 team_id,team_name 和 num_points。结果根据 num_points 降序排序,如果有两队积分相同,那么这两队按 team_id 升序排序。
查询结果格式如下:
Teams table:
+-----------+--------------+
| team_id | team_name |
+-----------+--------------+
| 10 | Leetcode FC |
| 20 | NewYork FC |
| 30 | Atlanta FC |
| 40 | Chicago FC |
| 50 | Toronto FC |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id | host_team | guest_team | host_goals | guest_goals |
+------------+--------------+---------------+-------------+--------------+
| 1 | 10 | 20 | 3 | 0 |
| 2 | 30 | 10 | 2 | 2 |
| 3 | 10 | 50 | 5 | 1 |
| 4 | 20 | 30 | 1 | 0 |
| 5 | 50 | 30 | 1 | 0 |
+------------+--------------+---------------+-------------+--------------+
Result table:
+------------+--------------+---------------+
| team_id | team_name | num_points |
+------------+--------------+---------------+
| 10 | Leetcode FC | 7 |
| 20 | NewYork FC | 3 |
| 50 | Toronto FC | 3 |
| 30 | Atlanta FC | 1 |
| 40 | Chicago FC | 0 |
+------------+--------------+---------------+
25.2、数据准备
drop table if exists Teams;
create table Teams
(
team_id int,
team_name varchar(32)
);
drop table if exists Matches;
create table Matches
(
match_id int,
host_team int,
guest_team int,
host_goals int,
guest_goals int
);
INSERT INTO teams (team_id, team_name) VALUES (10, 'Leetcode FC');
INSERT INTO teams (team_id, team_name) VALUES (20, 'NewYork FC');
INSERT INTO teams (team_id, team_name) VALUES (30, 'Atlanta FC');
INSERT INTO teams (team_id, team_name) VALUES (40, 'Chicago FC');
INSERT INTO teams (team_id, team_name) VALUES (50, 'Toronto FC');
select * from Teams;
INSERT INTO matches (match_id, host_team, guest_team, host_goals, guest_goals) VALUES (1, 10, 20, 30, 0);
INSERT INTO matches (match_id, host_team, guest_team, host_goals, guest_goals) VALUES (2, 30, 10, 2, 2);
INSERT INTO matches (match_id, host_team, guest_team, host_goals, guest_goals) VALUES (3, 10, 50, 5, 1);
INSERT INTO matches (match_id, host_team, guest_team, host_goals, guest_goals) VALUES (4, 20, 30, 1, 0);
INSERT INTO matches (match_id, host_team, guest_team, host_goals, guest_goals) VALUES (5, 50, 30, 1, 0);
select * from Matches;
25.3、答案
-- 1、根据比赛的球队分组,然后计算每个球队的得分
(select host_team as team_id,
sum(case
when (host_goals > guest_goals) then 3
when host_goals = guest_goals then 1
else 0 end) as score
from Matches
group by host_team)
union all
(select guest_team as team_id,
sum(case
when (host_goals > guest_goals) then 0
when host_goals = guest_goals then 1
else 3 end) as score
from Matches
group by guest_team);
-- 2、将关联起来的球队再进行分组,这个时候的分组,还是以球队进行分组的
select team_id, sum(score) as num_points
from (
(select host_team as team_id,
sum(case
when (host_goals > guest_goals) then 3
when host_goals = guest_goals then 1
else 0 end) as score
from Matches
group by host_team)
union all
(select guest_team as team_id,
sum(case
when (host_goals > guest_goals) then 0
when host_goals = guest_goals then 1
else 3 end) as score
from Matches
group by guest_team)
) m
group by team_id;
-- 3、left join 查询
select t.team_id, t.team_name, ifnull(num_points, 0) as num_points
from Teams t
left join (
select team_id, sum(score) as num_points
from (
(select host_team as team_id,
sum(case
when (host_goals > guest_goals) then 3
when host_goals = guest_goals then 1
else 0 end) as score
from Matches
group by host_team)
union all
(select guest_team as team_id,
sum(case
when (host_goals > guest_goals) then 0
when host_goals = guest_goals then 1
else 3 end) as score
from Matches
group by guest_team)
) m
group by team_id
) n on t.team_id = n.team_id
order by num_points desc, team_id
26、平面上的最近距离
26.1、题目
表 point_2d 保存了所有点(多于 2 个点)的坐标 (x,y) ,这些点在平面上两两不重合。
写一个查询语句找到两点之间的最近距离,保留 2 位小数。
| x | y |
|----|----|
| -1 | -1 |
| 0 | 0 |
| -1 | -2 |
最近距离在点 (-1,-1) 和(-1,2) 之间,距离为 1.00 。所以输出应该为:
| shortest |
|----------|
| 1.00 |
26.2、数据准备
drop table if exists point_2d ;
create table point_2d
(
x int,
y int
);
insert into point_2d (x, y) values (-1, -1);
insert into point_2d (x, y) values (0, 0);
insert into point_2d (x, y) values (-1, -2);
select * from point_2d;;
26.3、答案
# 3、答案
-- 1、准备关联数据(除了自己以外的其他点进行关联,因为没有id,所以只能使用 x y 任意不相等就成立)
select p1.x, p1.y, p2.x, p2.y from point_2d p1 join point_2d p2 on !(p1.x = p2.x and p1.y = p2.y);
-- 2、函数
-- power(value, 2); 平方 比如:select power(3,2); -> 9
-- sqrt(value) 开平方,比如:select sqrt(9); -> 3
select min(
round(
sqrt(power((p2.y - p1.y), 2) + power((p2.x - p1.x), 2)),
2)
) as shortest
from point_2d p1
join point_2d p2 on !(p1.x = p2.x and p1.y = p2.y);
27、二级关注者
27.1、题目
在 facebook 中,表 follow 会有 2 个字段: followee, follower ,分别表示被关注者和关注者。
请写一个 sql 查询语句,对每一个关注者,查询关注他的关注者的数目。
比方说:
+-------------+------------+
| followee | follower |
+-------------+------------+
| A | B |
| B | C |
| B | D |
| D | E |
+-------------+------------+
应该输出:
+-------------+------------+
| follower | num |
+-------------+------------+
| B | 2 |
| D | 1 |
+-------------+------------+
解释: B 和 D 都在在 follower 字段中出现,作为被关注者,B 被 C 和 D 关注,D 被 E 关注。A 不在 follower 字段内,所以A不在输出列表中。
注意: 被关注者永远不会被他 / 她自己关注。 将结果按照字典序返回。
27.1、数据准备
drop table if exists follow;
create table follow
(
followee varchar(8),
follower varchar(8)
);
INSERT INTO follow (followee, follower) VALUES ('A', 'B');
INSERT INTO follow (followee, follower) VALUES ('B', 'C');
INSERT INTO follow (followee, follower) VALUES ('B', 'D');
INSERT INTO follow (followee, follower) VALUES ('D', 'E');
select * from follow;
27.3、答案
# 3、答案
-- 1、根据关注人进行分组
select f1.followee, count(f1.followee) as num from follow f1 group by f1.followee
-- 2、必须使用join进行关联(由于解释中的说法)
select f2.follower, f.num
from follow f2
join (
select f1.followee, count(f1.followee) as num from follow f1 group by f1.followee
) f on f.followee = f2.follower
28、不同性别每日分数总计
28.1、题目
表: Scores
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| player_name | varchar |
| gender | varchar |
| day | date |
| score_points | int |
+---------------+---------+
(gender, day)是该表的主键
一场比赛是在女队和男队之间举行的
该表的每一行表示一个名叫 (player_name) 性别为 (gender) 的参赛者在某一天获得了 (score_points) 的分数
如果参赛者是女性,那么 gender 列为 'F',如果参赛者是男性,那么 gender 列为 'M'
写一条SQL语句查询每种性别在每一天的总分,并按性别和日期对查询结果排序
下面是查询结果格式的例子:
Scores表:
+-------------+--------+------------+--------------+
| player_name | gender | day | score_points |
+-------------+--------+------------+--------------+
| Aron | F | 2020-01-01 | 17 |
| Alice | F | 2020-01-07 | 23 |
| Bajrang | M | 2020-01-07 | 7 |
| Khali | M | 2019-12-25 | 11 |
| Slaman | M | 2019-12-30 | 13 |
| Joe | M | 2019-12-31 | 3 |
| Jose | M | 2019-12-18 | 2 |
| Priya | F | 2019-12-31 | 23 |
| Priyanka | F | 2019-12-30 | 17 |
+-------------+--------+------------+--------------+
结果表:
+--------+------------+-------+
| gender | day | total |
+--------+------------+-------+
| F | 2019-12-30 | 17 |
| F | 2019-12-31 | 40 |
| F | 2020-01-01 | 57 |
| F | 2020-01-07 | 80 |
| M | 2019-12-18 | 2 |
| M | 2019-12-25 | 13 |
| M | 2019-12-30 | 26 |
| M | 2019-12-31 | 29 |
| M | 2020-01-07 | 36 |
+--------+------------+-------+
女性队伍:
第一天是 2019-12-30,Priyanka 获得 17 分,队伍的总分是 17 分
第二天是 2019-12-31, Priya 获得 23 分,队伍的总分是 40 分
第三天是 2020-01-01, Aron 获得 17 分,队伍的总分是 57 分
第四天是 2020-01-07, Alice 获得 23 分,队伍的总分是 80 分
男性队伍:
第一天是 2019-12-18, Jose 获得 2 分,队伍的总分是 2 分
第二天是 2019-12-25, Khali 获得 11 分,队伍的总分是 13 分
第三天是 2019-12-30, Slaman 获得 13 分,队伍的总分是 26 分
第四天是 2019-12-31, Joe 获得 3 分,队伍的总分是 29 分
第五天是 2020-01-07, Bajrang 获得 7 分,队伍的总分是 36 分
28.2、数据准备
drop table if exists Scores;
create table Scores
(
player_name varchar(32),
gender varchar(1),
day date,
score_points int
);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Aron', 'F', '2020-01-01', 17);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Alice', 'F', '2020-01-07', 23);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Bajrang', 'M', '2020-01-07', 7);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Khali', 'M', '2019-12-25', 11);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Slaman', 'M', '2019-12-30', 13);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Joe', 'M', '2019-12-31', 3);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Jose', 'M', '2019-12-18', 2);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Priya', 'F', '2019-12-31', 23);
INSERT INTO scores (player_name, gender, day, score_points) VALUES ('Priyanka', 'F', '2019-12-30', 17);
select * from Scores;
28.3、答案
28.3.1、答案1
-- 3.1、答案1
-- 解析
--- 1、找出性别对应日期的顺序
select sum(s2.score_points)
from Scores s2
where s2.day <= '2020-01-07'
and s2.gender = 'F';
-- 2、条件查询
select s1.gender,
s1.day,
(select sum(s2.score_points)
from Scores s2
where s2.day <= s1.day
and s2.gender = s1.gender) as total
from Scores s1
order by s1.gender, s1.day;
28.3.2、答案2
-- 答案2
select s1.gender,
s1.day,
sum(s2.score_points) as total
from Scores s1
left join Scores s2 on s2.gender = s1.gender and s1.day >= s2.day
group by s1.day, s1.gender
order by s1.gender, s1.day;
;
29、顾客的可信联系人数量
29.1、题目
顾客表:Customers
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| customer_id | int |
| customer_name | varchar |
| email | varchar |
+---------------+---------+
customer_id 是这张表的主键。 此表的每一行包含了某在线商店顾客的姓名和电子邮件。
联系方式表:Contacts
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | id |
| contact_name | varchar |
| contact_email | varchar |
+---------------+---------+
(user_id, contact_email) 是这张表的主键。
此表的每一行表示编号为 user_id 的顾客的某位联系人的姓名和电子邮件。
此表包含每位顾客的联系人信息,但顾客的联系人不一定存在于顾客表中。
发票表:Invoices
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| invoice_id | int |
| price | int |
| user_id | int |
+--------------+---------+
invoice_id 是这张表的主键。
此表的每一行分别表示编号为 user_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。
为每张发票 invoice_id 编写一个SQL查询以查找以下内容:
customer_name:与发票相关的顾客名称。
price:发票的价格。
contacts_cnt:该顾客的联系人数量。
trusted_contacts_cnt:可信联系人的数量:既是该顾客的联系人又是商店顾客的联系人数量(即:可信联系人的电子邮件存在于客户表中)。
将查询的结果按照 invoice_id 排序。
查询结果的格式如下例所示:
Customers table:
+-------------+---------------+--------------------+
| customer_id | customer_name | email |
+-------------+---------------+--------------------+
| 1 | Alice | alice@leetcode.com |
| 2 | Bob | bob@leetcode.com |
| 13 | John | john@leetcode.com |
| 6 | Alex | alex@leetcode.com |
+-------------+---------------+--------------------+
Contacts table:
+-------------+--------------+--------------------+
| user_id | contact_name | contact_email |
+-------------+--------------+--------------------+
| 1 | Bob | bob@leetcode.com |
| 1 | John | john@leetcode.com |
| 1 | Jal | jal@leetcode.com |
| 2 | Omar | omar@leetcode.com |
| 2 | Meir | meir@leetcode.com |
| 6 | Alice | alice@leetcode.com |
+-------------+--------------+--------------------+
Invoices table:
+------------+-------+---------+
| invoice_id | price | user_id |
+------------+-------+---------+
| 77 | 100 | 1 |
| 88 | 200 | 1 |
| 99 | 300 | 2 |
| 66 | 400 | 2 |
| 55 | 500 | 13 |
| 44 | 60 | 6 |
+------------+-------+---------+
Result table:
+------------+---------------+-------+--------------+----------------------+
| invoice_id | customer_name | price | contacts_cnt | trusted_contacts_cnt |
+------------+---------------+-------+--------------+----------------------+
| 44 | Alex | 60 | 1 | 1 |
| 55 | John | 500 | 0 | 0 |
| 66 | Bob | 400 | 2 | 0 |
| 77 | Alice | 100 | 3 | 2 |
| 88 | Alice | 200 | 3 | 2 |
| 99 | Bob | 300 | 2 | 0 |
+------------+---------------+-------+--------------+----------------------+
Alice 有三位联系人,其中两位(Bob 和 John)是可信联系人。
Bob 有两位联系人, 他们中的任何一位都不是可信联系人。
Alex 只有一位联系人(Alice),并是一位可信联系人。
John 没有任何联系人。
29.2、数据准备
drop table if exists Customers;
create table Customers
(
customer_id int,
customer_name varchar(32),
email varchar(32)
);
drop table if exists Contacts;
create table Contacts
(
user_id int,
contact_name varchar(32),
contact_email varchar(32)
);
drop table if exists Invoices;
create table Invoices
(
invoice_id int,
price int,
user_id int
);
INSERT INTO customers (customer_id, customer_name, email) VALUES (1, 'Alice', 'alice@leetcode.com');
INSERT INTO customers (customer_id, customer_name, email) VALUES (2, 'Bob', 'bob@leetcode.com');
INSERT INTO customers (customer_id, customer_name, email) VALUES (13, 'John', 'john@leetcode.com');
INSERT INTO customers (customer_id, customer_name, email) VALUES (6, 'Alex', 'alex@leetcode.com');
select * from Customers;
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (1, 'Bob', 'bob@leetcode.com');
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (1, 'John', 'john@leetcode.com');
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (1, 'Jal', 'jal@leetcode.com');
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (2, 'Omar', 'omar@leetcode.com');
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (2, 'Meir', 'meir@leetcode.com');
INSERT INTO contacts (user_id, contact_name, contact_email) VALUES (6, 'Alice', 'alice@leetcode.com');
select * from Contacts;
INSERT INTO invoices (invoice_id, price, user_id) VALUES (77, 100, 1);
INSERT INTO invoices (invoice_id, price, user_id) VALUES (88, 200, 1);
INSERT INTO invoices (invoice_id, price, user_id) VALUES (99, 300, 2);
INSERT INTO invoices (invoice_id, price, user_id) VALUES (66, 400, 2);
INSERT INTO invoices (invoice_id, price, user_id) VALUES (55, 500, 13);
INSERT INTO invoices (invoice_id, price, user_id) VALUES (44, 60, 6);
select * from Invoices;
29.3、答案
29.3.1、答案1
select n1.invoice_id, cus.customer_name, n1.price
from Invoices n1
join Customers cus on cus.customer_id = n1.user_id
order by invoice_id;
-- 2、查询客户所有的联系人数量
select con1.user_id,
count(con1.user_id) as contacts_cnt
from Contacts con1 group by con1.user_id;
-- 3、查询客户可信联系人数量
select cus2.customer_id, count(con1.user_id) as trusted_contacts_cnt
from Contacts con1
right join Customers cus2 on cus2.customer_id = con1.user_id
group by cus2.customer_id;
-- 4、整合
select n1.invoice_id,
cus.customer_name,
n1.price,
ifnull(contacts_cnt, 0) as contacts_cnt,
ifnull(trusted_contacts_cnt, 0) as trusted_contacts_cnt
from Invoices n1
join Customers cus on cus.customer_id = n1.user_id
left join (select con1.user_id, count(con1.user_id) as contacts_cnt
from Contacts con1
group by con1.user_id) m on m.user_id = n1.user_id
left join (select con1.user_id, count(con1.user_id) as trusted_contacts_cnt
from Contacts con1
where con1.contact_name in (select cus3.customer_name from Customers cus3)
group by con1.user_id) n on n.user_id = n1.user_id
order by invoice_id
29.3.2、答案2
select i.invoice_id,
cus1.customer_name,
i.price,
count(con1.contact_name) contacts_cnt,
count(cus2.customer_name) trusted_contacts_cnt
from invoices i
join customers cus1 on i.user_id = cus1.customer_id
left join contacts con1 on i.user_id = con1.user_id
left join customers cus2 on con1.contact_name = cus2.customer_name
group by i.invoice_id,customer_name,price
order by i.invoice_id
30、分组排序
.1、题目
.2、数据准备
.3、答案
、
.1、题目
.2、数据准备
.3、答案