21-40

21. 寻找用户推荐人

需求：写一个查询语句，返回一个编号列表，列表中编号的推荐人的编号都 不是 2

展示效果：

CREATE TABLE IF NOT EXISTS 21_customer (id INT,name VARCHAR(25),referee_id INT);
Truncate table 21_customer;
insert into 21_customer (id, name, referee_id) values (1, 'Will', null);
insert into 21_customer (id, name, referee_id) values (2, 'Jane', null);
insert into 21_customer (id, name, referee_id) values (3, 'Alex', 2);
insert into 21_customer (id, name, referee_id) values (4, 'Bill', null);
insert into 21_customer (id, name, referee_id) values (5, 'Zack', 1);
insert into 21_customer (id, name, referee_id) values (6, 'Mark', 2);

最终SQL:

SELECT 
      name
FROM 
      21_customer
WHERE 
      referee_id <> 2 OR referee_id IS NULL;

22. 2016年的投资

需求：写一个查询语句，将 2016 年 (TIV_2016) 所有成功投资的金额加起来，保留 2 位小数。

展示效果：

CREATE TABLE IF NOT EXISTS 22_insurance (PID INTEGER(11), TIV_2015 NUMERIC(15,2), TIV_2016 NUMERIC(15,2), LAT NUMERIC(5,2), LON NUMERIC(5,2) );
Truncate table 22_insurance;
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (1, 10, 5, 10, 10);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (2, 20, 20, 20, 20);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (3, 10, 30, 20, 20);
insert into 22_insurance (PID, TIV_2015, TIV_2016, LAT, LON) values (4, 10, 40, 40, 40);

提示：

对于一个投保人，他在 2016 年成功投资的条件是：

他在 2015 年的投保额 (TIV_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
他所在的城市必须与其他投保人都不同（也就是说维度和经度不能跟其他任何一个投保人完全相同）。

就如最后一个投保人，第一个投保人同时满足两个条件：

1. 他在 2015 年的投保金额 TIV_2015 为 10 ，与第三个和第四个投保人在 2015 年的投保金额相同。
2. 他所在城市的经纬度是独一无二的。

第二个投保人两个条件都不满足。他在 2015 年的投资 TIV_2015 与其他任何投保人都不相同。
且他所在城市的经纬度与第三个投保人相同。基于同样的原因，第三个投保人投资失败。

最终SQL:

SELECT
    SUM(i.TIV_2016) AS TIV_2016
FROM
    22_insurance as i
WHERE
    i.TIV_2015 IN(
                          SELECT
                                TIV_2015
                          FROM
                                22_insurance
                          GROUP BY 
                                TIV_2015
                          HAVING 
                                COUNT(*) > 1
                          )
AND 
    CONCAT(LAT, LON) IN(
                          SELECT
                                CONCAT(LAT, LON)
                          FROM
                                22_insurance
                          GROUP BY 
                                LAT , LON
                          HAVING COUNT(*) = 1
    );

23. 订单最多的客户

需求：在表 orders 中找到订单数最多客户对应的 customer_number 。

展示效果：

Create table If Not Exists 23_orders (order_number int, customer_number int, order_date date, required_date date, shipped_date date, status char(15), comment char(200), key(order_number));
Truncate table 23_orders;
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (1, 1, '2017-04-09', '2017-04-13', '2017-04-12', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (2, 2, '2017-04-15', '2017-04-20', '2017-04-18', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (3, 3, '2017-04-16', '2017-04-25', '2017-04-20', 'Closed');
insert into 23_orders (order_number, customer_number, order_date, required_date, shipped_date, status) values (4, 3, '2017-04-18', '2017-04-28', '2017-04-25', 'Closed');

最终SQL:

SELECT
    customer_number
FROM
    23_orders
GROUP BY 
    customer_number
ORDER BY
    COUNT(*) DESC
LIMIT 1;

进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

24. 大的国家

需求：编写一个SQL查询，输出表中所有大国家的名称、人口和面积。

展示效果：

Create table If Not Exists 24_World (name varchar(255), continent varchar(255), area int, population int, gdp bigint);
Truncate table 24_world;
insert into 24_World (name, continent, area, population, gdp) values ('Afghanistan', 'Asia', 652230, 25500100, 20343000000);
insert into 24_World (name, continent, area, population, gdp) values ('Albania', 'Europe', 28748, 2831741, 12960000000);
insert into 24_World (name, continent, area, population, gdp) values ('Algeria', 'Africa', 2381741, 37100000, 188681000000);
insert into 24_World (name, continent, area, population, gdp) values ('Andorra', 'Europe', 468, 78115, 3712000000);
insert into 24_World (name, continent, area, population, gdp) values ('Angola', 'Africa', 1246700, 20609294, 100990000000);

说明：如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。

最终SQL:

-- 方法一：or
select 
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.area >3000000 or w.population >25000000
-- 方法二：union
select 
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.area>3000000
union
select
      w.name,
      w.population,
      w.area
from 
      24_World w
where 
      w.population>25000000

25. 超过五名学生的课

需求：编写一个 SQL 查询，列出所有超过或等于5名学生的课。

展示效果：

Create table If Not Exists 25_courses (student varchar(255), class varchar(255));
Truncate table 25_courses;
insert into 25_courses (student, class) values ('A', 'Math');
insert into 25_courses (student, class) values ('B', 'English');
insert into 25_courses (student, class) values ('C', 'Math');
insert into 25_courses (student, class) values ('D', 'Biology');
insert into 25_courses (student, class) values ('E', 'Math');
insert into 25_courses (student, class) values ('F', 'Computer');
insert into 25_courses (student, class) values ('G', 'Math');
insert into 25_courses (student, class) values ('H', 'Math');
insert into 25_courses (student, class) values ('I', 'Math');

最终SQL:

select
      class 
from 
      25_courses 
group by 
      class
having 
      count(distinct student)>=5 ;

26. 好友申请

需求一：写一个查询语句，求出好友申请的通过率，用 2 位小数表示。通过率由接受好友申请的数目除以申请总数。

展示效果：

Create table If Not Exists 26_friend_request ( sender_id INT NOT NULL, send_to_id INT NULL, request_date DATE NULL);
Create table If Not Exists 26_request_accepted ( requester_id INT NOT NULL, accepter_id INT NULL, accept_date DATE NULL);
Truncate table 26_friend_request;
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 2, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 3, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (1, 4, '2016/06/01');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (2, 3, '2016/06/02');
insert into 26_friend_request (sender_id, send_to_id, request_date) values (3, 4, '2016/06/09');
Truncate table 26_request_accepted;
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (1, 2, '2016/06/03');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (1, 3, '2016/06/08');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (2, 3, '2016/06/08');
insert into 26_request_accepted (requester_id, accepter_id, accept_date) values (3, 4, '2016/06/09');

注意:

    通过的好友申请不一定都在表 friend_request 中。在这种情况下，你只需要统计总的被通过的申请数（不管它们在不在原来的申请中），并将它除以申请总数，得到通过率<br />

一个好友申请发送者有可能会给接受者发几条好友申请，也有可能一个好友申请会被通过好几次。这种情况下，重复的好友申请只统计一次。
如果一个好友申请都没有，通过率为 0.00 。

解释： 总共有 5 个申请，其中 4 个是不重复且被通过的好友申请，所以成功率是 0.80 。

最终SQL:

select
      round(
            ifnull(
                   (select count(*) from (select distinct requester_id, accepter_id from 26_request_accepted) as A)
                   /
                   (select count(*) from (select distinct sender_id, send_to_id from 26_friend_request) as B)
            , 0)
      , 2) as accept_rate;

需求二：写一个查询语句，求出谁拥有最多的好友和他拥有的好友数目。

展示效果：

注意：

• 保证拥有最多好友数目的只有 1 个人。
• 好友申请只会被接受一次，所以不会有 requester_id 和 accepter_id 值都相同的重复记录。

最终SQL:

select 
     ids as id,
     cnt as num
from
    (select
           ids,
           count(*) as cnt
     from
           (select 
                  requester_id as ids 
            from
                  26_request_accepted
            union all
            select
                  accepter_id 
            from
                  26_request_accepted
            ) as tbl1
     group by ids
     ) as tbl2
order by 
     cnt desc
limit 1;

27. 体育馆人流量

需求：请编写一个查询语句，找出人流量的高峰期。高峰期定义，至少连续三行记录中的人流量不少于100。

展示效果：

Create table If Not Exists 27_stadium (id int, visit_date DATE NULL, people int);
Truncate table 27_stadium;
insert into 27_stadium (id, visit_date, people) values (1, '2017-01-01', 10);
insert into 27_stadium (id, visit_date, people) values (2, '2017-01-02', 109);
insert into 27_stadium (id, visit_date, people) values (3, '2017-01-03', 150);
insert into 27_stadium (id, visit_date, people) values (4, '2017-01-04', 99);
insert into 27_stadium (id, visit_date, people) values (5, '2017-01-05', 145);
insert into 27_stadium (id, visit_date, people) values (6, '2017-01-06', 1455);
insert into 27_stadium (id, visit_date, people) values (7, '2017-01-07', 199);
insert into 27_stadium (id, visit_date, people) values (8, '2017-01-08', 188);

最终SQL:

SELECT 
     distinct a.*
FROM 
     27_stadium as a,
     27_stadium as b,
     27_stadium as c
where
     ((a.id = b.id-1 and b.id+1 = c.id) or(a.id-1 = b.id and a.id+1 = c.id) or(a.id-1 = c.id and c.id-1 = b.id))
      and 
     (a.people>=100 and b.people>=100 and c.people>=100)
order by 
     a.id;

28. 连续空余座位

需求：编写一个 SQL 查询，获取所有空余座位，并将它们按照 seat_id 排序

展示效果：

Create table If Not Exists 28_cinema (seat_id int primary key auto_increment, free bool);
Truncate table 28_cinema;
insert into 28_cinema (seat_id, free) values (1, 1);
insert into 28_cinema (seat_id, free) values (2, 0);
insert into 28_cinema (seat_id, free) values (3, 1);
insert into 28_cinema (seat_id, free) values (4, 1);
insert into 28_cinema (seat_id, free) values (5, 1);

注意：

• seat_id 字段是一个自增的整数，free 字段是布尔类型（’1’ 表示空余， ‘0’ 表示已被占据）。
• 连续空余座位的定义是大于等于 2 个连续空余的座位。

最终SQL:

select 
     distinct a.seat_id
from 
     28_cinema a 
join 
     28_cinema b
on 
     abs(a.seat_id - b.seat_id) = 1 and a.free = true and b.free = true
order by 
     a.seat_id;

29. 销售员

需求：输出所有表 salesperson中，没有向公司 ‘RED’ 销售任何东西的销售员。

展示效果：

Create table If Not Exists 29_salesperson (sales_id int, name varchar(255), salary int,commission_rate int, hire_date varchar(255));
Create table If Not Exists 29_company (com_id int, name varchar(255), city varchar(255));
Create table If Not Exists 29_orders (order_id int, order_date varchar(255), com_id int, sales_id int, amount int);
Truncate table 29_salesperson;
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (1,'John',100000, 6, '4/1/2006');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (2,'Amy', 12000, 5, '5/1/2010');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (3,'Mark',65000, 12, '12/25/2008');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (4,'Pam', 25000, 25, '1/1/2005');
insert into 29_salesperson (sales_id, name, salary, commission_rate, hire_date) values (5,'Alex', 5000, 10, '2/3/2007');
Truncate table 29_company;
insert into 29_company (com_id, name, city) values (1, 'RED', 'Boston');
insert into 29_company (com_id, name, city) values (2, 'ORANGE', 'New York');
insert into 29_company (com_id, name, city) values (3, 'YELLOW', 'Boston');
insert into 29_company (com_id, name, city) values (4, 'GREEN', 'Austin');
Truncate table 29_orders;
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (1, '1/1/2014', 3, 4, 10000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (2, '2/1/2014', 4, 5, 5000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (3, '3/1/2014', 1, 1, 50000);
insert into 29_orders (order_id, order_date, com_id, sales_id, amount) values (4, '4/1/2014', 1, 4, 25000);

最终SQL:

SELECT
    s.name
FROM
    29_salesperson s
WHERE
    s.sales_id NOT IN (
                       SELECT
                             o.sales_id
                       FROM
                             29_orders o
                       LEFT JOIN
                             29_company c 
                       ON
                             o.com_id = c.com_id
                       WHERE
                             c.name = 'RED'
    );

30. 节点树

需求：写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。

表 tree，id 是树节点的编号， p_id 是它父节点的 id 。

树中每个节点属于以下三种类型之一：

    叶子：如果这个节点没有任何孩子节点。
    根：如果这个节点是整棵树的根，即没有父节点。
    内部节点：如果这个节点既不是叶子节点也不是根节点。

展示效果：

解释:

节点 1 是根节点，因为它的父节点是 NULL ，同时它有孩子节点 2 和 3 。

节点 2 是内部节点，因为它有父节点 1 ，也有孩子节点 4 和 5 。

节点 3, 4 和 5 都是叶子节点，因为它们都有父节点同时没有孩子节点。

Create table If Not Exists 30_tree (id int, p_id int);
Truncate table 30_tree;
insert into 30_tree (id, p_id) values (1, null);
insert into 30_tree (id, p_id) values (2, 1);
insert into 30_tree (id, p_id) values (3, 1);
insert into 30_tree (id, p_id) values (4, 2);
insert into 30_tree (id, p_id) values (5, 2);

最终SQL:

-- 方法一：
SELECT
    id, 'Root' AS Type
FROM
    30_tree
WHERE
    p_id IS NULL
UNION
SELECT
    id, 'Leaf' AS Type
FROM
    30_tree
WHERE
    id NOT IN (SELECT
                     DISTINCT p_id
               FROM
                     30_tree
               WHERE
                     p_id IS NOT NULL)
    AND p_id IS NOT NULL
UNION
SELECT
    id, 'Inner' AS Type
FROM
    30_tree
WHERE
    id IN (SELECT
                DISTINCT p_id
           FROM
                30_tree
           WHERE
                p_id IS NOT NULL)
    AND p_id IS NOT NULL
ORDER BY id;
-- 方法二：
SELECT
      id AS `Id`,
      CASE 
          WHEN t1.id = (SELECT t2.id FROM 30_tree as t2 WHERE t2.p_id IS NULL) THEN 'Root'
          WHEN t1.id IN (SELECT t3.p_id FROM 30_tree t3) THEN 'Inner'
          ELSE 'Leaf'
      END AS Type
FROM
      30_tree t1
ORDER BY `Id`;
-- 方法三：
SELECT
    t1.id,
    IF(ISNULL(t1.p_id),'Root', IF(t1.id IN (SELECT p_id FROM 30_tree), 'Inner','Leaf')) Type
FROM
    30_tree t1
ORDER BY t1.id;

31. 判断是否是三角形

需求：编写一个 SQL 查询，判断三条线段是否能形成一个三角形。

展示效果：

Create table If Not Exists 31_triangle (x int, y int, z int);
Truncate table 31_triangle;
insert into 31_triangle (x, y, z) values (13, 15, 30);
insert into 31_triangle (x, y, z) values (10, 20, 15);

最终SQL:

select
      x,
      y,
      z,
      if((x + y <= z or x + z <= y or y + z <= x), "No", "Yes") as triangle
from 31_triangle;

32. 平面上的最近距离

需求：写一个查询语句找到两点之间的最近距离，保留 2 位小数。

展示效果：

CREATE TABLE If Not Exists 32_point_2d (x INT NOT NULL, y INT NOT NULL);
Truncate table 32_point_2d;
insert into 32_point_2d (x, y) values (-1, -1);
insert into 32_point_2d (x, y) values (0, 0);
insert into 32_point_2d (x, y) values (-1, -2);

最终SQL:

-- 方法一：
SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))), 2) AS shortest
FROM
    32_point_2d p1
JOIN
    32_point_2d p2 
ON 
    p1.x != p2.x OR p1.y != p2.y;
-- 方法二：
SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))),2) AS shortest
FROM
    32_point_2d p1
JOIN
    32_point_2d p2 
ON (p1.x <= p2.x AND p1.y < p2.y) OR (p1.x <= p2.x AND p1.y > p2.y) OR (p1.x < p2.x AND p1.y = p2.y);

33. 直线上最近距离

需求：找到这些点中最近两个点之间的距离。

展示效果：

CREATE TABLE If Not Exists 33_point (x INT NOT NULL, UNIQUE INDEX x_UNIQUE (x ASC));
Truncate table 33_point;
insert into 33_point (x) values (-1);
insert into 33_point (x) values (0);
insert into 33_point (x) values (2);

最终SQL:

SELECT
    MIN(ABS(p1.x - p2.x)) AS shortest
FROM
    33_point p1
JOIN
    33_point p2 
ON p1.x != p2.x;

34. 二级关注者

需求：对每一个关注者(follower)，查询他的关注者数目。

展示效果：

Create table If Not Exists 34_follow (followee varchar(255), follower varchar(255));
Truncate table 34_follow;
insert into 34_follow (followee, follower) values ('A', 'B');
insert into 34_follow (followee, follower) values ('B', 'C');
insert into 34_follow (followee, follower) values ('B', 'D');
insert into 34_follow (followee, follower) values ('D', 'E');

解释：

以A为主体，A为被关注者，B为被关注者，求出关注B的关注者。这里需要注意，被关注者永远不会被他 / 她自己关注。
将结果按照字典序返回。

最终SQL:

select 
      followee as 'follower',
      count(distinct follower) as num
from
      34_follow
where
      followee in(select follower from 34_follow)
group by 1
order by 1;

说明：这里的group by 表示使用第一列作为分组依据，order by 同理。

35. 平均工资

需求：写一个查询语句，求出在每一个工资发放日，每个部门的平均工资与公司的平均工资的比较结果 （高 / 低 / 相同）

展示效果：

Create table If Not Exists 35_salary (id int, employee_id int, amount int, pay_date date);
Create table If Not Exists 35_employee (employee_id int, department_id int);
Truncate table 35_salary;
insert into 35_salary (id, employee_id, amount, pay_date) values (1, 1, 9000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (2, 2, 6000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (3, 3, 10000, '2017/03/31');
insert into 35_salary (id, employee_id, amount, pay_date) values (4, 1, 7000, '2017/02/28');
insert into 35_salary (id, employee_id, amount, pay_date) values (5, 2, 6000, '2017/02/28');
insert into 35_salary (id, employee_id, amount, pay_date) values (6, 3, 8000, '2017/02/28');
Truncate table 35_employee;
insert into 35_employee (employee_id, department_id) values (1, 1);
insert into 35_employee (employee_id, department_id) values (2, 2);
insert into 35_employee (employee_id, department_id) values (3, 2);

解释:

在三月，公司的平均工资是 (9000+6000+10000)/3 = 8333.33…

由于部门 1 里只有一个 employee_id 为 1 的员工，所以部门 1 的平均工资就是此人的工资 9000 。因为 9000 > 8333.33 ，所以比较结果是 ‘higher’。

第二个部门的平均工资为 employee_id 为 2 和 3 两个人的平均工资，为 (6000+10000)/2=8000 。因为 8000 < 8333.33 ，所以比较结果是 ‘lower’ 。

在二月用同样的公式求平均工资并比较，比较结果为 ‘same’ ，因为部门 1 和部门 2 的平均工资与公司的平均工资相同，都是 7000 。

最终SQL:

select 
      department_salary.pay_month,
      department_id,
      case
          when department_avg>company_avg then 'higher'
          when department_avg<company_avg then 'lower'
          else 'same'
      end as comparison
from
     (select
            department_id,
            avg(amount) as department_avg,
            date_format(pay_date, '%Y-%m') as pay_month
      from 
            35_salary as s1
      join
            35_employee as e1
      on  
            s1.employee_id = e1.employee_id
      group by
            department_id, pay_month
     ) as department_salary
join
    (select
           avg(amount) as company_avg,
           date_format(pay_date, '%Y-%m') as pay_month 
     from 
           35_salary
     group by
           date_format(pay_date, '%Y-%m')
     ) as company_salary
on 
     department_salary.pay_month = company_salary.pay_month;

36. 学生地理信息报告

需求：写一个查询语句实现对大洲（continent）列的 透视表 操作，使得每个学生按照姓名的字母顺序依次排列在对应的大洲下面。输出的标题应依次为美洲（America）、亚洲（Asia）和欧洲（Europe）。数据保证来自美洲的学生不少于来自亚洲或者欧洲的学生。

展示效果：

Create table If Not Exists 36_student (name varchar(50), continent varchar(7));
Truncate table 36_student;
insert into 36_student (name, continent) values ('Jane', 'America');
insert into 36_student (name, continent) values ('Pascal', 'Europe');
insert into 36_student (name, continent) values ('Xi', 'Asia');
insert into 36_student (name, continent) values ('Jack', 'America');

最终SQL:

-- 方法一
SELECT 
      MAX(if(A.continent = 'America',A.NAME,NULL)) AS `America`,
      MAX(if(A.continent = 'Asia',A.NAME,NULL)) AS `Asia`,
      MAX(if(A.continent = 'Europe',A.NAME,NULL)) AS `Europe`
FROM
     (SELECT 
             S1.continent,
             S1.NAME,
             S1.row_id,
             COUNT(*) AS `trank`
      FROM 
            (SELECT
                    S.*,
                    @row_id:=(@row_id + 1) AS `row_id`
             FROM
                    36_student AS S,
                    (SELECT @row_id:=0) AS T
             ) AS S1 
      JOIN 
            (SELECT
                    S.*,
                    @n_row_id:=(@n_row_id + 1) AS `n_row_id`
             FROM 
                    36_student AS S,
                    (SELECT @n_row_id:=0) AS T
             ) AS S2 
      ON 
            (S1.continent = S2.continent AND (S1.NAME > S2.NAME OR (S1.NAME = S2.NAME AND S1.row_id >= S2.n_row_id)))
      group BY
             S1.continent,S1.NAME,S1.row_id
      order BY
             S1.continent,S1.NAME
      ) AS A
GROUP BY 
      A.trank;
-- 方法二      
SELECT
    MAX(IF(continent = 'America', name, NULL)) America,
    MAX(IF(continent = 'Asia', name, NULL)) Asia,
    MAX(IF(continent = 'Europe', name, NULL)) Europe
FROM   
    (SELECT continent,name,ROW_NUMBER() OVER(PARTITION BY continent ORDER BY name) rn FROM 36_student) T
GROUP BY rn

37. 只出现一次的最大数字

需求：编写一个 SQL 查询，找到只出现过一次的数字中，最大的一个数字。如果没有只出现一次的数字，输出 null 。

展示效果：

Create table If Not Exists 37_my_numbers (num int);
Truncate table 37_my_numbers;
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (8);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (3);
insert into 37_my_numbers (num) values (1);
insert into 37_my_numbers (num) values (4);
insert into 37_my_numbers (num) values (5);
insert into 37_my_numbers (num) values (6);

最终SQL:

select 
      ifnull((SELECT 
                    num
              FROM 
                    37_my_numbers
              group by 
                    num
              having
                    count(*) = 1
              order by 
                    num desc
              limit 1), null) as num;

38. 有趣的电影

需求：编写一个 SQL 查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列

展示效果：

Create table If Not Exists 38_cinema (id int, movie varchar(255), description varchar(255), rating float(2, 1));
Truncate table 38_cinema;
insert into 38_cinema (id, movie, description, rating) values (1, 'War', 'great 3D', 8.9);
insert into 38_cinema (id, movie, description, rating) values (2, 'Science', 'fiction', 8.5);
insert into 38_cinema (id, movie, description, rating) values (3, 'irish', 'boring', 6.2);
insert into 38_cinema (id, movie, description, rating) values (4, 'Ice song', 'Fantacy', 8.6);
insert into 38_cinema (id, movie, description, rating) values (5, 'House card', 'Interesting', 9.1);

最终SQL:

select 
      id,
      movie,
      description,
      rating
from 
      38_cinema
where 
      mod(id, 2) = 1 and description != 'boring'
order by 
      rating DESC;

说明：mod 函数求余数

39. 换座位

需求：编写一个 SQL 查询，对相邻的两位同学进行位置交换（例如：id=1 和id=2 的进行交换，id=3 和id=4 的进行交换）。如果学生人数是奇数，则不需要改变最后一个同学的座位。

展示效果：

Create table If Not Exists 39_seat(id int, student varchar(255));
Truncate table 39_seat;
insert into 39_seat (id, student) values (1, 'Abbot');
insert into 39_seat (id, student) values (2, 'Doris');
insert into 39_seat (id, student) values (3, 'Emerson');
insert into 39_seat (id, student) values (4, 'Green');
insert into 39_seat (id, student) values (5, 'Jeames');

最终SQL:

-- 方法一
select 
      a.id,
      ifnull(b.student,a.student) as student 
from 
      39_seat as a 
left join
      39_seat as b 
on 
      (a.id%2=1 && a.id=b.id-1) || (a.id%2=0 && a.id=b.id+1) 
order by 
      a.id;
-- 方法二
select 
      if(id%2=0,id-1,if(id=cnt,id,id+1)) as id,
      student
from 
      (select 
             count(*) as cnt
       from 
             39_seat
      )as a,
      39_seat
order by id;
-- 方法三      
select 
      b.id,
      a.student
from 
      39_seat as a,
      39_seat as b,
      (select
             count(*) as cnt
       from
             39_seat
      ) as c 
where 
      b.id=1^(a.id-1)+1 || (c.cnt%2 && b.id=c.cnt && a.id=c.cnt);

40. 交换性别

需求：给定一个 salary 表，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

展示效果：

create table if not exists 40_salary(id int, name varchar(100), sex char(1), salary int);
Truncate table 40_salary;
insert into 40_salary (id, name, sex, salary) values (1, 'A', 'm', 2500);
insert into 40_salary (id, name, sex, salary) values (2, 'B', 'f', 1500);
insert into 40_salary (id, name, sex, salary) values (3, 'C', 'm', 5500);
insert into 40_salary (id, name, sex, salary) values (4, 'D', 'f', 500);

最终SQL:

UPDATE 40_salary
SET
    sex = CASE sex
               WHEN 'm' THEN 'f'
               ELSE 'm'
          END;