目录
1.从不订购的客户(183)
解法一(not in)
解法二(is)
解法三(not exists)
解法四(isnull函数)
2.部门工资最高的员工(184)
解法一(in)
解法二(rank窗口函数)
1.从不订购的客户(183)
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders
表:
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
例如给定上述表格,你的查询应返回:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
解法一(not in)
# Write your MySQL query statement below distinct name Customers
select customers.name as 'Customers'
from customers
where customers.id not in
(
select customerid from orders
);
解法二(is)
# Write your MySQL query statement below distinct name Customers
select c.Name as Customers
from Customers as c
left join Orders as o on c.Id = o.CustomerId
where o.Id is null
解法三(not exists)
select name as Customers
from Customers c
where not exists (select 1 from Orders o where c.id = o.CustomerId)
解法四(isnull函数)
select name Customers from customers left join orders
on customers.id = orders.customerId where isnull(customerId);
2.部门工资最高的员工(184)
表: Employee
+--------------+---------+ | 列名 | 类型 | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id是此表的主键列。 departmentId是Department表中ID的外键。 此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。
表: Department
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id是此表的主键列。 此表的每一行都表示一个部门的ID及其名称。 编写SQL查询以查找每个部门中薪资最高的员工。 按 任意顺序 返回结果表。
解法一(in)
# Write your MySQL query statement below
select d.name Department,e.name Employee,e.salary Salary
from employee e join department d
on e.departmentId=d.id
where (e.departmentId,salary)
in (select departmentId,max(salary) from employee group by departmentId)
解法二(rank窗口函数)
select Department, Employee, Salary
from
(
select
D.Name as Department,
E.Name as Employee,
E.Salary as Salary,
rank() over(partition by D.Name order by E.Salary desc) as rank_
from Employee E join Department D on E.DepartmentId = D.Id
) as tmp
where rank_ = 1
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
相关推荐: 多线程知识:三个线程如何交替打印ABC循环100次
本文博主给大家讲解一道网上非常经典的多线程面试题目。关于三个线程如何交替打印ABC循环100次的问题。 下文实现代码都基于Java代码在单个JVM内实现。 问题描述 给定三个线程,分别命名为A、B、C,要求这三个线程按照顺序交替打印ABC,每个字母打印100次…