[DB & SQL] 그룹함수,서브쿼리

그룹함수

---

 all: 중복 포함 모두

distinct: 중복제거

[count 에 사용시]

count(distinct manage_id) 처럼 괄호 안에 선언 해줘야함

 

avg,sum 과 같은 그룹 함수는 NULL 값을 무시.

그렇기 때문에 바로 아래와 같이 값이 달라짐

 

 

제대로된 결과값을 연산하기 위해선 그룹함수에  nvl 을 통해서 null값을 0으로 수치화 해줘야함.

 

1.Group by

select department_id, sum(salary) from employees;

오류를 출력

select department_id, sum(salary)

from employees

group by department_id;

department_id 는 다중행을 출력하나  그룹함수인 sum은 열을 계산하여 단일 값을 출력하기 때문에

서로 맞지 않다 

이 때문에 department_id  다중행을 그룹화 시켜줘야함

 

@공식

avg,sum과 같은 그룹화 함수가 존재시 이 외의 다른 컬럼값들은  group by 절에 포함되어야한다.

 

[where  절은 행을 제한, 그룹절을 제한하는 조건절이 아니다.]

실행 순서가  from  다음으로 where절이 나온다. 이상황에서 avg라는 그룹절 또한 찾을수 없으며

그러기 때문에 아래 실행문 또한 오류 발생

 select department_id, avg(salary)

 from employees

where avg(salary)>8000

group by department_id;

아래와 같이 having을 이용하여 그룹절을 제한하여야함

select department_id, avg(salary)

from employees

group by department_id;

having avg(salary)>8000

 

서브쿼리

---

select last_name,salary
from employees
where salary>(select salary 
                    from employees
                    where last_name='Abel'
                              )

서브쿼리 절에서는  ORDER BY절을 사용하지 않는것이 좋음(성능상 유리)

서브쿼리 절은 출력절이 아니라 조건에 해당하는 값이기 때문에 정렬에 자원을 투자할 가치가 없음

단일 행을 반환하는 값에 서브쿼리에서는 단일행 연산자 사용 = < >복수 행은 복수행 서브쿼리 IN

 

ex)

select last_name, job_id

from employees

where job_id=

                          (select job_id

                            from employees

                            where last_name='Haas' );

 

서브쿼리가 널값을 반환하기 때문에 출력문에 0 row 출력됨

서브쿼리가 널값을 갖고있으면 안됨

 

ex) 사원 141과 동일한 업무  ID를 가진 사원을 표시하시오.

select last_name, job_id
from employees
where job_id=
                  (select  job_id
       from   employees
           where  employee_id=141);

 

where 절 뿐만 아닌  having 절에서도 서브쿼리를 반환

select nvl(department_id,0), min(salary)
from employees
group by department_id
having min(salary)>
                       (select min(salary)
                       from employees
                       where department_id=50);

ex) 질럿키와 동일한 부서에 근무하는 다른 모든 사원들의 사번 및 고용 날자를 출력하시오

select employee_id, hire_date
from employees
where department_id IN
                      (select department_id
                       from employees
                        where last_name='Zlotkey')
                          AND last_name!='Zlotkey'
/

 

ex)e시애틀에 근무하는 사람 중 커미션을 받지 않는 모든 사람드르이 이름,부서 명, 지역id를 출력하시오

select last_name, department_name, d.location_id
from employees e, departments d
where e.department_id=d.department_id
and d.location_id=(select location_id
                        from locations
                        where city='Seattle')
                        and e.commission_pct is null

any

 

in(30,40)   : 30, 40

30>any   : 최소값 30보다 큰 값  40 50 60

40<any   : 최대값 40보다 작은 값 10 20 30

40>all     :전부. 최대값 40보다 큰 값  50 60

30<all     :전부, 최소값 30보다 작은 값 10 20