力扣每日刷题_随机个数

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

实现方法

elect email as Email from person group by email having count(id) > 1;

小结

聚合函数

力扣_183题

说明

https://leetcode.cn/problems/customers-who-never-order/submissions/548605636/

分析

找出所有从不点任何东西的顾客。以任意顺序返回结果表。

输入

输出

订单表

顾客表

Customers

customerId

name

Henry

Joe

Max

Henry

Sam

Max

分析:方法一:子查询

订单表

顾客表

使用子查询,查出订单表里买过东西的顾客id,然后作为条件在顾客表查询id不等于customeid

Customers

customerId

name

Henry

Joe

Max

Henry

Sam

Max

分析:方法二:左连接

订单表

顾客表

使用顾客表左连接订单表,条件是顾客表id=订单表customeid

customerId

name

Joe

Henry

Sam

Max

customer.id

name

orders.id

customerId

orders.id为空行对应的custome.id即为从不点任何东西的顾客

customer.id

name

orders.id

customerId

输出name

Customers

Joe

Henry

null

Henry

null

Max

null

Max

Sam

Max

null

实现

准备工作

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

实现方法

方法1:子查询

elect name as Customers from customers where id not in (select customerId from orders);

方法2:左连接

elect name as Customers from customers left join orders on Orders.customerId = Customers.id where Orders.id is null;

小结

子查询

左连接

力扣_184题

说明

https://leetcode.cn/problems/department-highest-salary/submissions/548652398/

分析

查找出每个部门中薪资最高的员工,按任意顺序返回结果表。
查找出每个部门中薪资最高的员工,按任意顺序返回结果表。
输入:employee表					输入:department表
id	name	salary	departmentId		id	name
1	Joe	70000	1		1	IT
2	Jim	90000	1		2	Sales
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1
分析
id	name	salary	departmentId		id	name
1	Joe	70000	1		1	IT
2	Jim	90000	1		2	Sales
3	Henry	80000	2
4	Sam	60000	2
5	Max	90000	1
首先对两个表进行连接查询,连接条件是员工表的departmentid等于部门表的id,然后利用窗口函数dense_rank(),按照部门名称分组,员工薪资降序排列,查询连接表中的Department.name,Employee.name,salary,ranking得到如下表:



	Department.name		Employee.name	salary	ranking
	IT		Jim	90000	1
	IT		Max	90000	1
	IT		Joe	70000	2
	Salary		Henry	80000	1
	Salary		Sam	60000	2
把上表作为一个临时表,使用子查询,从表中查找ranking=1的Employee.name,结果如下表:
把上表作为一个临时表,使用子查询,从表中查找ranking=1的Employee.name,结果如下表:
	Department	Employee	Salary
	IT	Jim	90000
	IT	Max	90000
	Salary	Henry	80000

实现

准备工作

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', '70000', '1'); 
insert into Employee (id, name, salary, departmentId) values ('2', 'Jim', '90000', '1');
insert into Employee (id, name, salary, departmentId) values ('3', 'Henry', '80000', '2');
insert into Employee (id, name, salary, departmentId) values ('4', 'Sam', '60000', '2');
insert into Employee (id, name, salary, departmentId) values ('5', 'Max', '90000', '1'); 
Truncate table Department; insert into Department (id, name) values ('1', 'IT'); 
insert into Department (id, name) values ('2', 'Sales');

实现方法

ith t1 as( select Department.name as Department, Employee.name as Employee, Salary, dense_rank() over (partition by Department.name order by salary desc) as ranking from employee, department where Employee.departmentId = Department.id ) select Department, Employee, Salary from t1 where ranking = 1;