175.组合两个表
表:
Person+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ personId 是该表的主键（具有唯一值的列）。 该表包含一些人的 ID 和他们的姓和名的信息。表:
Address+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ addressId 是该表的主键（具有唯一值的列）。 该表的每一行都包含一个 ID = PersonId 的人的城市和州的信息。编写解决方案，报告
Person表中每个人的姓、名、城市和州。如果personId的地址不在Address表中，则报告为null。以 任意顺序 返回结果表。
示例 1:
输入: Person表: +----------+----------+-----------+ | personId | lastName | firstName | +----------+----------+-----------+ | 1 | Wang | Allen | | 2 | Alice | Bob | +----------+----------+-----------+ Address表: +-----------+----------+---------------+------------+ | addressId | personId | city | state | +-----------+----------+---------------+------------+ | 1 | 2 | New York City | New York | | 2 | 3 | Leetcode | California | +-----------+----------+---------------+------------+ 输出: +-----------+----------+---------------+----------+ | firstName | lastName | city | state | +-----------+----------+---------------+----------+ | Allen | Wang | Null | Null | | Bob | Alice | New York City | New York | +-----------+----------+---------------+----------+ 解释: 地址表中没有 personId = 1 的地址，所以它们的城市和州返回 null。 addressId = 1 包含了 personId = 2 的地址信息。
根据示例一输出结果发现state 中出现Null并且数据项和Person表一样，可得这两个表进行了左连接且左边是Person表，即可解出本题
select firstName,lastName,city,state
from Person left join address on Person.personId = address.personId176.第二高的薪水
Employee表：+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ 在 SQL 中，id 是这个表的主键。 表的每一行包含员工的工资信息。查询并返回
Employee表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回null(Pandas 则返回 None)。查询结果如下例所示。
示例 1：
输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+示例 2：
输入： Employee 表： +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ 输出： +---------------------+ | SecondHighestSalary | +---------------------+ | null | +---------------------+
利用distinct order by ....desc 在配合limit1，1即可求出薪水第二，但是如果没有第二的薪水无法返回null，所以需要再套一个select ，如果查询出为空即可返回null
select
(select distinct salary from employee order by salary desc limit 1,1)
as SecondHighestSalary177.第N高的薪水
表:
Employee+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ 在 SQL 中，id 是该表的主键。 该表的每一行都包含有关员工工资的信息。查询
Employee表中第n高的工资。如果没有第n个最高工资，查询结果应该为null。查询结果格式如下所示。
示例 1:
输入: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+示例 2:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | null | +------------------------+
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N := N-1;
RETURN (
# Write your MySQL query statement below.
select (select distinct salary from employee order by salary desc limit N,1)
);
END178.分数排名
表:
Scores+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | score | decimal | +-------------+---------+ id 是该表的主键（有不同值的列）。 该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。编写一个解决方案来查询分数的排名。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等，那么两个分数的排名应该相同。
- 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
按
score降序返回结果表。查询结果格式如下所示。
示例 1:
输入: Scores 表: +----+-------+ | id | score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 输出: +-------+------+ | score | rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+
本题因为本题为连续排序 需要使用dense_rank配合窗体函数使用 对score进行倒叙排序
注意rank不能直接作为标题，需要单引号
select score,dense_rank()over(order by score desc) as 'rank' from scores180.连续出现的数字
表：
Logs+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | num | varchar | +-------------+---------+ 在 SQL 中，id 是该表的主键。 id 是一个自增列。找出所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。
结果格式如下面的例子所示：
示例 1:
输入： Logs 表： +----+-----+ | id | num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+ 输出： Result 表： +-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+ 解释：1 是唯一连续出现至少三次的数字。
这题关键字 “连续” “至少三次” 所以这里可以运用三个表查询，依次去判断相等，最后还需要带上一个distinct避免重复
select distinct l1.num as ConsecutiveNums from
logs as l1,
logs as l2,
logs as l3
where l1.id-1 = l2.id and l1.num = l2.num and l2.id-1 = l3.id and l2.num = l3.num181.超过经理收入的员工
表：
Employee+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | salary | int | | managerId | int | +-------------+---------+ id 是该表的主键（具有唯一值的列）。 该表的每一行都表示雇员的ID、姓名、工资和经理的ID。编写解决方案，找出收入比经理高的员工。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1:
输入: Employee 表: +----+-------+--------+-----------+ | id | name | salary | managerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | Null | | 4 | Max | 90000 | Null | +----+-------+--------+-----------+ 输出: +----------+ | Employee | +----------+ | Joe | +----------+ 解释: Joe 是唯一挣得比经理多的雇员。
本题需要用到两个employee表，一个用来查询员工，一个用来查询老板，并且需要判断员工和老板是上下级关系，即可得出结果
select em1.name as Employee from employee em1,employee em2
where em1.managerId = em2.id and em1.salary > em2.salary 182.查找重复的电子邮箱
表:
Person+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id 是该表的主键（具有唯一值的列）。 此表的每一行都包含一封电子邮件。电子邮件不包含大写字母。编写解决方案来报告所有重复的电子邮件。 请注意，可以保证电子邮件字段不为 NULL。
以 任意顺序 返回结果表。
结果格式如下例。
示例 1:
输入: Person 表: +----+---------+ | id | email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+ 输出: +---------+ | Email | +---------+ | a@b.com | +---------+ 解释: a@b.com 出现了两次。
简单题
select email from Person group by email having count(email)>1183.从不订购的客户
Customers表：+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ 在 SQL 中，id 是该表的主键。 该表的每一行都表示客户的 ID 和名称。
Orders表：+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ 在 SQL 中，id 是该表的主键。 customerId 是 Customers 表中 ID 的外键( Pandas 中的连接键)。 该表的每一行都表示订单的 ID 和订购该订单的客户的 ID。找出所有从不点任何东西的顾客。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： Customers table: +----+-------+ | id | name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Orders table: +----+------------+ | id | customerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ 输出： +-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
这题需要求出从不订购的客户，也就是说在orders表中没有的客户，所以可以通过左连接去查询customerid为null的客户，即可求出此题。
select name as Customers from Customers
left join Orders on Customers.id = Orders.CustomerId
where CustomerId is null184.部门工资最高的员工
表：
Employee+--------------+---------+ | 列名 | 类型 | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ 在 SQL 中，id是此表的主键。 departmentId 是 Department 表中 id 的外键（在 Pandas 中称为 join key）。 此表的每一行都表示员工的 id、姓名和工资。它还包含他们所在部门的 id。表：
Department+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ 在 SQL 中，id 是此表的主键列。 此表的每一行都表示一个部门的 id 及其名称。查找出每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。示例 1:
输入： Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出： +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。
思路：先取出每个部门的最高薪水，再去和原表进行比较，取出最高薪水的员工，在和部门表进行拼接
select Department.name as Department,employee.name as Employee,maxx as Salary
from employee join
(select departmentId,max(salary) as maxx from employee group by departmentId) as tk
on employee.departmentId = tk.departmentId
join Department on employee.departmentId = Department.id
where employee.departmentId = tk.departmentId and employee.salary = tk.maxx185.部门工资前三高的所有员工
表:
Employee+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id 是该表的主键列(具有唯一值的列)。 departmentId 是 Department 表中 ID 的外键（reference 列）。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。表:
Department+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行表示部门ID和部门名。公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。
编写解决方案，找出每个部门中 收入高的员工 。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释: 在IT部门: - Max的工资最高 - 兰迪和乔都赚取第二高的独特的薪水 - 威尔的薪水是第三高的 在销售部: - 亨利的工资最高 - 山姆的薪水第二高 - 没有第三高的工资，因为只有两名员工
思路：既然需要得到薪资的排序，理所应到想到利用dense_rank函数，取出排名之后再去取出前三即可
select distinct department.name as Department,employee.name as Employee,salary as Salary from
employee join department on employee.departmentId = department.id
join (select employee.name as name,dense_rank()over(partition by departmentId order by salary desc) as posn
from employee join department on employee.departmentId = department.id) as rk on employee.name = rk.name
where posn <=3 196.删除重复的电子邮箱
表:
Person+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | email | varchar | +-------------+---------+ id 是该表的主键列(具有唯一值的列)。 该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。编写解决方案 删除 所有重复的电子邮件，只保留一个具有最小
id的唯一电子邮件。（对于 SQL 用户，请注意你应该编写一个
DELETE语句而不是SELECT语句。）（对于 Pandas 用户，请注意你应该直接修改
Person表。）运行脚本后，显示的答案是
Person表。驱动程序将首先编译并运行您的代码片段，然后再显示Person表。Person表的最终顺序 无关紧要 。返回结果格式如下示例所示。
示例 1:
输入: Person 表: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ 输出: +----+------------------+ | id | email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+ 解释: john@example.com重复两次。我们保留最小的Id = 1。
delete p1 from person as p1 , person as p2 where p1.email = p2.email and p1.id > p2.id197.上升的温度
表：
Weather+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id 是该表具有唯一值的列。 没有具有相同 recordDate 的不同行。 该表包含特定日期的温度信息编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的
id。返回结果 无顺序要求 。
结果格式如下例子所示。
示例 1：
输入： Weather 表： +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+ 输出： +----+ | id | +----+ | 2 | | 4 | +----+ 解释： 2015-01-02 的温度比前一天高（10 -> 25） 2015-01-04 的温度比前一天高（20 -> 30）
思路：本题需要两天的比较，所以首先想到两个表一起查询，即可解决
select w1.id from weather w1,
weather w2
where datediff(w1.recordDate,w2.recordDate) = 1 and w1.Temperature > w2.Temperature262.行程和用户
表：
Trips+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | varchar | +-------------+----------+ id 是这张表的主键（具有唯一值的列）。 这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。 status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。表：
Users+-------------+----------+ | Column Name | Type | +-------------+----------+ | users_id | int | | banned | enum | | role | enum | +-------------+----------+ users_id 是这张表的主键（具有唯一值的列）。 这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。 banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
编写解决方案找出
"2013-10-01"至"2013-10-03"期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率Cancellation Rate需要四舍五入保留 两位小数 。返回结果表中的数据 无顺序要求 。
结果格式如下例所示。
示例 1：
输入： Trips 表： +----+-----------+-----------+---------+---------------------+------------+ | id | client_id | driver_id | city_id | status | request_at | +----+-----------+-----------+---------+---------------------+------------+ | 1 | 1 | 10 | 1 | completed | 2013-10-01 | | 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 | | 3 | 3 | 12 | 6 | completed | 2013-10-01 | | 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 | | 5 | 1 | 10 | 1 | completed | 2013-10-02 | | 6 | 2 | 11 | 6 | completed | 2013-10-02 | | 7 | 3 | 12 | 6 | completed | 2013-10-02 | | 8 | 2 | 12 | 12 | completed | 2013-10-03 | | 9 | 3 | 10 | 12 | completed | 2013-10-03 | | 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 | +----+-----------+-----------+---------+---------------------+------------+ Users 表： +----------+--------+--------+ | users_id | banned | role | +----------+--------+--------+ | 1 | No | client | | 2 | Yes | client | | 3 | No | client | | 4 | No | client | | 10 | No | driver | | 11 | No | driver | | 12 | No | driver | | 13 | No | driver | +----------+--------+--------+ 输出： +------------+-------------------+ | Day | Cancellation Rate | +------------+-------------------+ | 2013-10-01 | 0.33 | | 2013-10-02 | 0.00 | | 2013-10-03 | 0.50 | +------------+-------------------+ 解释： 2013-10-01： - 共有 4 条请求，其中 2 条取消。 - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。 - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 3) = 0.33 2013-10-02： - 共有 3 条请求，其中 0 条取消。 - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。 - 取消率为 (0 / 2) = 0.00 2013-10-03： - 共有 3 条请求，其中 1 条取消。 - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。 - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。 - 取消率为 (1 / 2) = 0.50
这题我本来是
select request_at as Day ,round(
sum(
if( status = 'completed' , 0 , 1)
)
/count(*),2
) as 'Cancellation Rate' from
(select * from trips
where client_id in (select users_id as client_id from users where banned = 'NO')) as trip
where request_at between '2013-10-01' and '2013-10-03'
group by request_at
发现他0不能通过，所以要改成left join去修改
SELECT request_at AS Day,
ROUND(
SUM(
IF(T.STATUS = 'completed',0,1)
)
/
COUNT(T.STATUS),
2
) AS 'Cancellation Rate'
FROM trips AS T LEFT JOIN
(
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
SELECT users_id
FROM users
WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY request_at550.游戏玩法分析 IV
Table:
Activity+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ （player_id，event_date）是此表的主键（具有唯一值的列的组合）。 这张表显示了某些游戏的玩家的活动情况。 每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
结果格式如下所示：
示例 1：
输入： Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-03-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ 输出： +-----------+ | fraction | +-----------+ | 0.33 | +-----------+ 解释： 只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33
思路，先求出第二天登录的时间，再去找表里面有没有第二天登录的并取出算数量，再去和整理相除得出结果
select round(count(distinct player_id)/
(select count(distinct player_id) from activity),2) as fraction
from activity where (player_id,event_date) in
(select player_id,min(event_date) + interval 1 day
from activity group by player_id)511.游戏玩法分析|
活动表
Activity：+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ 在 SQL 中，表的主键是 (player_id, event_date)。 这张表展示了一些游戏玩家在游戏平台上的行为活动。 每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。查询每位玩家 第一次登录平台的日期。
查询结果的格式如下所示：
Activity 表： +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result 表： +-----------+-------------+ | player_id | first_login | +-----------+-------------+ | 1 | 2016-03-01 | | 2 | 2017-06-25 | | 3 | 2016-03-02 | +-----------+-------------+
第一种：直接取登录时间的最小值 就是他第一次登录的时间
select player_id,min(event_date) as first_login
from activity
group by player_id第二种：运用rank函数求出每一个账户的最早登录时间
# Write your MySQL query statement below
select player_id,event_date as first_login from
(select *,rank()over(partition by player_id order by event_date) as rk from activity)as x where x.rk = 1570.至少有5名直接下属的经理
表:
Employee+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ id 是此表的主键（具有唯一值的列）。 该表的每一行表示雇员的名字、他们的部门和他们的经理的id。 如果managerId为空，则该员工没有经理。 没有员工会成为自己的管理者。编写一个解决方案，找出至少有五个直接下属的经理。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Employee 表: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | Null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ 输出: +------+ | name | +------+ | John | +------+
思路：先求出有五个下属的经理ID，再去原表中进行联表查询，最后剩下结果就是经理的名字
select name
from employee join
(select managerId
from employee
group by managerId
having count(*)>=5) as rk
on employee.id = rk.managerId577.员工奖金
表：
Employee+-------------+---------+ | Column Name | Type | +-------------+---------+ | empId | int | | name | varchar | | supervisor | int | | salary | int | +-------------+---------+ empId 是该表中具有唯一值的列。 该表的每一行都表示员工的姓名和 id，以及他们的工资和经理的 id。表：
Bonus+-------------+------+ | Column Name | Type | +-------------+------+ | empId | int | | bonus | int | +-------------+------+ empId 是该表具有唯一值的列。 empId 是 Employee 表中 empId 的外键(reference 列)。 该表的每一行都包含一个员工的 id 和他们各自的奖金。编写解决方案，报告每个奖金 少于
1000的员工的姓名和奖金数额。以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： Employee table: +-------+--------+------------+--------+ | empId | name | supervisor | salary | +-------+--------+------------+--------+ | 3 | Brad | null | 4000 | | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 4 | Thomas | 3 | 4000 | +-------+--------+------------+--------+ Bonus table: +-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+ 输出： +------+-------+ | name | bonus | +------+-------+ | Brad | null | | John | null | | Dan | 500 | +------+-------+
思路：题目需要查奖金，奖金表条数比员工表少而且只有部分获得，所以可以使用左连接，这样就能列出员工工资，然后再去判定小于1000 注意还要判断null
select name,bonus from employee
left join bonus on employee.empId = bonus.empId
where bonus<1000 or bonus is null585.2016年的投资
Insurance表：+-------------+-------+ | Column Name | Type | +-------------+-------+ | pid | int | | tiv_2015 | float | | tiv_2016 | float | | lat | float | | lon | float | +-------------+-------+ pid 是这张表的主键(具有唯一值的列)。 表中的每一行都包含一条保险信息，其中： pid 是投保人的投保编号。 tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。 lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。 lon 是投保人所在城市的经度。题目数据确保 lon 不为空。编写解决方案报告 2016 年 (
tiv_2016) 所有满足下述条件的投保人的投保金额之和：
- 他在 2015 年的投保额 (
tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。- 他所在的城市必须与其他投保人都不同（也就是说 (
lat, lon) 不能跟其他任何一个投保人完全相同）。
tiv_2016四舍五入的 两位小数 。查询结果格式如下例所示。
示例 1：
输入： Insurance 表： +-----+----------+----------+-----+-----+ | pid | tiv_2015 | tiv_2016 | lat | lon | +-----+----------+----------+-----+-----+ | 1 | 10 | 5 | 10 | 10 | | 2 | 20 | 20 | 20 | 20 | | 3 | 10 | 30 | 20 | 20 | | 4 | 10 | 40 | 40 | 40 | +-----+----------+----------+-----+-----+ 输出： +----------+ | tiv_2016 | +----------+ | 45.00 | +----------+ 解释： 表中的第一条记录和最后一条记录都满足两个条件。 tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。 第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。 因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。
思路：根据条件，先去取出2015中与别人不一样的，也就是需要满足条件count(*)>1，同理第二个条件也这样进行判断就能得出最后结果
select round(sum(tiv_2016),2) as tiv_2016 from insurance
where tiv_2015 in
(select tiv_2015 from insurance group by tiv_2015 having count(*)>1)
and lat in
(select lat from insurance group by lat,lon having count(*)=1)584.寻找用户推荐人
表:
Customer+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | referee_id | int | +-------------+---------+ 在 SQL 中，id 是该表的主键列。 该表的每一行表示一个客户的 id、姓名以及推荐他们的客户的 id。找出那些 没有被
id = 2的客户 推荐 的客户的姓名。以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： Customer 表: +----+------+------------+ | id | name | referee_id | +----+------+------------+ | 1 | Will | null | | 2 | Jane | null | | 3 | Alex | 2 | | 4 | Bill | null | | 5 | Zack | 1 | | 6 | Mark | 2 | +----+------+------------+ 输出： +------+ | name | +------+ | Will | | Jane | | Bill | | Zack | +------+
简单题，主要是需要判断null
select name from customer where referee_id is null or referee_id !=2586.订单最多的客户
表:
Orders+-----------------+----------+ | Column Name | Type | +-----------------+----------+ | order_number | int | | customer_number | int | +-----------------+----------+ 在 SQL 中，Order_number是该表的主键。 此表包含关于订单ID和客户ID的信息。查找下了 最多订单 的客户的
customer_number。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。
查询结果格式如下所示。
示例 1:
输入: Orders 表: +--------------+-----------------+ | order_number | customer_number | +--------------+-----------------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 3 | +--------------+-----------------+ 输出: +-----------------+ | customer_number | +-----------------+ | 3 | +-----------------+ 解释: customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。 所以结果是该顾客的 customer_number ，也就是 3 。
简单题
select customer_number from orders
group by customer_number
order by count(*) desc
limit 1595.大的国家
World表：+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | bigint | +-------------+---------+name是该表的主键（具有唯一值的列）。 这张表的每一行提供：国家名称、所属大陆、面积、人口和 GDP 值。如果一个国家满足下述两个条件之一，则认为该国是 大国 ：
- 面积至少为 300 万平方公里（即，
3000000 km2），或者- 人口至少为 2500 万（即
25000000）编写解决方案找出 大国 的国家名称、人口和面积。
按 任意顺序 返回结果表。
返回结果格式如下例所示。
示例：
输入： World 表： +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+ 输出： +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+
简单题
select name,population,area from world where population>=25000000 or area >= 3000000596.超过5名学生的课
表:
Courses+-------------+---------+ | Column Name | Type | +-------------+---------+ | student | varchar | | class | varchar | +-------------+---------+ 在 SQL 中，(student, class)是该表的主键列。 该表的每一行表示学生的名字和他们注册的班级。查询 至少有5个学生 的所有班级。
以 任意顺序 返回结果表。
查询结果格式如下所示。
示例 1:
输入: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+ 输出: +---------+ | class | +---------+ | Math | +---------+ 解释: -数学课有6个学生，所以我们包括它。 -英语课有1名学生，所以我们不包括它。 -生物课有1名学生，所以我们不包括它。 -计算机课有1个学生，所以我们不包括它。
简单题
select class from courses group by class having count(*)>=5601.体育馆的人流量
表：
Stadium+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date 是该表中具有唯一值的列。 每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录，日期随着 id 的增加而增加编写解决方案找出每行的人数大于或等于
100且id连续的三行或更多行记录。返回按
visit_date升序排列 的结果表。查询结果格式如下所示。
示例 1:
输入： Stadium表: +------+------------+-----------+ | id | visit_date | people | +------+------------+-----------+ | 1 | 2017-01-01 | 10 | | 2 | 2017-01-02 | 109 | | 3 | 2017-01-03 | 150 | | 4 | 2017-01-04 | 99 | | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-09 | 188 | +------+------------+-----------+ 输出： +------+------------+-----------+ | id | visit_date | people | +------+------------+-----------+ | 5 | 2017-01-05 | 145 | | 6 | 2017-01-06 | 1455 | | 7 | 2017-01-07 | 199 | | 8 | 2017-01-09 | 188 | +------+------------+-----------+ 解释： id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。 请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。 不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by id602.好友申请Ⅱ:谁有最多的好友
RequestAccepted表：+----------------+---------+ | Column Name | Type | +----------------+---------+ | requester_id | int | | accepter_id | int | | accept_date | date | +----------------+---------+ (requester_id, accepter_id) 是这张表的主键(具有唯一值的列的组合)。 这张表包含发送好友请求的人的 ID ，接收好友请求的人的 ID ，以及好友请求通过的日期。编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。
生成的测试用例保证拥有最多好友数目的只有 1 个人。
查询结果格式如下例所示。
示例 1：
输入： RequestAccepted 表： +--------------+-------------+-------------+ | requester_id | accepter_id | accept_date | +--------------+-------------+-------------+ | 1 | 2 | 2016/06/03 | | 1 | 3 | 2016/06/08 | | 2 | 3 | 2016/06/08 | | 3 | 4 | 2016/06/09 | +--------------+-------------+-------------+ 输出： +----+-----+ | id | num | +----+-----+ | 3 | 3 | +----+-----+ 解释： 编号为 3 的人是编号为 1 ，2 和 4 的人的好友，所以他总共有 3 个好友，比其他人都多。进阶：在真实世界里，可能会有多个人拥有好友数相同且最多，你能找到所有这些人吗？
select id,count(*) as num from (
select requester_id as id from RequestAccepted
union all
select accepter_id as id from RequestAccepted
) as t
group by id
order by num desc
limit 1607.销售员
表:
SalesPerson+-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id 是该表的主键列(具有唯一值的列)。 该表的每一行都显示了销售人员的姓名和 ID ，以及他们的工资、佣金率和雇佣日期。表:
Company+-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id 是该表的主键列(具有唯一值的列)。 该表的每一行都表示公司的名称和 ID ，以及公司所在的城市。表:
Orders+-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id 是该表的主键列(具有唯一值的列)。 com_id 是 Company 表中 com_id 的外键（reference 列）。 sales_id 是来自销售员表 sales_id 的外键（reference 列）。 该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。编写解决方案，找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1：
输入： SalesPerson 表: +----------+------+--------+-----------------+------------+ | sales_id | name | salary | commission_rate | hire_date | +----------+------+--------+-----------------+------------+ | 1 | John | 100000 | 6 | 4/1/2006 | | 2 | Amy | 12000 | 5 | 5/1/2010 | | 3 | Mark | 65000 | 12 | 12/25/2008 | | 4 | Pam | 25000 | 25 | 1/1/2005 | | 5 | Alex | 5000 | 10 | 2/3/2007 | +----------+------+--------+-----------------+------------+ Company 表: +--------+--------+----------+ | com_id | name | city | +--------+--------+----------+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +--------+--------+----------+ Orders 表: +----------+------------+--------+----------+--------+ | order_id | order_date | com_id | sales_id | amount | +----------+------------+--------+----------+--------+ | 1 | 1/1/2014 | 3 | 4 | 10000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +----------+------------+--------+----------+--------+ 输出： +------+ | name | +------+ | Amy | | Mark | | Alex | +------+ 解释： 根据表orders中的订单 '3' 和 '4' ，容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。 所以我们需要输出表salesperson中所有其他人的名字。
select name from SalesPerson
where sales_id not in (
select sales_id from orders
left join company on orders.com_id = company.com_id
where company.name = 'RED')608.树节点
表：
Tree+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | p_id | int | +-------------+------+ id 是该表中具有唯一值的列。 该表的每行包含树中节点的 id 及其父节点的 id 信息。 给定的结构总是一个有效的树。树中的每个节点可以是以下三种类型之一：
- "Leaf"：节点是叶子节点。
- "Root"：节点是树的根节点。
- "lnner"：节点既不是叶子节点也不是根节点。
编写一个解决方案来报告树中每个节点的类型。
以 任意顺序 返回结果表。
结果格式如下所示。
示例 1：
输入： Tree table: +----+------+ | id | p_id | +----+------+ | 1 | null | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +----+------+ 输出： +----+-------+ | id | type | +----+-------+ | 1 | Root | | 2 | Inner | | 3 | Leaf | | 4 | Leaf | | 5 | Leaf | +----+-------+ 解释： 节点 1 是根节点，因为它的父节点为空，并且它有子节点 2 和 3。 节点 2 是一个内部节点，因为它有父节点 1 和子节点 4 和 5。 节点 3、4 和 5 是叶子节点，因为它们有父节点而没有子节点。示例 2：
输入： Tree table: +----+------+ | id | p_id | +----+------+ | 1 | null | +----+------+ 输出： +----+-------+ | id | type | +----+-------+ | 1 | Root | +----+-------+ 解释：如果树中只有一个节点，则只需要输出其根属性。
首先取出null值判定为root根节点，再去p_id中取id，这些id代表他们存在叶子节点 标记为inner剩下的就是leaf
select
id as Id,
case when
tree.id = (select a.id from tree a where a.p_id is null) then 'Root'
when tree.id in (select b.p_id from tree b) then 'Inner'
else 'Leaf' end
as type
from tree
扫一扫
86
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
