[leetcode] 175 ~ 197

175. Combine Two Tables

select firstName, lastName, city, state from Person A
left join Address B on A.personId = B.personId

 

Mysql은 full outer join이 안된다. -> union으로 하기

(SELECT ... FROM t1 LEFT JOIN t2 ON t1.name = t2.name)
UNION
(SELECT ... FROM t2 LEFT JOIN t1 ON t1.name = t2.name)

 

176. Second Highest Salary

select if(count(*) = 1, salary, null) as SecondHighestSalary
from 
    (select *
    from Employee
    group by salary
    order by salary desc
    limit 1, 1) A;

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee)


SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

 

177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
        SELECT e1.Salary
              FROM (SELECT DISTINCT Salary FROM Employee) e1
              WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) e2 WHERE e2.Salary > e1.Salary) = N - 1      

              LIMIT 1
  );
END

limit으로 정렬하고 싶다면?

That is because LIMIT cannot recognize expressions with arithmetic operators. You have to do the math before LIMIT

 

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

 

178. Rank Scores

SELECT Score, DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
FROM Scores
ORDER BY 'Rank'

Because Rank is a function, you cannot use it as a column alias. Hence 'Rank'

https://m.blog.naver.com/gglee0127/221318160003

[Oracle] 순위를 반환하는 함수 (RANK, DENSE_RANK, ROW_NUMBER)

 

180. Consecutive Numbers

select distinct(A.num) as ConsecutiveNums
from Logs A, Logs B, Logs C
where A.num = B.num and A.id = B.id+2 and 
    A.num = C.num and A.id = C.id+1
    and B.id != C.id# and A.id = B.id

 

184. Department Highest Salary

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;

 

185. Department Top Three Salaries

# Write your MySQL query statement below
select D.name as Department,
        A.name as Employee,
        A.salary as Salary
from 
    (select name, departmentId, salary, dense_rank() over (partition by departmentId order by salary desc) as deptrank
    from employee) A join
    Department D
    on A.departmentId = D.id
where A.deptrank<=3

 

181. Employees Earning More Than Their Managers

select A.name as Employee
from Employee A
left join Employee B
on A.managerID = B.id
where A.salary > B.salary;

 

182. Duplicate Emails

select email as Email
from Person
group by email
having count(*)>=2

 

183. Customers Who Never Order

select name as Customers
from Customers
left join Orders
on Customers.id = Orders.customerId
where Orders.customerId is null

 

196. Delete Duplicate Emails

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
delete p1 from Person p1, Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

 

197. Rising Temperature

# Write your MySQL query statement below
select p1.id as 'Id'
from Weather p1 join
Weather p2
on DATEDIFF(p1.recordDate, p2.recordDate) = 1 and p1.Temperature > p2.Temperature



SELECT DISTINCT a.Id
FROM Weather a,Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate,b.Recorddate) = 1