精讲Mysql各种高难度Sql编写（三）

sql 继续更新，前面的六篇，老哥写的马马虎虎，不知道大家消化得怎么样了，如果感觉很难，千万不要气馁，因为人总是需要一个慢慢适应的过程的，多看几次就好了。

  考虑到要写的内容太多了，所以从这篇开始，5个sql查询，同时保证质量。

  接着，继续肝文，

sql1

这题是 Leetcode 数据库 620 题

题目:找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列

输出:

sql脚本：
DROP TABLE IF EXISTS cinema;
CREATE TABLE cinema (
id int(0) NOT NULL,
movie varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
rating varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO cinema VALUES (1, ‘War’, ‘great 3D’, ‘8.9’);
INSERT INTO cinema VALUES (2, ‘Science’, ‘fiction’, ‘8.5’);
INSERT INTO cinema VALUES (3, ‘irish’, ‘boring’, ‘6.2’);
INSERT INTO cinema VALUES (4, ‘Ice song’, ‘Fantacy’, ‘8.6’);
INSERT INTO cinema VALUES (5, ‘House card’, ‘Interesting’, ‘9.1’);

解法一、一看这个题的时候，感觉很难，但是做了之后发现好像挺简单的，哈哈哈

select * from cinema where description <> 'boring' and id % 2 =1 ORDER BY rating desc

sql2

这题是 Leetcode 数据库 627 题

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

sql脚本：
DROP TABLE IF EXISTS salary;
CREATE TABLE salary (
id int(0) NOT NULL,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
sex varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
salary int(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO salary VALUES (1, ‘A’, ‘m’, 2500);
INSERT INTO salary VALUES (2, ‘B’, ‘f’, 1500);
INSERT INTO salary VALUES (3, ‘C’, ‘m’, 5500);
INSERT INTO salary VALUES (4, ‘D’, ‘f’, 500);

解法一、老哥解法，使用 if 函数

update salary set sex = if(sex = 'f', 'm', 'f')

解法二、大神解法，使用case判断，我没想到的是mysql 也可以这么玩

update salary
set sex = case sex
                when 'f' then 'm'
                else  'f'
                end

解法三、官方解法，利用asci码解决问题

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

sql3

这题是 Leetcode 数据库 626 题

题目:小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

如果学生人数是奇数，则不需要改变最后一个同学的座位。

输出:

sql脚本:
DROP TABLE IF EXISTS seat;
CREATE TABLE seat (
id int(0) NULL DEFAULT NULL,
student varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO seat VALUES (1, ‘Abbot’);
INSERT INTO seat VALUES (2, ‘Doris’);
INSERT INTO seat VALUES (3, ‘Emerson’);
INSERT INTO seat VALUES (4, ‘Green’);
INSERT INTO seat VALUES (5, ‘Jeames’);

解法一、常规解法，老哥参考了一下其他大佬

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;	

解法二、官方解法，先拿到seat总数，然后利用case when 分情况讨论

select (case
      when mod(id,2)!=0 and id!=counts then id+1
      when mod(id,2)!=0 and id=counts then id
      else id-1 end)as id,student
      from seat,(select count(*)as counts from seat)as seat_counts
 order by id;

解法三、大神解法，用与1 异或，将奇数变偶数，偶数变奇数。又用-1对最后一行为奇数行的情况进行处理，只能说牛逼

select rank() over(order by (id-1)^1) as id,student from seat

sql4

这题是 Leetcode 数据库 1179 题

题目: 编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

说白了，就是竖着放变成横着放

输出:

sql脚本:
CREATE TABLE department2(
id INT,
revenue INT,
MONTH VARCHAR(10),
PRIMARY KEY(id, MONTH));

INSERT INTO department2 VALUE(1, 8000, ‘Jan’),(2, 9000, ‘Jan’),(3, 10000, ‘Feb’),(1, 7000, ‘Feb’),(1, 6000, ‘Mar’);

解法一、使用sum聚合函数，然后case分情况讨论,revenue 是该属性字段，then revenue 表示为null

SELECT id,
SUM(CASE `month` WHEN 'Jan' THEN revenue END) Jan_Revenue,
SUM(CASE `month` WHEN 'Feb' THEN revenue END) Feb_Revenue,
SUM(CASE `month` WHEN 'Mar' THEN revenue END) Mar_Revenue
FROM department2
GROUP BY id;

解法二、和上面一样，也是分情况讨论，差不多

SELECT id,
MAX(IF(`month`='Jan',revenue,NULL)) Jan_Revenue,
MAX(IF(`month`='Feb',revenue,NULL)) Feb_Revenue,
MAX(IF(`month`='Mar',revenue,NULL)) Mar_Revenue
FROM Department2
GROUP BY id;

sql5

这是Leetcode 数据库 512题 游戏分析II

题目:这个表显示的是某些游戏玩家的游戏活动情况
每一行是在某天使用某个设备登出之前登录并玩多个游戏（可能为0）的玩家的记录
请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称

输出:

sql脚本:
DROP TABLE IF EXISTS activity;
CREATE TABLE activity (
player_id int(0) NOT NULL,
device_id int(0) NULL DEFAULT NULL,
event_date date NOT NULL,
games_played int(0) NULL DEFAULT NULL,
PRIMARY KEY (player_id, event_date) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO activity VALUES (1, 2, ‘2016-03-01’, 5);
INSERT INTO activity VALUES (1, 2, ‘2016-05-02’, 6);
INSERT INTO activity VALUES (2, 3, ‘2017-06-25’, 1);
INSERT INTO activity VALUES (3, 1, ‘2016-03-02’, 0);
INSERT INTO activity VALUES (3, 4, ‘2018-07-03’, 5);

解法一、老哥写的，先拿到玩家id，最早时间，然后in两个，作条件过滤

select player_id,device_id from activity where (player_id,event_date) in(
select player_id,min(event_date) as late from activity GROUP BY player_id
)

解法二、内连接，和上面差不多

select a.player_id,a.device_id from activity a join 
(select player_id,min(event_date) as late from activity GROUP BY player_id)
b
on (a.player_id=b.player_id and a.event_date =b.late)

解法三、大佬写法,先对日期排序，拿到最早日期，然后子查询，秒秒秒！

SELECT 
  player_id,
  device_id 
FROM
  activity AS A 
WHERE event_date = 
  (SELECT 
    event_date 
  FROM
    activity 
  WHERE player_id = A.`player_id` 
  ORDER BY event_date 
  LIMIT 1) ;