题一:连续出现的数字
表: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 l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
;
任意连续n问题:
SELECT DISTINCT
ConsecutiveNums
FROM
(
SELECT
num AS ConsecutiveNums,
id,
CAST(rk AS signed) rk
-- 因为rank、row_number、dense_rank都会产生无字符值(即正值),
-- 而用无字符值返回的结果的类型也将相应的被限制为无字符值类型,
-- 需要使用cast(rank as signed)调整为有符号的数值。
FROM
(
SELECT
id,
num,
row_number() OVER(PARTITION BY num ORDER BY id) rk
FROM
Logs
)t1
)t2
GROUP BY
ConsecutiveNums,id-rk
HAVING
COUNT(id-rk)>=3
题二:指定日期的产品价格
产品数据表: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ (product_id, change_date) 是此表的主键(具有唯一值的列组合)。 这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
编写一个解决方案,找出在 2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是 10
。
以 任意顺序 返回结果表。
结果格式如下例所示。
示例 1:
输入: Products 表: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ 输出: +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+
方法(left join
和 ifnull
):
select p1.product_id, ifnull(p2.new_price, 10) as price
from (
select distinct product_id
from products
) as p1 -- 所有的产品
left join (
select product_id, new_price
from products
where (product_id, change_date) in (
select product_id, max(change_date)
from products
where change_date <= '2019-08-16'
group by product_id
)
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id
题三:最后一个能进入巴士的人
表: Queue
+-------------+---------+ | Column Name | Type | +-------------+---------+ | person_id | int | | person_name | varchar | | weight | int | | turn | int | +-------------+---------+ person_id 是这个表具有唯一值的列。 该表展示了所有候车乘客的信息。 表中 person_id 和 turn 列将包含从 1 到 n 的所有数字,其中 n 是表中的行数。 turn 决定了候车乘客上巴士的顺序,其中 turn=1 表示第一个上巴士,turn=n 表示最后一个上巴士。 weight 表示候车乘客的体重,以千克为单位。
有一队乘客在等着上巴士。然而,巴士有1000
千克 的重量限制,所以其中一部分乘客可能无法上巴士。
编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客,并报告 person_name
。题目测试用例确保顺位第一的人可以上巴士且不会超重。
返回结果格式如下所示。
示例 1:
输入: Queue 表 +-----------+-------------+--------+------+ | person_id | person_name | weight | turn | +-----------+-------------+--------+------+ | 5 | Alice | 250 | 1 | | 4 | Bob | 175 | 5 | | 3 | Alex | 350 | 2 | | 6 | John Cena | 400 | 3 | | 1 | Winston | 500 | 6 | | 2 | Marie | 200 | 4 | +-----------+-------------+--------+------+ 输出: +-------------+ | person_name | +-------------+ | John Cena | +-------------+ 解释: 为了简化,Queue 表按 turn 列由小到大排序。 +------+----+-----------+--------+--------------+ | Turn | ID | Name | Weight | Total Weight | +------+----+-----------+--------+--------------+ | 1 | 5 | Alice | 250 | 250 | | 2 | 3 | Alex | 350 | 600 | | 3 | 6 | John Cena | 400 | 1000 | (最后一个上巴士) | 4 | 2 | Marie | 200 | 1200 | (无法上巴士) | 5 | 4 | Bob | 175 | ___ | | 6 | 1 | Winston | 500 | ___ | +------+----+-----------+--------+--------------+
方法(根据 turn
排序, 并累加 weight
,找到最后一个使得总和小于等于 1000 的 person_name
):
SELECT a.person_name
FROM Queue a, Queue b
WHERE a.turn >= b.turn
GROUP BY a.person_id HAVING SUM(b.weight) <= 1000
ORDER BY a.turn DESC
LIMIT 1