[SQL] LeetCode: SELF JOIN 문제

181. Employees Earning More Than Their Managers

 

문제 

 

Write an SQL query to find the employees who earn more than their managers.

Return the result table in any order.

 

 

풀이

 

SELECT a.name AS Employee
FROM employee AS a
    LEFT JOIN employee AS b ON a.managerid = b.id
WHERE a.salary > b.salary

 

---

 

197. Rising Temperature

 

문제

 

Write an SQL query to find all dates' Id with higher temperatures compared to its previous dates (yesterday).

Return the result table in any order.

 

 

풀이

SELECT today.id
FROM Weather AS today
    LEFT JOIN Weather AS yesterday 
    ON today.recordDate = DATE_ADD(yesterday.recordDate, INTERVAL 1 DAY)
WHERE today.temperature > yesterday.temperature

 

# 셀프조인하면  똑같은 변수명이 2개가 됨 ->  alias를 잘 정의해야 함! 

 

# DATE_ADD (기준날짜, INTERVAL )

날짜는 +/- 연산 안됨 -> 특정 함수 사용(DATE_ADD, DATE_SUB)