[MySQL] select(4) - sub query, case문, 기타함수

💖 sub query

query 안에 query
한 개의 행(row, record)에서 결과가 반환되는 query

• select 한 개 row 한 개 column
• from 다중 row 다중 column
• where 다중 row 다중 column

🔥 select

: 한 개 row 한 개 column

select employee_id, first_name,
	( select last_name
      from employees
      where employee_id = 100 )	-- 사원번호가 100인 사원의 성. 결과값: King
from employees;

-- 사원의 정보 + 월급의 합계, 평균
select first_name, salary,
	(select sum(salary) from employees) as 급여합계,
    (select avg(salary) from employees) as 급여평균,
from employees;  

select department_id, first_name, salary,
    (select avg(salary) from employees) as 급여평균
from employees
where department_id = 30;

🔥 from

: 다중 row 다중 column

-- 부서번호가 50인 사원들 중 월급이 5000초과인 사원의 사원번호, 성명, 월급
select employee_id, first_name, salary
from (	select employee_id, first_name, salary, last_name
		from employees
        where department_id = 50) as e;	-- mysql에서는 sub query에 alias를 반드시 붙여 줘야 함
where salary > 5000;  

-- 업부별로 급여의 합계, 인원수, 사원명, 월급
select sum(salary), count(employee_id), first_name, salary
from employees
group by job_id;	-- 에러

select e.job_id, j.salsum, j.empcnt, e.first_name, e.salary
from employees e, (	select job_id as jobno, sum(salary) as salsum, count(employee_id) as empcnt
					from employees
					group by job_id) j;

🔥 where

: 다중 row 다중 column

-- 평균급여보다 많이 받는 사원
select employee_id, first_name, salary
from employees
where salary > (select avg(salary) from employees);

-- 부서번호가 90인 사원의 업무
select department_id, job_id
from employees
where job_id in (	select job_id 
					from employees
                    where department_id = 90);

-- 부서별로 가장 급여를 적게 받는 사원과 같은 급여를 받는 사원
select first_name, salary
from employees
where salary in (select min(salary) 
				from employees 
                where department_id);
                
-- 부서별로 가장 급여를 적게 받는 사원
select first_name, salary
from employees
where (department_id, salary) in (	select department_id, min(salary)
									from employees
                                    where department_id);
order by department_id;                                    

💖 case문

🔥 형식

case
	when 조건1 then 결과1
    when 조건2 then 결과2
    when 조건3 then 결과3
    else 결과4
end

🔥 예시

select employee_id, first_name, phone_number,
	case substr(phone_number, 1, 3)
    	when '515' then '뉴욕'
        when '590' then '워싱턴'
        when '650' then '보스턴'
        else '기타'
	end as 지역
from employees;    

💖 기타함수

🔥 substr

문자열을 추출할 수 있다

substr(대상문자열, 시작위치, 길이);

select substr('hello', 1, 4);	-- 1번지부터 4글자

🔥 over

group by를 보강하기 위해 나온 함수
합계나 평균등 집계함수의 결과를 group by 없이 출력할 수 있다

select department_id, count(*)	-- error
from employees;

select department_id, count(*)over()	-- 그룹으로 만들어주지 않아도 정상적으로 출력됨
from employees;

-- partition by == select절 안의 group by
-- over함수 안에서 사용
select department_id, first_name, salary,
	count(*)over(partition by department_id),
    avg(salary)over(partition by department_id)
from employees;    

🔥 분석함수(순위함수)

결과에 순번, 순위를 매기는 함수

rank() : 동등 순위 번호는 같게 나오고 다음 순위는 그 다음 번호를 제외한 다음 번호로 출력
dense_rank() : 동등 순위 번호는 같게 나오고 다음 순위는 다음 번호로 출력
row_number() : 매번 증가되는 순위를 출력
rownum

select employee_id, first_name, salary,
	rank()over(order by salary desc) as "rank",
    dense_rank()over(order by salary desc) as "dense_rank",
    row_number()over(order by salary desc) as "row_number"
from employees;    

-- console
/*
rank dense_rank row_number
 1	  1			 1
 2	  2			 2
 2	  2			 3
 4	  3			 4
        :
*/

🔥 limit

한계를 지정하는 함수. 시작위치(0 ~), 갯수

select first_name, salary
from employees
order by salary desc
limit 0, 10;	-- 0부터 시작해서 10갸(0~10)