leetcode考试题
考试题1 262. 行程和用户 leetcode
+-------------+----------+ | 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
解答：
# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;
SELECT
Request_at 'Day',
round ( sum(case when status != 'completed' then 1 else 0 end ) /count(1) ,2) 'Cancellation Rate'
FROM Trips t
JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No')
JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No')
WHERE
Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
Request_at;
#对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止
#筛选订单日期在目标日期之间（BETWEEN AND）
#用日期进行分组（GROUP BY）
#分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值
#对订单取消率保留两位小数，对输出列名改名。（round）
SELECT
Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate'
FROM Trips t
JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No')
JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No')
WHERE
Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY
Request_at;
# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;# Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;
SELECT Request_at 'Day', round ( sum(case when status != 'completed' then 1 else 0 end ) /count(1) ,2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at; 方法1 如上 方法2 如下 # Write your MySQL query statement below #对Trips表和Users表连接，连接条件是行程对应的乘客非禁止且司机非禁止 #筛选订单日期在目标日期之间（BETWEEN AND） #用日期进行分组（GROUP BY） #分别统计所有订单数和被取消的订单数，其中取消订单数用一个bool条件来得到0或1，再用avg求均值 #对订单取消率保留两位小数，对输出列名改名。（round） SELECT Request_at 'Day', round(avg(Status!='completed'), 2) 'Cancellation Rate' FROM Trips t JOIN Users u1 ON (t.Client_id = u1.Users_id AND u1.Banned = 'No') JOIN Users u2 ON (t.Driver_id = u2.Users_id AND u2.Banned = 'No') WHERE Request_at BETWEEN '2013-10-01' AND '2013-10-03' GROUP BY Request_at;
avg(Status!='completed') 是一个 SQL 表达式，用于计算布尔条件的平均值。在这个表达式中：
Status != 'completed'是一个布尔条件，它返回TRUE或FALSE。在 SQL 中，TRUE通常被当作 1，FALSE被当作 0。avg()函数计算传入表达式的平均值。
所以，avg(Status!='completed') 实际上是计算 Status 列中不等于 'completed' 的记录的比例。例如，如果有 30% 的记录的 Status 不是 'completed'，那么结果就是 0.3。
考试 题2 SQL219 获取员工其当前的薪水比其manager当前薪水还高的相关信息
描述 有一个，部门关系表dept_emp简况如下: emp_no dept_no from_date to_date 10001 d001 1986-06-26 9999-01-01 10002 d001 1996-08-03 9999-01-01 有一个部门经理表dept_manager简况如下: dept_no emp_no from_date to_date d001 10002 1996-08-03 9999-01-01 有一个薪水表salaries简况如下: emp_no salary from_date to_date 10001 88958 2002-06-22 9999-01-01 10002 72527 1996-08-03 9999-01-01 获取员工其当前的薪水比其manager当前薪水还高的相关信息， 第一列给出员工的emp_no， 第二列给出其manager的manager_no， 第三列给出该员工当前的薪水emp_salary, 第四列给该员工对应的manager当前的薪水manager_salary 以上例子输出如下: emp_no manager_no emp_salary manager_salary 10001 10002 88958 72527
SELECT
m.emp_no,
m.manager_no,
n.salary emp_salary,
o.salary manager_salary
FROM
(
SELECT
a.emp_no,
b.emp_no manager_no
FROM
dept_emp a
INNER JOIN dept_manager b ON a.dept_no = b.dept_no
AND a.emp_no <> b.emp_no
) m
INNER JOIN salaries n ON m.emp_no = n.emp_no
INNER JOIN salaries o ON m.manager_no = o.emp_no
where n.salary emp_salary > o.salary manager_salary
**查询员工当前工资表 emp_sal**
select de.emp_no,de.dept_no,s1.salary as emp_salary
from dept_emp de,salaries s1
where de.emp_no=s1.emp_no
and s1.to_date='9999-01-01'
and de.to_date='9999-01-01'
**查询经理当前工资表mag_sal**
select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
from dept_manager dm,salaries s2
where dm.emp_no=s2.emp_no
and s2.to_date='9999-01-01'
and dm.to_date='9999-01-01'
**联结表emp_sal和表mag_sal，连接条件部门编号相等，要求：员工工资>经理工资**
select emp_sal.emp_no,mag_sal.manager_no,
emp_sal.emp_salary,mag_sal.manager_salary
from (
select de.emp_no,de.dept_no,s1.salary as emp_salary
from dept_emp de,salaries s1
where de.emp_no=s1.emp_no
and s1.to_date='9999-01-01'
and de.to_date='9999-01-01'
)as emp_sal
inner join(
select dm.emp_no as manager_no,dm.dept_no,s2.salary as manager_salary
from dept_manager dm,salaries s2
where dm.emp_no=s2.emp_no
and s2.to_date='9999-01-01'
and dm.to_date='9999-01-01'
)as mag_sal
on emp_sal.dept_no=mag_sal.dept_no
where mag_sal.manager_salary<emp_sal.emp_salary;
180. 连续出现的数字
+-------------+---------+ | 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 是唯一连续出现至少三次的数字。
---# 1. 三表直接连接 select distinct l1.num as ConsecutiveNums from Logs as l1, Logs as l2, Logs as l3 where l1.id = l2.id - 1 and l1.id = l3.id - 2 and l1.num = l2.num and l2.num = l3.num # 2. 利用窗口函数求出diff后进行分组，再用having子句过滤 select distinct num as ConsecutiveNums from (select num, id + 1 - row_number() over(partition by num order by id) as diff from Logs) t group by num, diff having count(*) >= 3 # 3. 如果id不是连续的，要求按照表中连续3个以上的记录 select distinct num as ConsecutiveNums from (select num, (row_number() over(order by id) - row_number() over(partition by num order by id)) as diff from Logs) t group by num, diff having count(*) >= 3 SELECT DISTINCT NUM AS CONSECUTIVENUMS FROM ( SELECT NUM, ID - ROW_NUMBER() OVER ( PARTITION BY NUM ORDER BY ID ) AS DIFF FROM LOGS ) T GROUP BY NUM, DIFF HAVING COUNT(*) >= 3
