[MySQL] 컬럼 한 줄 밀어서 JOIN

테이블

Weather

id recordDate temperature
1 2015-01-01 10
2 2015-01-02 25
3 2015-01-03 20
4 2015-01-04 30

문제

  • 전날 보다 온도가 오른 튜플만 뽑으시오

답

SELECT w2.id
FROM Weather w1, Weather w2
WHERE w1.recordDate = DATE_SUB(w2.recordDate, INTERVAL 1 DAY)
AND w1.temperature < w2.temperature
;

스키마

Create table If Not Exists Weather (id int, recordDate date, temperature int);
Truncate table Weather;
insert into Weather (id, recordDate, temperature) values (1, '2015-01-01', 10);
insert into Weather (id, recordDate, temperature) values (2, '2015-01-02', 25);
insert into Weather (id, recordDate, temperature) values (3, '2015-01-03', 20);
insert into Weather (id, recordDate, temperature) values (4, '2015-01-04', 30);

links

social