時(shí)間常用api
返回從0000年到現(xiàn)在的天數(shù)
to_days("2015-01-04")
將時(shí)間/日期間隔添加到日期
adddate("2015-01-03",INTERVAL 1 day) #2015-01-04
TIMESTAMPDIFF
#計(jì)算相差天數(shù)：
select TIMESTAMPDIFF(DAY,'2019-05-20', '2019-05-21'); # 1
#計(jì)算相差小時(shí)數(shù)：
select TIMESTAMPDIFF(HOUR, '2015-03-22 07:00:00', '2015-03-22 18:00:00'); # 11
#計(jì)算相差秒數(shù)：
select TIMESTAMPDIFF(SECOND, '2015-03-22 07:00:00', '2015-03-22 7:01:01'); # 61
返回值是相差的天數(shù)
DATEDIFF('2007-12-31','2007-12-30'); # 1
DATEDIFF('2010-12-30','2010-12-31'); # -1
從日期減去指定的時(shí)間間隔
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滿足條件的才會(huì)去顯示
-- 外連接(outer join)
- 如果數(shù)據(jù)不存在，也會(huì)出現(xiàn)在連接結(jié)果中。
-- 左外連接 left join
如果數(shù)據(jù)不存在，左表記錄會(huì)出現(xiàn)，而右表為null填充
-- 右外連接 right join
如果數(shù)據(jù)不存在，右表記錄會(huì)出現(xiàn)，而左表為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;
