1789. 员工的直属部门
表:Employee
+---------------+---------+ | Column Name | Type | +---------------+---------+ | employee_id | int | | department_id | int | | primary_flag | varchar | +---------------+---------+ 这张表的主键为 employee_id, department_id (具有唯一值的列的组合) employee_id 是员工的ID department_id 是部门的ID,表示员工与该部门有关系 primary_flag 是一个枚举类型,值分别为('Y', 'N'). 如果值为'Y',表示该部门是员工的直属部门。 如果值是'N',则否
一个员工可以属于多个部门。当一个员工加入超过一个部门的时候,他需要决定哪个部门是他的直属部门。请注意,当员工只加入一个部门的时候,那这个部门将默认为他的直属部门,虽然表记录的值为'N'
.
请编写解决方案,查出员工所属的直属部门。
示例 1:
输入: Employee table: +-------------+---------------+--------------+ | employee_id | department_id | primary_flag | +-------------+---------------+--------------+ | 1 | 1 | N | | 2 | 1 | Y | | 2 | 2 | N | | 3 | 3 | N | | 4 | 2 | N | | 4 | 3 | Y | | 4 | 4 | N | +-------------+---------------+--------------+ 输出: +-------------+---------------+ | employee_id | department_id | +-------------+---------------+ | 1 | 1 | | 2 | 1 | | 3 | 3 | | 4 | 3 | +-------------+---------------+ 解释: - 员工 1 的直属部门是 1 - 员工 2 的直属部门是 1 - 员工 3 的直属部门是 3 - 员工 4 的直属部门是 3
select employee_id,department_id from
(select employee_id,department_id,primary_flag,count(department_id) over(partition by employee_id) as count_over from employee) t
where count_over=1 or primary_flag='Y';
- 使用窗口函数把每个员工所在的部门数加在每一行数据上(相当于在原表中加多了一列)。
- 如果只有一个部门的话这个部门肯定是直属部门,如果有多个部门就找出primary_flag为'Y'的列。
610. 判断三角形
表: Triangle
+-------------+------+ | Column Name | Type | +-------------+------+ | x | int | | y | int | | z | int | +-------------+------+ 在 SQL 中,(x, y, z)是该表的主键列。 该表的每一行包含三个线段的长度。
对每三个线段报告它们是否可以形成一个三角形。
以 任意顺序 返回结果表。
示例 1:
输入: Triangle 表: +----+----+----+ | x | y | z | +----+----+----+ | 13 | 15 | 30 | | 10 | 20 | 15 | +----+----+----+ 输出: +----+----+----+----------+ | x | y | z | triangle | +----+----+----+----------+ | 13 | 15 | 30 | No | | 10 | 20 | 15 | Yes | +----+----+----+----------+
select *,if(x+y>z and x+z>y and z+y>x,'Yes','No') as triangle
from triangle;
- 当任意两边之和大于第三边的时候可以组成三角形
180. 连续出现的数字
表: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 是唯一连续出现至少三次的数字。
1.官方解法
select l1.num as ConsecutiveNums
from logs l1,logs l2,logs l3
where l1.id+1=l2.id and l2.id+1=l3.id and l1.num=l2.num and l2.num=l3.num
group by ConsecutiveNums;
- 很好理解直接把表连接三次。
- 但是有一个问题,在现实业务中如果是要我们找连续出现多次的数据呢?30次,100次,把表连接30次100次吗(写死人了)。
2.连续n次问题通解
SELECT DISTINCT Num as ConsecutiveNums FROM (SELECT Id,Num, CAST(Id AS SIGNED) - CAST(ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Id) AS SIGNED) as SerialNumberSubGroup
FROM Logs) as Sub GROUP BY Num, SerialNumberSubGroup HAVING COUNT(1) >= 3;
- emmm,这种解法我也理解了很久 ,具体去leetcode的题解上面看看吧,链接我放出来180. 连续出现的数字 - 力扣(LeetCode)
https://leetcode.cn/problems/consecutive-numbers/solutions/21537/sql-server-jie-fa-by-neilsons/?envType=study-plan-v2&envId=sql-free-50(看懂之后真的觉得人与人之间是真的有差距的)
1164. 指定日期的产品价格
产品数据表: 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 | +------------+-------+
select b.product_id,ifnull(a.new_price,10) as price from (select distinct product_id from products)as b
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)) a
on b.product_id=a.product_id;
- 先搜索出在8.16前修改过价格的所有产品的id和最新修改日期。
- 通过原表找出对应修改过价格的产品的最新价格。
- 通过distinct 原表id然后left join,当new_price为null时代表没有修改过价格,设为10;
另一种解法 :
SELECT DISTINCT product_id, IF(filter_date IS NULL, 10, new_price) AS price
FROM (
SELECT *, RANK() OVER(PARTITION BY product_id ORDER BY filter_date DESC) AS RANKING
FROM (
SELECT *, IF(change_date > '2019-08-16', NULL, change_date) AS filter_date
FROM Products
) T
) TT
WHERE TT.RANKING = 1
- 先搜索原表,增加filter_date字段,大于8.16才修改的产品此字段设为null否则就是和原表一样的修改日期。
- 增加ranking字段,通过rank()窗口函数对产品id分组并根据filter_date字段降序排序。
- 这样直接将各个产品ranking=1(最新更新时间)的价格取出来,为空就代表8.16前没改过价格设为10。
1204. 最后一个能进入巴士的人
表: 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 | ___ | +------+----+-----------+--------+--------------+
select person_name from
(select person_id, person_name, turn, sum(weight) over(order by turn) as sum_weight from queue) t
where sum_weight<=1000
order by turn desc
limit 1;
- 通过sum的窗口函数给原表添加一个sum_weight的字段来计算weight的累加。
- 找出累加值小于1000的行并根据turn字段降序排序,limit只取第一行就是最后一个上车的人。