前言

博客：http://blog.healerjean.com

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