인프런 커뮤니티 질문&답변
작성한 질문수
구문 정리
작성
·
169
0
- 학습 관련 질문을 남겨주세요. 상세히 작성하면 더 좋아요!
- 먼저 유사한 질문이 있었는지 검색해보세요.
- 서로 예의를 지키며 존중하는 문화를 만들어가요.
- 잠깐! 인프런 서비스 운영 관련 문의는 1:1 문의하기를 이용해주세요.
with a as (select emp.*, row_number () over (partition by deptno order by sal desc) as high_sal
, row_number () over (partition by deptno order by sal) as low_sal
from emp),
b as (select *
from a where high_sal = 1 or low_sal = 1),
c as (select distinct a.*, case when a.high_sal=1 then '최고'
when a.low_sal =1 then '최저'
end as 급여수준
from a join b on a.deptno = b.deptno
where a.high_sal = 1 or a.low_sal =1),
d as (select deptno , max(sal) as max_sal, min(sal) as min_sal
from c group by deptno)
select c.*, d.max_sal - d.min_sal
from c join d on c.deptno = d.deptno
order by deptno
이 구문을 좀 더 깔끔하게 정리하고 싶은데 혹시 필요없는 절이라던가 생략 가능한 부분이 있을까요?
답변 1
0
아래 SQL은 어떤신지요?
with a as
(
select emp.*, row_number () over (partition by deptno order by sal desc) as high_sal
, row_number () over (partition by deptno order by sal) as low_sal
from emp
),
b as (
select deptno, max(sal) as max_sal_dept, min(sal) as min_sal_dept
from emp group by deptno
)
select a.*
, case when a.high_sal=1 then '최고'
when a.low_sal =1 then '최저' end as 급여수준
, (max_sal_dept - min_sal_dept) as sal_diff
from a join b on a.deptno = b.deptno
where a.high_sal = 1 or a.low_sal=1;