MySQL_Self JOIN

LeetCode : 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.

The query result format is in the following example.

 

 

 

SELECT Employee.name AS employee_name
     , Employee.salary AS employee_salary
     , Manager.name AS manager_name
     , Manager.salary AS manager_salary
FROM Employee 
    INNER JOIN Employee AS Manager ON Employee.managerId = Manager.id

ManagerID를 기준으로 해서 같은 테이블을 self join 한다.

 

employee_name	employee_salary	manager_name	manager_salary
Joe	70000	Sam	60000
Henry	80000	Max	90000

 

employee salary가 manager salary보다 커야 하므로 WHERE 조건절을 추가한다.

SELECT Employee.name AS employee
FROM Employee 
    INNER JOIN Employee AS Manager ON Employee.managerId = Manager.id
WHERE Employee.salary > Manager.salary

 

---

LeetCode : 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.

The query result format is in the following example.

 

 

풀이 과정

DATE TYPE을 기준으로 풀어야한다.

DATE_ADD(기준날짜, INTERVAL)

SELECT DATE_ADD(NOW( ), INTERVAL 1 SECOND)

SELECT DATE_ADD(NOW( ), INTERVAL 1 MINUTE)

SELECT DATE_ADD(NOW( ), INTERVAL 1 HOUR)

SELECT DATE_ADD(NOW( ), INTERVAL 1 DAY)

SELECT DATE_ADD(NOW( ), INTERVAL 1 MONTH)

SELECT DATE_ADD(NOW( ), INTERVAL 1 YEAR)

SELECT DATE_ADD(NOW( ), INTERVAL -1 MINUTE)

 

DATE_SUB(기준날짜, INTERVAL) : 빼줄 때(ADD에 -를 사용하는 것과 같음)

SELECT DATE_SUB(NOW( ), INTERVAL 1 SECOND)

 

---

SELECT Today.id
FROM Weather AS Today
    INNER JOIN Weather as Yesterday ON DATE_ADD(Yesterday.recordDate, INTERVAL 1 DAY) = Today.recordDate
WHERE Today.temperature > Yesterday.temperature