LeetCode-数据库181-183

181题干：

编写一个SQL查询来查找收入比经理高的员工。

以 任意顺序 返回结果表

SQL架构

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', 'None')
insert into Employee (id, name, salary, managerId) values ('4', 'Max', '90000', 'None')

示例

 

 

 题解

SELECT E1.name AS Employee
From Employee E1 LEFT JOIN Employee E2 ON E1.managerId = E2.id 
WHERE E1.salary > E2.salary

 

182

SQL架构

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')

 

 

 题解

SELECT P.Email
FROM(SELECT Email, COUNT(Email) AS 'COUNT'
FROM Person
GROUP BY Email) P
WHERE P.COUNT >= 2;

 

 183

SQL架构

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Create table If Not Exists Customers (id int, 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')

 

示例

 

 

SELECT Name AS 'Customers'
FROM Customers
WHERE Id NOT IN (SELECT CustomerId FROM Orders);