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;
