心得
題目要求找出溫度比前一天高的資料,這裡用了DATE_SUB
與TO_DAYS
兩種方式來解答。
題目
Given a
Weather
table, write a SQL query to find all dates’ Ids with higher temperature compared to its previous (yesterday’s) dates.
12345678 +---------+------------+------------------+| Id(INT) | Date(DATE) | Temperature(INT) |+---------+------------+------------------+| 1 | 2015-01-01 | 10 || 2 | 2015-01-02 | 25 || 3 | 2015-01-03 | 20 || 4 | 2015-01-04 | 30 |+---------+------------+------------------+For example, return the following Ids for the above Weather table:
123456 +----+| Id |+----+| 2 || 4 |+----+
答案
Sub Query
123456# Write your MySQL query statement belowSELECT a.IdFROM Weather AS aWHERE ( SELECT z.TemperatureFROM Weather AS zWHERE DATE_SUB(a.Date, INTERVAL 1 DAY) = z.Date) < a.TemperatureJoin
123456# Write your MySQL query statement belowSELECT a.IdFROM Weather AS aJOIN Weather AS bON DATE_SUB(a.Date, INTERVAL 1 DAY) = b.DateWHERE a.Temperature > b.TemperatureTO_DAYS
123456# Write your MySQL query statement belowSELECT a.IdFROM Weather AS aJOIN Weather AS bON TO_DAYS(a.Date) - TO_DAYS(b.Date) = 1WHERE a.Temperature > b.Temperature
參考: