周一,总是容易犯困。。。不过下班啦,利用好自己的专属数据吧
今天的专项训练只有两道题,还是和合并相关,对于我而言,无论时规划为哪一个小类,掌握并理解相关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' )
我写SQL一般是习惯从内往外写,先解决一个子查询,然后套上外部框架逻辑进行核实,相当于是嵌套啦。
–疲惫但美好的一天结束啦
–晚安~