[SQL] 서브쿼리 임시컬럼을 통해 원하는 테이블 조회하기

아래 두 테이블처럼 가로로 원하는 컬럼이름에 값을 출력하여 가시성을 높이고 싶은 경우가 있다.

이때는 서브쿼리의 임시컬럼을 생성하여 데이터가 존재하는 컬럼만 뽑아내도록 만들어줘야 한다.

 

이를테면, KOR, USA에 해당되는 값을 제외한 나머지는 모두 0으로 두고, flag컬럼을 1로 만든다.

그리고나서 원하는 항목을 출력하면서 flag를 통해 그룹바이를해주면, 해당되는 로우만 추출되게 된다.

select "population" as "category", sum(ct.KOR) as KOR, sum(ct.USA) as USA
from (
      select if(code="KOR", population, 0) as KOR,
      if(code="USA", population, 0) as USA,
      1 as flag
      from country
) as ct
group by flag
union
select "gnp" as "category", sum(ct.KOR) as KOR, sum(ct.USA) as USA
from (
      select if(code="KOR", gnp, 0) as KOR,
      if(code="USA", gnp, 0) as USA,
      1 as flag
      from country
) as ct
group by flag;

 

좀 더 확실한 예시를 들어보자면,

아래 테이블처럼 가로로 만드는 것은 쉽다.

단순히 monthly컬럼으로 그룹바이를 해주면된다.

select date_format(payment_date, '%Y-%m') as monthly, sum(amount) as amount
from payment
group by monthly;

이제 위 테이블을 가로로 만드려면, 특별한 과정을 거쳐야하는데

먼저, 임시테이블을 만들어서 해당되는 컬럼에만 값이 들어가도록 만들고, tmp컬럼을 추가한다.

그리고, 위 값들에 맞는 date를 컬럼으로 지정하여 tmp에 대해서 그룹바이를 진행한다.

tmp에 대해서 그룹바이를 진행하면, 해당되는 값이 있는 열만 컬럼에 조회가 되게된다.

select sum(sub.date1) as "2005-05",
       sum(sub.date2) as "2005-06",
       sum(sub.date3) as "2005-07",
       sum(sub.date4) as "2005-08",
       sum(sub.date5) as "2006-02"
from (
      select sum(if(date_format(payment_date, '%Y-%m')="2005-05", amount, 0)) as date1,
             sum(if(date_format(payment_date, '%Y-%m')="2005-06", amount, 0)) as date2,
             sum(if(date_format(payment_date, '%Y-%m')="2005-07", amount, 0)) as date3,
             sum(if(date_format(payment_date, '%Y-%m')="2005-08", amount, 0)) as date4,
             sum(if(date_format(payment_date, '%Y-%m')="2006-02", amount, 0)) as date5,
             "tmp"
      from payment
      group by date_format(payment_date, "%Y-%m")
) as sub
group by tmp;

 

 

응용

더 복잡한 테이블을 가로로 만드는 것도 할 수 있다.

select date_format(payment_date, '%Y-%m') as monthly, sum(amount) as amount, count(rental_id) as rent
from payment
group by monthly;

select "amount" as "category",
       sum(sub1.date1) as "2005-05",
       sum(sub1.date2) as "2005-06",
       sum(sub1.date3) as "2005-07",
       sum(sub1.date4) as "2005-08",
       sum(sub1.date5) as "2006-02"
from (
      select
            sum(if(date_format(payment_date, '%Y-%m')="2005-05", amount, 0)) as date1,
            sum(if(date_format(payment_date, '%Y-%m')="2005-06", amount, 0)) as date2,
            sum(if(date_format(payment_date, '%Y-%m')="2005-07", amount, 0)) as date3,
            sum(if(date_format(payment_date, '%Y-%m')="2005-08", amount, 0)) as date4,
            sum(if(date_format(payment_date, '%Y-%m')="2006-02", amount, 0)) as date5,
            "tmp"
	  from payment
	  group by date_format(payment_date, "%Y-%m")
) as sub1
group by tmp
union
select "rent" as "category",
       sum(sub2.date1) as "2005-05",
       sum(sub2.date2) as "2005-06",
       sum(sub2.date3) as "2005-07",
       sum(sub2.date4) as "2005-08",
       sum(sub2.date5) as "2006-02"
from (
      select
            sum(if(date_format(payment_date, '%Y-%m')="2005-05", 1, 0)) as date1,
            sum(if(date_format(payment_date, '%Y-%m')="2005-06", 1, 0)) as date2,
            sum(if(date_format(payment_date, '%Y-%m')="2005-07", 1, 0)) as date3,
            sum(if(date_format(payment_date, '%Y-%m')="2005-08", 1, 0)) as date4,
            sum(if(date_format(payment_date, '%Y-%m')="2006-02", 1, 0)) as date5,
            "tmp"
      from payment
      group by date_format(payment_date, "%Y-%m")
) as sub2
group by tmp;

 

주로, 조회테이블을 출력할 때 사용하게 될 것이므로, 꼭 알아두도록 하자.