< MEDIUM >
second highest salary
•
dense_rank()
select if(count(*)>=1,a.salary,null) as SecondHighestSalary
from (
select *,
dense_rank() over(order by salary desc) as rnk
from employee
) a
where a.rnk = 2
;
SQL
복사
•
limit [offset] : 출력되는 행 갯수 지정
◦
limit 출력할 행 갯수 [offset 시작행];
◦
첫 행은 0부터 시작이다.
◦
limit 2,3; → 3번째, 4번째 행만 출력
nth highest salary
CREATE FUNCTION getNthHighestSalary(
N INT # 파라미터 선언
) RETURNS INT # 반환할 데이터 타입
BEGIN # 함수 내용 시작
RETURN (
# Write your MySQL query statement below.
select if(count(*) >= 1,a.salary,null) as getNthHighestSalary
from (
select *,
dense_rank() over(order by salary desc) as rnk
from employee
) a
where rnk = N
);
END
SQL
복사
rank scores
select a.score
,a.rnk as 'rank'
from (
select *
,dense_rank() over(order by score desc) as rnk
from scores
) a
order by a.score desc;
SQL
복사
department highest salary
select d.name as 'Department'
,e.name as 'Employee'
,e.salary as 'Salary'
from employee e
left join department d on e.departmentId = d.id
where (e.departmentId, e.salary) in (
select departmentId
,max(salary) as max_salary
from employee
group by departmentId)
SQL
복사
tree node
•
case when 절 내의 서브쿼리
select id
,case when p_id is null then "Root"
when id in (select p_id from tree) then "Inner"
else "Leaf"
end as 'type'
from tree;
SQL
복사
exchange seats
select *
from (
# 짝수 번호일때
select id-1 as id
,student
from seat
where mod(id,2) = 0
union all
# 홀수 번호일때
select
case when id = (select max(id) from seat) then id
else id+1
end as 'id'
,student
from seat
where mod(id,2) = 1
) a
order by a.id
SQL
복사
다른 풀이•
전체 데이터 개수의 값을 나타내는 변수 추가 → inner join
select
(case
when mod(id,2) != 0 and counts != id then id+1
when mod(id,2) != 0 and counts = id then id
else id-1
end) as id
,student
from seat,
(select
count(*) as counts
from seat) as seat_counts
order by id;
SQL
복사
market analysis1
select a.user_id as buyer_id
,a.join_date
,sum(if(year(order_date)='2019',1,0)) as orders_in_2019
from users a
left join orders b on a.user_id = b.buyer_id
group by a.user_id, a.join_date;
SQL
복사
다른 풀이select u.user_id as buyer_id
,u.join_date
,count(o.order_id) as orders_in_2019
from users u
left join (
select *
from orders
where year(order_date) = 2019
) o on u.user_id = o.buyer_id
group by u.user_id, u.join_date
SQL
복사
capital gain/loss
select stock_name
,sum(if(operation = 'Buy',-price,price)) as capital_gain_loss
from stocks
group by stock_name;
SQL
복사