2022-04-06 sql 时间 join not in

时间常用api

返回从0000年到现在的天数
to_days("2015-01-04")
将时间/日期间隔添加到日期
adddate("2015-01-03",INTERVAL 1 day) #2015-01-04
TIMESTAMPDIFF
#计算相差天数：
select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1

#计算相差小时数：
select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11

#计算相差秒数：
select TIMESTAMPDIFF(SECOND, '2015-03-22 07:00:00', '2015-03-22 7:01:01'); # 61
返回值是相差的天数
DATEDIFF('2007-12-31','2007-12-30');   # 1
DATEDIFF('2010-12-30','2010-12-31');   # -1
从日期减去指定的时间间隔
DATE_SUB("2008-12-29",INTERVAL 2 DAY) #2008-12-27
interval
"2015-01-03"+interval'1' day #2015-01-04

表示不等于尽可能用 where colume not in ，而不是!=

inner join满足条件的才会去显示

-- 外连接(outer join)
    - 如果数据不存在，也会出现在连接结果中。
    -- 左外连接 left join
        如果数据不存在，左表记录会出现，而右表为null填充
    -- 右外连接 right join
        如果数据不存在，右表记录会出现，而左表为null填充

607. 销售员

select s.name from SalesPerson s
where s.name not in
(
 select person.name
from SalesPerson person
inner join
(select o.com_id,o.sales_id,c.name
from Orders o
inner join Company c
on c.com_id=o.com_id)t
on t.sales_id=person.sales_id
where t.name = 'RED'
);

197. 上升的温度

select t1.id from Weather t1
inner join Weather t2
on DATEDIFF(t1.recordDate,t2.recordDate)=1
and t1.Temperature>t2.Temperature;