LeetCode Sql笔记3

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';

1.  使用窗口函数把每个员工所在的部门数加在每一行数据上（相当于在原表中加多了一列）。
2. 如果只有一个部门的话这个部门肯定是直属部门，如果有多个部门就找出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;

1. 当任意两边之和大于第三边的时候可以组成三角形 

---

 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;

1. 很好理解直接把表连接三次。
2. 但是有一个问题，在现实业务中如果是要我们找连续出现多次的数据呢？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;

1. 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;

1.  先搜索出在8.16前修改过价格的所有产品的id和最新修改日期。
2. 通过原表找出对应修改过价格的产品的最新价格。
3. 通过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

1. 先搜索原表，增加filter_date字段，大于8.16才修改的产品此字段设为null否则就是和原表一样的修改日期。
2. 增加ranking字段，通过rank()窗口函数对产品id分组并根据filter_date字段降序排序。
3. 这样直接将各个产品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;

1.  通过sum的窗口函数给原表添加一个sum_weight的字段来计算weight的累加。
2. 找出累加值小于1000的行并根据turn字段降序排序，limit只取第一行就是最后一个上车的人。

---