【Leecode-专项突破-SQL入门】day6-CSDN博客

本文链接：https://blog.csdn.net/qq_41184032/article/details/126474050

周一，总是容易犯困。。。不过下班啦，利用好自己的专属数据吧
今天的专项训练只有两道题，还是和合并相关，对于我而言，无论时规划为哪一个小类，掌握并理解相关SQL语法即这段逻辑到底对每行数据做了什么，在解题或是处理具体问题上，会有很好的帮助。
总共 2 道题：2 简单
197.上升的温度

输入：
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  |
+----+

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。返回结果不要求顺序。
对于这类需要在原表同一列上进行数值比较的题目，我强烈推荐使用窗口分析函数 lead() 或 lag()。简单易懂无痛苦，安全快捷且高效。
（1）使用 lead() 函数

select t.next_id as 'id' from (
select *
  ,lead(id,1) over(order by recordDate) as 'next_id'
  ,lead(recordDate,1) over(order by recordDate) as 'next_dt'
  ,lead(Temperature,1) over(order by recordDate) as 'next_tmp'
  from Weather ) t
  where t.Temperature < t.next_tmp and datediff(recordDate,next_dt) = -1;

我们可以了解一下 lead() 函数对原表做了哪些处理。下图中，lead() 函数将 Weather 表中字段 id，recordDate，Temperature 都相对于原表提升了一行（在一个按照 recordDate 排序的窗口内）。因此依据下图，可以清晰的写出符合题意的SQL逻辑语句：

+----+------------+-------------+---------+------------+----------+
| id | recordDate | Temperature | next_id |   next_dt  | next_tmp |
+----+------------+-------------+---------+------------+----------+
| 1  | 2015-01-01 | 10          | 2       | 2015-01-02 | 25       |
| 2  | 2015-01-02 | 25          | 3       | 2015-01-03 | 20       |
| 3  | 2015-01-03 | 20          | 4       | 2015-01-04 | 30       |
| 4  | 2015-01-04 | 30          | null    | null       | null     |
+----+------------+-------------+---------+------------+----------+

（2）使用 lag() 函数

select t.id from (
select *
  ,lag(recordDate,1) over(order by recordDate) as 'before_dt'
  ,lag(Temperature,1) over(order by recordDate) as 'before_tmp'
  from Weather ) t
  where t.Temperature > t.before_tmp and datediff(recordDate,before_dt) = 1

与 lead() 类似，下图中，lag() 函数将 Weather 表中字段 id，recordDate，Temperature 都相对于原表下降了一行（在一个按照 recordDate 排序的窗口内）。

+----+------------+-------------+--------------------------+------------+-------------+
| id | recordDate | Temperature | (此列SQL未查询，只方便演示) | before_dt  | before_tmp  |
+----+------------+-------------+--------------------------+------------+-------------+
| 1  | 2015-01-01 | 10          | null                     | null       | null        |
| 2  | 2015-01-02 | 25          | 1                        | 2015-01-01 | 10          |
| 3  | 2015-01-03 | 20          | 2                        | 2015-01-02 | 25          |
| 4  | 2015-01-04 | 30          | 3                        | 2015-01-03 | 20          |
+----+------------+-------------+--------------------------+------------+-------------+

官方解法是利用自链接，依据链接条件，返回对应id。测试环境下数据量不大，可以作为一种解决方法。这里引入 datediff() 函数，对日期进行求差作为判断条件。下面两种解法殊途同归：
（1）where 外部过滤条件

select w2.id
  from Weather w1 join Weather w2
  on datediff(w2.recordDate,w1.recordDate) = 1
  where w2.Temperature > w1.Temperature

（2）and 内部关联条件

select w2.id
  from Weather w1 join Weather w2
  on datediff(w2.recordDate,w1.recordDate) = 1
  and w2.Temperature > w1.Temperature

607.销售员

输入：
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   |
+----------+------+--------+-----------------+------------+
#sales_id 是该表的主键列

Company 表:
+--------+--------+----------+
| com_id | name   | city     |
+--------+--------+----------+
| 1      | RED    | Boston   |
| 2      | ORANGE | New York |
| 3      | YELLOW | Boston   |
| 4      | GREEN  | Austin   |
+--------+--------+----------+
#com_id 是该表的主键列

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  |
+----------+------------+--------+----------+--------+
#order_id 是该表的主键列。
#com_id 是 Company 表中 com_id 的外键
#sales_id 是来自 SalesPerson 表 sales_id 的外键

输出：
+------+
| name |
+------+
| Amy  |
| Mark |
| Alex |
+------+

编写一个SQL查询，报告没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。以任意顺序返回结果表
题目意思很绕，但是理一理会发现逻辑很简单，翻译为SQL语义就是需要进行多表关联。

select s.name
  from SalesPerson s where s.sales_id not in (
select o.sales_id
  from Orders o left join Company c on o.com_id = c.com_id
  where c.name = 'RED' )