Multi Table JOIN
- 3개 이상의 테이블(뷰)을 가지고 JOIN
응용
107명의 사원 나타내기
----------------------------------------------------- 부서번호 부서명 부서주소 부서장성명 사원번호 ----------------------------------------------------- 사원명 성별 나이 연봉 연봉소득세액 ----------------------------------------------------- 부서내연봉평균차액 부서내연봉등수 전체연봉등수 ------------------------------------------------------
-- 부서번호 : employees, departments => department_id
-- 부서명 : departments => department_name
-- 부서주소 : departments join locations
-- => street_address, city, state_province
-- 부서장성명 : employees join departments
-- 사원번호, 사원명, 연봉 : employees
-- 성별, 나이 : employees 통해 구하기
-- 연봉소득세액 : employees join tbl_taxindex
-- 부서내연봉평균차액 : 평균 -> 그룹함수
-- -> group by ~ employees
-- 부서내연봉등수, 전체연봉등수 : employees
WITH
DEPT AS
(
-- [부서번호, 부서명, 부서주소, 부서장성명]
select D.DEPARTMENT_ID -- 부서번호
, department_name -- 부서명
, STREET_ADDRESS || CITY || STATE_PROVINCE
as department_address -- 부서주소
, E.FIRST_NAME || ' ' || E.LAST_NAME
as manager_name -- 부서장성명
from departments D
JOIN locations L
ON D.LOCATION_ID = L.LOCATION_ID
JOIN EMPLOYEES E
ON D.MANAGER_ID = E.EMPLOYEE_ID
)
, EMP AS
(
-- [사원번호, 사원명, 연봉, 성별, 나이,
-- 연봉소득세액,부서내연봉등수, 전체연봉등수]
select DEPARTMENT_ID
, EMPLOYEE_ID -- 사원번호
, fullname -- 사원명
, year_money -- 연봉
, gender -- 성별
, case when this_year_birthday
to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd')
then extract(year from sysdate) - birthyear - 1
else extract(year from sysdate) - birthyear
end as age -- 나이
, year_tax -- 연봉소득세액
, dept_rank -- 부서내연봉등수
, total_rank -- 전체연봉등수
FROM (
select DEPARTMENT_ID, employee_id
, FIRST_NAME || ' ' || LAST_NAME
as fullname
, nvl(salary + (salary * COMMISSION_PCT)
, salary) * 12 as year_money
, case when substr(jubun,7,1) in('1','3')
then '남' else '여' end as gender
, to_date(to_char(sysdate,'yyyy')
|| substr(jubun,3,4),'yyyymmdd')
as this_year_birthday
, case when substr(jubun,7,1) in('1','2')
then '19' else '20' end
|| substr(jubun,1,2) as birthyear
, nvl(salary + (salary * COMMISSION_PCT)
, salary) * 12 * taxpercent
as year_tax
, rank() over (partition by DEPARTMENT_ID
order by nvl(salary + (salary
* COMMISSION_PCT), salary)
* 12 desc) as dept_rank
, rank() over (order by nvl(salary
+ (salary * COMMISSION_PCT), salary) * 12
desc) as total_rank
from employees JOIN tbl_taxindex
ON nvl(salary + (salary * COMMISSION_PCT)
, salary) * 12
between lowerincome and highincome
) EMP_TAX
)
, AVG_MONEY AS
(
-- [부서내연봉평균차액]
select DEPARTMENT_ID
, avg(nvl(salary + (salary * COMMISSION_PCT), salary)
* 12) as avg_dept_money
from employees
group by department_id
)
-- DEPT, EMP, AVG_MONEY JOIN
select DEPT.DEPARTMENT_ID as 부서번호
, DEPT.DEPARTMENT_NAME as 부서명
, DEPT.department_address as 부서주소
, DEPT.manager_name as 부서장성명
, EMP.EMPLOYEE_ID as 사원번호
, EMP.fullname as 사원명
, EMP.gender as 성별
, EMP.age as 나이
, to_char(EMP.year_money,'999,999') as 연봉
, to_char(EMP.year_tax,'99,999') as 연봉소득세액
, to_char(
EMP.year_money - AVG_MONEY.avg_dept_money
,'999,999') as 부서내연봉평균차액
, EMP.dept_rank as 부서내연봉등수
, EMP.total_rank as 전체연봉등수
from DEPT RIGHT JOIN EMP
ON DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
LEFT JOIN AVG_MONEY
ON EMP.DEPARTMENT_ID = AVG_MONEY.DEPARTMENT_ID
ORDER BY 부서번호, 사원번호, 연봉;정리
-> local_hr에서작업한것
