力扣Mysql练手

力扣Mysql练手

2,184 次访问
icefire
Scroll Down
小提示,本文编写于  1,663  天前，最后编辑于  1,822  天前，某些信息可能有些出入，仅供参考。

最近在刷LeetCode的SQL题练手，在此总结一下

175 组合两个表

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

请注意，你必须编写一个 Update 语句，不要编写任何 Select 语句。

例如:

idnamesexsalary
1Am2500
2Bf1500
3Cm5500
4Df500

运行你所编写的更新语句之后，将会得到以下表:

idnamesexsalary
1Af2500
2Bm1500
3Cf5500
4Dm500

MySQL脚本：

-- ----------------------------
-- Table structure for `salary`
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id`int(11) NOT NULL,
  `name`varchar(10) NOT NULL,
  `sex`varchar(10) NOT NULL,
 `salary` int(11) NOT NULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salary
-- ----------------------------
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 函数

IF(expr1, expr2, expr3) 如果 expr1 为 TRUE，则 IF () 的返回值为 expr2；否则返回值为 expr3。

# Write your MySQL query statement below
UPDATE salary
SET sex = IF(sex = "f","m","f");

方法二：使用 case…when..then..else..end

UPDATE salary 
SET sex  = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);

176 - 第二高的薪水

  • 题目描述
    编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。
IdSalary
1100
2200
3300

例如上述 Employee 表，SQL 查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

SecondHighestSalary
200

本题解法

方法 1 简便方法，倒序排序，limit(1,1)即可

SELECT IFNULL((SELECT DISTINCT(Salary) 
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1),null) AS SecondHighestSalary

方法 2
先查询出最高的身高值，然后查询身高小于该值的最高身高。

# Write your MySQL query statement below
SELECT Max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT Max(Salary) FROM Employee);

注：IFNULL() 方法介绍：

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。IFNULL() 返回一个数字或字符串值，取决于它被使用的上下文环境。

177 - 第 n 高的薪水

  • 题目描述
    编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。
IdSalary
1100
2200
3300

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

getNthHighestSalary(2)
200

MySQL 脚本

Create table If Not Exists Employee (Idint, Salary int);
Truncate table 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');

本题解法

查询条件：

  • 1）返回第 N 高的薪水
  • 2）如果不存在第 N 高的薪水，那么查询应返回 null
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET N
      )
  );
END

注意：LIMIT 子句后面不能做运算。

或者可以新定义一个变量 x：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE x int;
  SET x = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET x
      )
  );
END

178 - 分数排名

  • 题目描述
    编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有 “间隔”。
IdScore
13.50
23.65
34.00
43.85
54.00
63.65

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

ScoreRank
4.001
4.001
3.852
3.653
3.653
3.504

MySQL脚本

Create table If Not Exists Scores (Id int,Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1','3.5');
insert into Scores (Id, Score) values ('2','3.65');
insert into Scores (Id, Score) values ('3','4.0');
insert into Scores (Id, Score) values ('4','3.85');
insert into Scores (Id, Score) values ('5','4.0');
insert into Scores (Id, Score) values ('6','3.65');

本题解法：

  • 方法 1
    对于每一个分数，从表中找出有多少个大于或等于该分数的不重复分数，然后降序排列。
# Write your MySQL query statement below
SELECT Score,(
    SELECT COUNT(DISTINCT s.Score)
    FROM Scores s
    WHERE s.Score >= Scores.Score) AS Rank
FROM Scores
ORDER BY Score DESC;

这种方式虽然很简单，但是在 select 语句中使用子查询来生成 rank，相当于对每一条记录都需要查询一次 scores 表，查询次数为 （记录条数 ^2），会很慢。

  • 方法2
    在 select 语句中创建两个变量，其中 @PreScore 用来记录前一个分数，@Ranker 用来记录当前排名。然后判断当前的分数是否等于前面的分数 @PreScore，如果等于的话，排名值就为 @Ranker，否则，排名值为 @Ranker+1。

这里用了 CAST() 函数进行类型转换，其语法为：

CAST(字段名 as 转换的类型 )

其中类型可以为：

  • CHAR [(N)] 字符型
  • DATE 日期型
  • DATETIME 日期和时间型
  • DECIMAL float 型
  • SIGNED int
  • TIME 时间型
SELECT Score,CAST(
    CASE 
    WHEN @PreScore = Score THEN @Ranker
    WHEN @PreScore := Score THEN @Ranker := @Ranker + 1
    ELSE @Ranker := @Ranker + 1
  END AS SIGNED) AS Rank
FROM Scores a,(SELECT @PreScore := NULL, @Ranker := 0) r
ORDER BY Score DESC;

180 - 连续出现的数字

  • 题目描述
    编写一个 SQL 查询，查找所有至少连续出现三次的数字。
IdNum
11
21
31
42
51
62
72

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

ConsecutiveNums
1
  • MySQL 脚本
Create table If Not Exists Logs (Id int,Num int);
Truncate table 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');

本题解法：

由于需要找三次相同数字，所以我们需要建立三个表的实例，我们可以用 l1 分别和 l2, l3 内交，l1 和 l2 的 Id 下一个位置比，l1 和 l3 的下两个位置比，然后将 Num 都相同的数字返回。

注意： SELECT DISTINCT l1.Num AS ConsecutiveNums 保证当连续的数字大于 3 个的时候，返回的还是一个 Num。

# Write your MySQL query statement below
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1 
LEFT JOIN Logs l2
ON l1.Id = l2.Id - 1
LEFT JOIN Logs l3
ON l1.Id = l3.Id - 2
WHERE l1.Num = l2.Num AND l1.Num = l3.Num;

181 - 超过经理收入的员工

  • 题目描述
    Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
IdNameSalaryManagerId
1Joe700003
2Henry800004
3Sam60000NULL
4Max90000NULL

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

Employee
Joe
  • MySQL 脚本
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int);
Truncate table Employee;
insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3');
insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4');
insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', null);
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', null);

182 - 查找重复的电子邮箱

  • 题目描述
    编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
    示例：
IdEmail
1a@b.com
2c@d.com
3a@b.com

根据以上输入，你的查询应返回以下结果：

Email
a@b.com

说明：所有电子邮箱都是小写字母。

  • MySQL 脚本
Create table If Not Exists Person (Id int,Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1','a@b.com');
insert into Person (Id, Email) values ('2','c@d.com');
insert into Person (Id, Email) values ('3','a@b.com');

本题解法：

寻找重复的数据：用 group by Email 分组后 数据个数大于 1 的就是重复的。

# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING Count(*) >= 2;

183 - 从不订购的客户

  • 题目描述
    某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
IdName
1Joe
2Henry
3Sam
4Max

Orders 表：

IdCustomerId
13
21

例如给定上述表格，你的查询应返回：

Customers
Henry
Max
  • MySQL 脚本
Create table If Not Exists Customers (Idint, Name varchar(255));
Create table If Not Exists Orders (Id int,CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values('1', 'Joe');
insert into Customers (Id, Name) values('2', 'Henry');
insert into Customers (Id, Name) values('3', 'Sam');
insert into Customers (Id, Name) values('4', 'Max');
Truncate table Orders;
insert into Orders (Id, CustomerId) values('1', '3');
insert into Orders (Id, CustomerId) values('2', '1');

本题解法

  • 方法 1——LEFT JOIN
    直接让两个表左外连接，然后只要找出右边的 CustomerId 为 Null 的顾客就是没有下订单的顾客。
SELECT Customers.Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS null;
  • 方法2——NOT IN
SELECT c.Name AS Customers
FROM Customers AS c
WHERE c.Id NOT IN (
    SELECT DISTINCT CustomerId
    FROM Orders
);

184 - 部门最高工资

  • 题目描述
    Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
IdNameSalaryDepartmentId
1Joe700001
2Henry800002
3Sam600002
4Max900001

Department 表包含公司所有部门的信息。

IdName
1IT
2Sales

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

DepartmentEmployeeSalary
ITMax90000
SalesHenry80000
  • MySQL 脚本
Create table If Not Exists Employee (Idint, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Idint, Name varchar(255));
Truncate table 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');
Truncate table Department;
insert into Department (Id, Name) values('1', 'IT');
insert into Department (Id, Name) values('2', 'Sales');

本题解法

通过 GROUP BY 把每个部门的最高工资找出来，再通过联结表，找到相应的员工名字和部门名字。

# Write your MySQL query statement below
SELECT d.name AS Department, e.name AS Employee, e.Salary AS Salary
FROM Employee e,Department d
WHERE e.DepartmentId = d.Id 
AND ((e.DepartmentId,e.Salary) IN (SELECT DepartmentId,Max(Salary) AS MSalary
                                   FROM Employee
                                   GROUP BY DepartmentId));

185 - 部门工资前三高的员工

  • 题目描述
    Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。
IdNameSalaryDepartmentId
1Joe700001
2Henry800002
3Sam600002
4Max900001
5Janet690001
6Randy850001

Department 表包含公司所有部门的信息。

IdName
1IT
2Sales

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

DepartmentEmployeeSalary
ITMax90000
ITRandy85000
ITJoe70000
SalesHenry80000
SalesSam60000
  • MySQL 脚本
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table 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');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');

本题解法

  • 方法 1
SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary AS Salary
FROM Employee AS e1 INNER JOIN Department AS d
ON e1.DepartmentId = d.Id
WHERE (
    SELECT COUNT(DISTINCT e2.Salary)
    FROM Employee AS e2
    WHERE e2.DepartmentId = d.Id AND e2.Salary >= e1.Salary
      ) <= 3
ORDER BY e1.DepartmentId,e1.Salary DESC;

  • 方法2，一个注意点，部门薪水前三高包含了相同薪水下排名相同的意思

  • 查询出每个部门 薪水前三高 的员工。我们可以分解查询步骤，再组合。

  • 根据 部门 (升)，薪水 (降) 顺序查询出每个部门的员工 (Department, Employee, Salary)

SELECT dep.Name AS Department, emp.Name AS Employee, emp.Salary
FROM Employee AS emp INNER JOIN Department AS dep 
ON emp.DepartmentId = dep.Id
ORDER BY emp.DepartmentId, emp.Salary DESC
  • 每个部门的员工根据薪水进行排序
SELECT te.DepartmentId, te.Salary,
       CASE 
            WHEN @pre = DepartmentId THEN @rank:= @rank + 1
            WHEN @pre := DepartmentId THEN @rank:= 1
       END AS RANK
FROM (SELECT @pre:=null, @rank:=0)tt,
     (
         SELECT DepartmentId,Salary
         FROM Employee
         GROUP BY DepartmentId,Salary
         ORDER BY DepartmentId,Salary DESC
     ) te

得出

DepartmentIdSalaryRANK
1900001
1850002
1700003
1690004
2800001
2600002
  • 组合步骤,将每个步骤变成一个结果集（没有二次查询），再将所有步骤的 结果集进行关联，从而提高性能。
SELECT dep.Name Department, emp.Name Employee, emp.Salary
FROM (
        SELECT te.DepartmentId, te.Salary,
               CASE 
                    WHEN @pre = DepartmentId THEN @rank:= @rank + 1
                    WHEN @pre := DepartmentId THEN @rank:= 1
               END AS RANK
        FROM (SELECT @pre:=null, @rank:=0)tt,
             (
                 SELECT DepartmentId,Salary
                 FROM Employee
                 GROUP BY DepartmentId,Salary
                 ORDER BY DepartmentId,Salary DESC
             )te
       )t
INNER JOIN Department dep 
ON t.DepartmentId = dep.Id
INNER JOIN Employee emp 
ON t.DepartmentId = emp.DepartmentId and t.Salary = emp.Salary and t.RANK <= 3
ORDER BY t.DepartmentId, t.Salary DESC

得出

DepartmentEmployeeSalary
ITMax90000
ITJoe85000
ITRandy85000
ITWill70000
SalesHenry80000
SalesSam60000

196 - 删除重复的电子邮箱

  • 题目描述
    编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
IdEmail
1john@example.com
2bob@example.com
3john@example.com

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

IdEmail
1john@example.com
2bob@example.com
  • MySQL 脚本
-- ----------------------------
-- Table structure for `person`
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
 `Id` int(11) DEFAULT NULL,
 `Email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1','john@example.com');
INSERT INTO `person` VALUES ('2','bob@example.com');
INSERT INTO `person` VALUES ('3','john@example.com');

本题解法

  • 查询目标：删除一条记录
  • 查询范围：Person 表
  • 查询条件：删除所有重复的电子邮箱 ，重复的邮箱里只保留 Id 最小的哪个。

显然，通过这个查询条件可以提取出来两条 and 关系的条件：

  • 找出所有重复的电子邮箱
  • 删除 Id 大的重复邮箱
  • 对于条件（1），需要判断出所有重复的电子邮箱，即 p1.Email = p2.Email；
  • 对于条件（2），需要判断重复邮箱中 Id 较大的：p1.Id > p2.Id
# Write your MySQL query statement below
DELETE p1
FROM Person p1,Person p2
WHERE (p1.Email = p2.Email) AND (p1.Id > p2.Id);

197 - 上升的温度

  • 题目描述
    给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
Id(INT)RecordDate(DATE)Temperature(INT)
12015-01-0110
22015-01-0225
32015-01-0320
42015-01-0430
  • MySQL 脚本
-- ----------------------------
-- Table structure for `weather`
-- ----------------------------
DROP TABLE IF EXISTS `weather`;
CREATE TABLE `weather` (
 `Id` int(11) DEFAULT NULL,
 `RecordDate` date DEFAULT NULL,
 `Temperature` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of weather
-- ----------------------------
INSERT INTO `weather` VALUES ('1','2015-01-01', '10');
INSERT INTO `weather` VALUES ('2','2015-01-02', '25');
INSERT INTO `weather` VALUES ('3','2015-01-03', '20');
INSERT INTO `weather` VALUES ('4','2015-01-04', '30');

本题解法

我们可以使用 MySQL 的函数 DATEDIFF 来计算两个日期的差值，我们的限制条件是温度高且日期差 1。

# Write your MySQL query statement below
SELECT a.Id
FROM Weather a INNER JOIN Weather b
WHERE DATEDIFF(a.RecordDate,b.RecordDate) = 1 AND a.Temperature > b.Temperature;

262 - 行程和用户

  • 本题描述
    Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
IdClient_IdDriver_IdCity_IdStatusRequest_at
11101completed2013-10-01
22111cancelled_by_driver2013-10-01
33126completed2013-10-01
44136cancelled_by_client2013-10-01
51101completed2013-10-02
62116completed2013-10-02
73126completed2013-10-02
821212completed2013-10-03
931012completed2013-10-03
1041312cancelled_by_driver2013-10-03

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

Users_IdBannedRole
1Noclient
2Yesclient
3Noclient
4Noclient
10Nodriver
11Nodriver
12Nodriver
13Nodriver
  • Mysql脚本
Create table If Not Exists Trips (Id int, Client_Id int, Driver_Id int, City_Id int, Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at varchar(50))
Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner'))
Truncate table Trips
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')
Truncate table Users
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')

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

DayCancellation Rate
2013-10-010.33
2013-10-020.00
2013-10-030.50

本题解法：

  • 方法1：
  • 对trips表和users表连接，连接条件是行程对应的乘客非禁止且司机非禁止
  • 筛选订单日期在目标日期之间
  • 用日期进行分组
  • 分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值
  • 对订单取消率保留两位小数，对输出列名改名
# Write your MySQL query statement below
SELECT
    request_at as 'Day', round(avg(Status!='completed'), 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	
    request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY 
    request_at
  • 方法2：
    非禁止用户生成的订单意思是非禁止乘客的订单，主要结合日常生活，打车订单都是由乘客发起生成的，因此换个思路，GROUP，然后俩COUNT除一下
SELECT
	t.Request_at 'Day',ROUND(
    1 - COUNT(IF(t.`Status` = 'completed', 1, NULL)) / COUNT(*) , 2) 'Cancellation Rate'
FROM	Trips t
INNER JOIN Users u ON t.Client_Id = u.Users_Id AND u.Banned = 'No' 
WHERE
  t.Request_at >= '2013-10-01' AND t.Request_at < '2013-10-04'
GROUP BY t.Request_at

595 - 大的国家

  • 题目描述
    这里有张 World 表
namecontinentareapopulationgdp
AfghanistanAsia6522302550010020343000
AlbaniaEurope28748283174112960000
AlgeriaAfrica238174137100000188681000
AndorraEurope468781153712000
AngolaAfrica124670020609294100990000

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

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

例如，根据上表，我们应该输出:

namepopulationarea
Afghanistan25500100652230
Algeria371000002381741
  • Mysql脚本
-- ----------------------------
-- Table structure for `world`
-- ----------------------------
DROP TABLE IF EXISTS `world`;
CREATE TABLE `world` (
  `name`varchar(255) DEFAULT NULL,
 `continent` varchar(255) DEFAULT NULL,
  `area`int(11) DEFAULT NULL,
 `population` int(11) DEFAULT NULL,
  `gdp`varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of world
-- ----------------------------
INSERT INTO `world` VALUES ('Afghanistan','Asia', '652230', '25500100', '20343000000');
INSERT INTO `world` VALUES ('Albania','Europe', '28748', '2831741', '12960000000');
INSERT INTO `world` VALUES ('Algeria','Africa', '2381741', '37100000', '188681000000');
INSERT INTO `world` VALUES ('Andorra','Europe', '468', '78115', '3712000000');
INSERT INTO `world` VALUES ('Angola', 'Africa','1246700', '20609294', '100990000000');

本题解法

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

# Write your MySQL query statement below
SELECT name,population,area
FROM World
WHERE area > 3000000 OR population > 25000000;

596 - 超过 5 名学生的课

  • 题目描述
    有一个 courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于 5 名学生的课。

例如，表:

studentclass
AMath
BEnglish
CMath
DBiology
EMath
FComputer
GMath
HMath
IMath

应该输出:

class
Math
  • 注意: 学生在每个课中不应被重复计算。
  • MySQL 脚本
-- ----------------------------
-- Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
 `student` varchar(255) DEFAULT NULL,
  `class`varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('A', 'Math');
INSERT INTO `courses` VALUES ('B', 'English');
INSERT INTO `courses` VALUES ('C', 'Math');
INSERT INTO `courses` VALUES ('D', 'Biology');
INSERT INTO `courses` VALUES ('E', 'Math');
INSERT INTO `courses` VALUES ('F', 'Computer');
INSERT INTO `courses` VALUES ('G', 'Math');
INSERT INTO `courses` VALUES ('H', 'Math');
INSERT INTO `courses` VALUES ('I', 'Math');

本题解法：

这表里有重复记录的，比如 A 学生选了 Math 的记录可以有多条，但是认为 Math 只被 A 学生选了 1 次。

# Write your MySQL query statement below
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

601 - 体育馆的人流量

  • 题目描述
    X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于 100。

例如，表 stadium：

iddatepeople
12017-01-0110
22017-01-02109
32017-01-03150
42017-01-0499
52017-01-05145
62017-01-061455
72017-01-07199
82017-01-08188

对于上面的示例数据，输出为：

iddatepeople
52017-01-05145
62017-01-061455
72017-01-07199
82017-01-08188
  • 注意: 每天只有一行记录，日期随着 id 的增加而增加。
  • MySQL 脚本
Create table If Not Exists stadium (id int, visit_date DATE NULL, people int);
Truncate table 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');

本题解法：

举例来说：(29,'2017-5-29',150),(30,'2017-6-1',150), 没有 5 月 30 日，按照日期检索就会直接跳过，但这个其实是算连续的，所以只能按照 id 来检索。

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 - t2.id = 1 AND t1.id - t3.id = 2 AND t2.id - t3.id = 1) 
    OR
    (t2.id - t1.id = 1 AND t2.id - t3.id = 2 AND t1.id - t3.id = 1) 
    OR
    (t3.id - t2.id = 1 AND t2.id - t1.id = 1 AND t3.id - t1.id = 2)
)
ORDER BY t1.id;

620 - 有趣的电影

  • 题目描述
    某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED 显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL 查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

idmoviedescriptionrating
1Wargreat 3D8.9
2Sciencefiction8.5
3irishboring6.2
4Ice songFantacy8.6
5House cardInteresting9.1

对于上面的例子，则正确的输出是为：

idmoviedescriptionrating
5House cardInteresting9.1
1Wargreat 3D8.9
  • 解题思路
  • 通过MySQL来判断奇偶数
    如
num % 2 =1; num为奇数
num % 2 =0; num为偶数

本题解法

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

626 - 换座位

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

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

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

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

idstudent
1Abbot
2Doris
3Emerson
4Green
5Jeames

假如数据输入的是上表，则输出结果如下：

idstudent
1Doris
2Abbot
3Green
4Emerson
5Jeames
  • 注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。
  • MySQL 脚本
Create table If Not Exists seat(id int, studentvarchar(255));
Truncate table 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');

本题解法：

我们可以分成三块来写，第一块就是 id 为偶数的，id-1 就相当于和奇数的互换了，第二块是 id 为奇数的，id+1 就相当于和偶数的互换了，最后一块是最后一个为奇数的，不换，然后三块合并排序就出来结果了。

  1. 方法1：
    需要注意的是，最后一块的过滤条件为 WHERE mod(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat，需要保证 id 不为偶数。
# Write your MySQL query statement below
SELECT s.id,s.student
FROM (SELECT (id-1) AS id,student
      FROM seat
      WHERE mod(id,2) = 0
      UNION
      SELECT (id+1) AS id,student
      FROM seat
      WHERE mod(id,2) = 1 AND id != (SELECT COUNT(*) FROM seat)
      UNION
      SELECT id,student
      FROM seat
      WHERE mod(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat)) s
ORDER BY s.id;
  1. 方法2：CASE WHEN
# Write your MySQL query statement below
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;

627 - 交换工资

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

请注意，你必须编写一个 Update 语句，不要编写任何 Select 语句。

例如:

idnamesexsalary
1Am2500
2Bf1500
3Cm5500
4Df500

运行你所编写的更新语句之后，将会得到以下表:

idnamesexsalary
1Af2500
2Bm1500
3Cf5500
4Dm500
  • MySQL 脚本
-- ----------------------------
-- Table structure for `salary`
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id`int(11) NOT NULL,
  `name`varchar(10) NOT NULL,
  `sex`varchar(10) NOT NULL,
 `salary` int(11) NOT NULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salary
-- ----------------------------
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 函数
    IF(expr1, expr2, expr3) 如果 expr1 为 TRUE，则 IF () 的返回值为 expr2；否则返回值为 expr3。
# Write your MySQL query statement below
UPDATE salary
SET sex = IF(sex = "f","m","f");
  • 方法二：使用 case…when..then..else..end
UPDATE salary 
SET sex  = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);

刷完了力扣数据库的非会员的题，大部分都是些偏基础的题，逼我开会员丫，之后有空会刷刷算法，有异议之处，还望各位不吝赐教。

  Mysql   

本文由 icefire 创作，如果您觉得本文不错，请随意赞赏
本文链接：https://blog.noheart.cn/archives/sqlshell
采用 知识共享署名4.0 国际许可协议进行许可，转载前请务必署名(本站链接或原出处地址)
最后更新于：2020-05-02 23:08:43

评论
Valine Sakura
最新文章
小破站7年随记

小破站7年随记不知不觉，小破站晃晃悠悠的运行了7年，当初和我一起建站的那群小伙伴们不知道还生下几人，我也几乎很
听说《仙逆》出动漫了

有生之年系列，之前初中开始追的仙侠小说居然现在要出动漫了，这是我意想不到的，回顾那些年看的各类小说，仙逆算是最
旅行游记浅记

我想，没有人不爱珠海吧去了一次就还想去的城市，深深地为这座城市的美丽沉迷。晴天的珠海真的狠狠治愈了我，巨浪漫的
年中随笔

今年的故事很简单，遇见你，我很喜欢、
行百里者半九十

行百里者半九十
分类
记事本 Workrecord
生活随笔 essay
学习笔记 Study Notes
藏书阁 thinking
设计模式 1573199577788
星辰大海 星辰大海
标签
仙逆 仙逆
Algorithm algorithm
设计模式 designpatterns
HTTP http
docker docker
西游 西游
宇宙 宇宙
ffmpeg ffmpeg
MongoDB mongodb
universe universe
study study
Git git
文字 1571885335724
Gaming gaming
Windows windows
Design Patterns design-patterns
听歌入歌 1567593165530
H1Z1 h1z1
ArrayList arraylist
List list
JAVA java
Linux linux
Ubuntu ubuntu
music music
test test
spring boot spring-boot
growing up growing-up
graduation graduation
interesting interesting
fighting fighting
Linked List linked-list
Array array
queue queue
data structure data-structure
Software engineering software-engineering
SSH ssh
HashMap hashmap
Ajax ajax
Hibernate hibernate
Struts struts
SpringMVC springmvc
Spring Data JPA springdatajpa
Spring spring
Mybatis mybatis
Mysql mysql
python python
Construct construct
Miss miss
Text record text-record
Face challenge face-challenge
deep learning deep-learning
Long time no see long-time-no-see
life life