LC_SQL_基础_DAY6

上升的温度

讨论 1

lag()和lead()这两个函数可以查询我们得到的结果集上下偏移相应行数的相应的结果。

lag()函数：
查询当前行向上偏移n行对应的结果，该函数有三个参数：第一个为待查询的参数列名，第二个为向上偏移的位数，第三个参数为超出最上面边界的默认值。

lead()函数：
查询当前行向下偏移n行对应的结果，该函数有三个参数：第一个为待查询的参数列名，第二个为向下偏移的位数，第三个参数为超出最下面边界的默认值。

DATEDIFF():

DATEDIFF() 函数返回两个日期之间的时间。

结果：day=DiffDate = enddate - startdate = 1day = DiffDate = enddate − startdate =1，把示例的day换成year，就是按年计算

select id
from
    (select 
        id,
        temperature,
        recordDate,
        lag(recordDate,1) over(order by recordDate) as last_date,
        lag(temperature,1) over(order by recordDate) as last_temperature
    from Weather) a
where temperature > last_temperature and datediff(recordDate, last_date) = 1

讨论 2

笛卡尔积

获取所有比前一天温度高的记录并记录其id
找相关联表 题干涉及到的表只有单表Weather

我们想要拿记录的温度与前一天进行相对比，那必然需要将前一天的记录放到一行中，不然单纯的select是无法进行比较的；所以想到用笛卡儿积，然后再通过前置知识进去过滤+温度条件

select 
    b.Id 
from 
    weather a 
inner join 
    weather b 
where 
    DATEDIFF(b.recordDate,a.recordDate)=1 
and b.Temperature > a.Temperature;

讨论 3

TIMESTAMPDIFF()
TIMESTAMPDIFF能干什么，可以计算相差天数、小时、分钟和秒，相比于datediff函数要灵活很多。格式是时间小的前，时间大的放在后面。 计算相差天数：

select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1 
AND w1.Temperature > w2.Temperature

讨论 4

adddate()函数

ADDDATE()函数用于将指定的时间间隔添加到日期值date是表示日期的值，它可以是 String、DATE（YEAR、MONTH 和 DAY）、DATETIME（HOURS、MINUTES 或 SECONDS）或 TIMESTAMP 类型。

ADDDATE( date , INTERVAL value addunit )

ADDDATE( date , days )

select a.id 
    from weather a join weather b 
    on (a.recorddate = adddate(b.recorddate,INTERVAL 1 day))
where a.temperature > b.temperature

讨论 5

外连接+子查询+Date_ADD()函数

select 
    w.Id
from Weather w
join (
    select 
        RecordDate,Temperature
    from 
        Weather
) t1
on w.RecordDate = DATE_ADD(t1.RecordDate,INTERVAL 1 day)
where w.Temperature > t1.Temperature;

销售员

讨论 1

方法：使用 OUTER JOIN 和 NOT IN [Accepted]

如果我们知道向 RED 公司销售东西的人，那么我们要知道没有向 RED 公司销售东西的人会非常容易。

首先，我们用一个临时表保存向 RED 公司销售过东西的人，然后利用姓名信息将这个表和 salesperson 表建立联系。

SELECT
    *
FROM
    orders o
        LEFT JOIN
    company c ON o.com_id = c.com_id
WHERE
    c.name = 'RED'
;

注意： “LEFT OUTER JOIN” 也可以写作 “LEFT JOIN” 。

| order_id | date     | com_id | sales_id | amount | com_id | name | city   |
|----------|----------|--------|----------|--------|--------|------|--------|
| 3        | 3/1/2014 | 1      | 1        | 50000  | 1      | RED  | Boston |
| 4        | 4/1/2014 | 1      | 4        | 25000  | 1      | RED  | Boston |

显然，列 sales_id 在 salesperson 中，所以我们把它当做子查询并使用 NOT IN 获得想要的数据。

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