Leetcode数据库无锁题答题记录

175. Combine Two Tables

# Write your MySQL query statement below
select FirstName, LastName, City, State
from Person left join Address on Person.PersonId = Address.PersonId;

176. Second Highest Salary

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary
from Employee
where Salary != (select max(Salary) from Employee);

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select IFNULL((select distinct salary from employee order by salary desc limit N,1),NULL)
  );
END

178. Rank Scores

# Write your MySQL query statement below
select b.Score, cast(b.Rank as UNSIGNED) as Rank
from (select id, score, if(@prev = score, @rank, @rank := @rank + 1) as Rank, @prev := score as tmp
from scores, (select @rank := 0, @prev := null) as a
order by score desc) b;

180. Consecutive Numbers

# Write your MySQL query statement below
select distinct a.num as ConsecutiveNums
from logs as a, logs as b, logs as c
where (a.id <> b.id and b.id <> c.id) and (b.id = a.id + 1 and c.id = b.id + 1) and (a.num = b.num and b.num = c.num);

181. Employees Earning More Than Their Managers

# Write your MySQL query statement below
select name as Employee from employee as e
where e.salary > ifnull((select salary from employee where id=e.managerid), e.salary + 1);

182. Duplicate Emails

# Write your MySQL query statement below
select Email from Person group by Email having count(Email) > 1;

183. Customers Who Never Order

# Write your MySQL query statement below
select name as Customers from Customers where id not in (select distinct CustomerId from Orders);

184. Department Highest Salary

# Write your MySQL query statement below
select Department, e.Name as Employee, Salary
from 
(select DepartmentId as did, Department.Name as Department, max(Salary) maxsalary
from Employee join Department on Department.Id = DepartmentId
group by DepartmentId) as maxtable, Employee e
where e.DepartmentId = maxtable.did and maxsalary = e.Salary;

185. Department Top Three Salaries

# Write your MySQL query statement below
select Department, Employee, Salary
from (
select d1.Name as Department, e1.name as Employee, e1.Salary, (
	select count(distinct e2.salary)
	from Employee as e2
	where e2.salary > e1.salary and e2.departmentId = e1.departmentId
) as Rankk
from Employee e1, Department d1
where e1.departmentId = d1.id) as ddd
where Rankk < 3
order by ddd.Department, ddd.Salary desc;

196. Delete Duplicate Emails

# Write your MySQL query statement below
delete p1.* from Person p1, Person p2
where p1.email = p2.email and p1.id > p2.id;

197. Rising Temperature

# Write your MySQL query statement below
select w2.Id
from Weather w1, Weather w2
where datediff(w2.RecordDate, w1.RecordDate) = 1 and w2.Temperature > w1.Temperature;

262. Trips and Users

题目所需的建表语句如下：

user表

/*
 Navicat Premium Data Transfer

 Source Server         : LocalMySQL
 Source Server Type    : MySQL
 Source Server Version : 80011
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80011
 File Encoding         : 65001

 Date: 30/07/2018 13:38:27
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `banned` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `role` enum('client','driver','partner') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, 'No', 'client');
INSERT INTO `users` VALUES (2, 'Yes', 'client');
INSERT INTO `users` VALUES (3, 'No', 'client');
INSERT INTO `users` VALUES (4, 'No', 'client');
INSERT INTO `users` VALUES (10, 'No', 'driver');
INSERT INTO `users` VALUES (11, 'No', 'driver');
INSERT INTO `users` VALUES (12, 'No', 'driver');
INSERT INTO `users` VALUES (13, 'No', 'driver');

SET FOREIGN_KEY_CHECKS = 1;

trips表

/*
 Navicat Premium Data Transfer

 Source Server         : LocalMySQL
 Source Server Type    : MySQL
 Source Server Version : 80011
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 80011
 File Encoding         : 65001

 Date: 30/07/2018 13:38:43
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for trips
-- ----------------------------
DROP TABLE IF EXISTS `trips`;
CREATE TABLE `trips`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` int(10) UNSIGNED NOT NULL,
  `driver_id` int(10) UNSIGNED NOT NULL,
  `city_id` int(11) NOT NULL,
  `status` enum('completed','cancelled_by_driver','cancelled_by_client') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `request_at` date NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `client_fk`(`client_id`) USING BTREE,
  INDEX `driver_fk`(`driver_id`) USING BTREE,
  CONSTRAINT `client_fk` FOREIGN KEY (`client_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `driver_fk` FOREIGN KEY (`driver_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of trips
-- ----------------------------
INSERT INTO `trips` VALUES (1, 1, 10, 1, 'completed', '2013-10-01');
INSERT INTO `trips` VALUES (2, 2, 11, 1, 'cancelled_by_driver', '2013-10-01');
INSERT INTO `trips` VALUES (3, 3, 12, 6, 'completed', '2013-10-01');
INSERT INTO `trips` VALUES (4, 4, 13, 6, 'cancelled_by_client', '2013-10-01');
INSERT INTO `trips` VALUES (5, 1, 10, 1, 'completed', '2013-10-02');
INSERT INTO `trips` VALUES (6, 2, 11, 6, 'completed', '2013-10-02');
INSERT INTO `trips` VALUES (7, 3, 12, 6, 'completed', '2013-10-02');
INSERT INTO `trips` VALUES (8, 2, 12, 12, 'completed', '2013-10-03');
INSERT INTO `trips` VALUES (9, 3, 10, 12, 'completed', '2013-10-03');
INSERT INTO `trips` VALUES (10, 4, 13, 12, 'cancelled_by_driver', '2013-10-03');

SET FOREIGN_KEY_CHECKS = 1;

Accepted Solution

# Write your MySQL query statement below
select date as Day, cast(format(sum(valid) / count(date), 2) as decimal(10, 2)) as 'Cancellation Rate'
from (
		select t.id, t.client_id, c.banned cb, t.driver_id, d.banned db, t.status, t.request_at as date, 
				if (status in ('cancelled_by_driver', 'cancelled_by_client'), 1, 0) as valid
		from trips t, users c, users d
		where t.client_id = c.users_id
				and t.driver_id = d.users_id
				and c.banned != 'Yes' and d.banned != 'Yes'
				and t.request_at between '2013-10-01' and '2013-10-03') as tmp
group by date;

595. Big Countries

# Write your MySQL query statement below
select name, population, area
from World
where population > 25000000 or area > 3000000;

596. Classes More Than 5 Students

# Write your MySQL query statement below
select class
from courses
group by class
having count(distinct student) >= 5;

620. Not Boring Movies

# Write your MySQL query statement below
select *
from cinema
where id % 2 <> 0 and description not like '%boring%'
order by rating desc;

626. Exchange Seats

# Write your MySQL query statement below
select b.id, a.student
from seat a, seat b, (select max(id) as id from seat) as maxid
where ((maxid.id % 2 <> 0 and a.id = maxid.id and a.id = b.id)) or
(a.id <> maxid.id and a.id % 2 <> 0 and b.id = a.id + 1) or
(b.id <> maxid.id and a.id % 2 = 0 and a.id = b.id + 1);

627. Swap Salary

# Write your MySQL query statement below
update salary
set sex=case sex
when 'm' then 'f'
else 'm'
end;