심심풀이 - 그네번째(1차 내용보완 및 수정, 약간의 주석추가)
더미(모조)테이블을 이용하자.
귀찮아서 주석을 대충 달았네요. 그래두 천천히 보면 어렵지는 않을듯.
먼저 cross join(상호조인)에 대해서 살펴보자.
온라인설명서를 보니 번역이 상호조인 이라고 되어 있네요.
상호 조인 사용 : WHERE 절이 없는 상호 조인은 조인에 포함된 테이블의 카티션 곱을 반환합니다.
카티션 곱 결과 집합의 크기는 첫 번째 테이블의 행 개수와 두 번째 테이블의 행 개수를 곱한
값입니다. 다음은 Transact-SQL 상호 조인의 예제입니다.
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
결과 집합에는 184개의 행이 포함됩니다.
authors의 행 개수인 23과 publishers의 행 개수인 8을 곱하면 184입니다.
이제 대강의 cross join에 대해서 살펴봤으니 본론으로 들어가서
/* *********************************************************************** */
1. cross join 을 사용한 뻥튀기(숫자 더미테이블 사용)
set statistics io off
set statistics profile off
--drop table dumy_no
create table dumy_no(no int, no2 varchar(2), no3 char(2))
go
set nocount on
declare @i int
set @i = 1
while (@i < 100)
begin
insert into dumy_no
values(@i, convert(varchar(2), @i), right('0'+convert(varchar(2),@i),2))
set @i = @i + 1
end
set nocount off
go
select * from dumy_no
/* ----------------------------------------------------------------------- */
사용예)
--drop table 부서
create table 부서(부서코드 varchar(3), 부서명칭 varchar(20))
go
insert into 부서 values('101', '총무부')
insert into 부서 values('102', '영업부')
insert into 부서 values('103', '개발부')
go
--drop table 사원
create table 사원(사원번호 varchar(10), 사원명 varchar(10), 부서코드 varchar(3), 급여 int)
go
insert into 사원 values('2002001', '홍길동', '101', 100)
insert into 사원 values('2002002', '콩쥐', '101', 200)
insert into 사원 values('2002003', '팥쥐', '102', 300)
insert into 사원 values('2002004', '흥부', '102', 400)
insert into 사원 values('2002005', '놀부', '102', 500)
insert into 사원 values('2002006', '까치', '102', 600)
insert into 사원 values('2002007', '산적', '103', 700)
insert into 사원 values('2002008', '토끼', '103', 800)
insert into 사원 values('2002009', '거북이', '103', 900)
insert into 사원 values('2002010', '곰돌이푸', '103', 100)
insert into 사원 values('2002011', '짱구', '103', 200)
go
/* ----------------------------------------------------------------------- */
사례1 : 부서별 급여계와 전체계를 구해보자
--1. rollup 사용
select case when grouping(부서명칭)=1 then '합계' else 부서명칭 end 부서,
sum(급여) 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드
group by 부서명칭 with rollup
--2. 더미테이블 사용
select case y.no when 1 then 부서명칭 else '합계' end 부서,
sum(급여) 급여
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
group by case y.no when 1 then 부서명칭 else '합계' end
부서 급여
-------------------- -----------
개발부 2700
영업부 1800
총무부 300
합계 4800
(4개 행 적용됨)
rollup을 사용한것이나 더미테이블을 사용한것이나 결과가 동일하고
오히려 rollup을 사용한것이 코딩이 더 깔끔하구 효율도 더 좋다.
더미테이블을 사용할필요가 없어 보인다. 그렇지만 가끔 필요한 경우가 있다.
/* ----------------------------------------------------------------------- */
좀더 진도를 나가기 전에 우선 위쿼리문에 대해서 조금만 살펴보자.
1. 필요한것은 부서별 급여합계이다. 먼저 부서별로 급여합계를 구한다.
select 부서코드, sum(급여) 급여
from 사원
group by 부서코드
2. 부서명칭이 필요하니까 부서코드테이블과 조인후에 부서명칭을 구해보자.
select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드
물론, 그냥
select 부서명칭, sum(급여) 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드
group by 부서명칭
이런식으로 사원과 부서를 조인한후에 급여합계를 구해도 되겠지만
조금이나마 불필요한 조인을 줄이기 위해서 위와 같이 한것이다.
3. 그 다음으로 해야할것은 일단, 부서별 급여합계와 함께 전체합계를 구해야 한다.
뭐, 커서를 사용해서 누적시켜나가던지, 해당테이블을 두번읽어서
한번은 부서별합계를 구하고, 또 한번읽어서 전체 합계를 구한다음 union등으로
합쳐서 보여줄수도 있겠지만, 이런식의 쿼리는 중학생들이나 하는 쿼리가 아닐런지...
굳이 테이블을 두번읽어서 성능을 떨어뜨리면서 사용하고싶다면 말리지는 않겠다.
여기서 더미테이블을 사용하여 cross join 을 하는것은 위 글에서 지적했듯이
테이블을 두번읽지않고 두번 읽는 효과를 주기위한방법인것이다.
cross join 은 알다시피 2row를 가진 테이블과 3row를 가진테이블을 cross join시키면
모든 가능한조합을 만들어 2 * 3 = 6row의 결과테이블이 나온다.
이것을 응용해보자는 것이다.
먼저, cross join을 시켜서 2배로 뻥튀기 해놓고 첫번째것은 부서별 합계를 그대로 보여주고,
두번째 것은 전체합계를 만들기 위한 용도로 사용한다.
먼저, cross join을 사용하여 뻥튀기 해보자.
select x.*, y.no
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
order by y.no
부서명칭 급여 no
-------------------- ----------- -----------
총무부 300 1
영업부 1800 1
개발부 2700 1
개발부 2700 2
영업부 1800 2
총무부 300 2
(6개 행 적용됨)
위 결과를 보면 2개씩(no가 1인것과 no가 2인것) 정확히 뻥튀기 되었다.
이제 group by시키면 된다.
4. group by 하기 전에, 그럼, 어떻게 해야 no가 1인것들은 그냥보여주고
no가 2인것들은 합칠수 있을까 ?
select case y.no when 1 then 부서명칭 else '합계' end 부서, 급여, no
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
order by y.no
부서 급여 no
-------------------- ----------- -----------
총무부 300 1
영업부 1800 1
개발부 2700 1
합계 2700 2
합계 1800 2
합계 300 2
바로, case를 사용하여 부서명칭을 변경시켜주는것이다.
이제 부서명칭으로 group by를 하게되면 총무부,영업부,개발부는 자기이름이
별도로 있으니까 그냥 나올것이고 나머지 합계라는 이름은 하나로 합쳐져서
전체합계가 되는것이다.
5. 진짜 group by 시켜보자.
select case y.no when 1 then 부서명칭 else '합계' end 부서,
sum(급여) 급여
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
group by case y.no when 1 then 부서명칭 else '합계' end
부서 급여
-------------------- -----------
개발부 2700
영업부 1800
총무부 300
합계 4800
(4개 행 적용됨)
잘 처리되었다. 더미테이블을 사용한 뻥튀기 방법을 이제는 알겠지요...
/* ----------------------------------------------------------------------- */
사례2 : 부서별 사원급여 리스트
조건 - 해당부서의 사원 리스트와 부서별 소계를 출력, 마지막줄에 합계 출력
(사례1을 이해했다면 어렵지않게 풀수있는 문제이다)
<원하는 출력형태>
사원번호 사원명 부서명칭 급여
---------- ---------- ------------------------ -----------
2002007 산적 개발부 700
2002008 토끼 개발부 800
2002009 거북이 개발부 900
2002010 곰돌이푸 개발부 100
2002011 짱구 개발부 200
개발부소계 2700
2002003 팥쥐 영업부 300
2002004 흥부 영업부 400
2002005 놀부 영업부 500
2002006 까치 영업부 600
영업부소계 1800
2002001 홍길동 총무부 100
2002002 콩쥐 총무부 200
총무부소계 300
합계 4800
아래 답을 보지말고 직접 풀어보세요.
select min(case when no=1 then 사원번호 else '' end) 사원번호,
min(case when no=1 then 사원명 else '' end) 사원명,
min(case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end) 부서명칭,
sum(급여) 급여
from (select 사원번호, 사원명, 부서명칭, 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드) x
cross join dumy_no y
where y.no <= 3
group by case no when 1 then 사원번호
when 2 then 부서명칭+'소계'
when 3 then '합계' end
order by min(case no when 1 then '1'+부서명칭
when 2 then '1'+부서명칭+'소계'
when 3 then '2'+'합계' end)
이번쿼리문도 살짝 분석(?)해 보면
나머지는 별거없구 select 절과, order by 절에 min()함수를 사용한 것이 조금특이(?)하다.
이것에 대해서 조금만 살펴보자.
기본적으로 group by 절에 없는 컬럼은 select 절에 그냥올수 없고 집계합수와 같이 와야한다.
물론, order by 절도 마찬가지이다. 결론적으로 그래서 min()함수를 사용한것이다.
select절 부터 보면
select case when no=1 then 사원번호 else '' end 사원번호,
case when no=1 then 사원명 else '' end 사원명,
case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end 부서명칭,
급여,
case no when 1 then 사원번호
when 2 then 부서명칭+'소계'
when 3 then '합계' end 그룹바이용,
case no when 1 then '1'+부서명칭
when 2 then '1'+부서명칭+'소계'
when 3 then '2'+'합계' end 오더바이용
from (select 사원번호, 사원명, 부서명칭, 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드) x
cross join dumy_no y
where y.no <= 3
사원번호 사원명 부서명칭 급여 그룹바이용 오더바이용
---------- ---------- ------------- ----- -------------- ----------
2002001 홍길동 총무부 100 2002001 1총무부
2002002 콩쥐 총무부 200 2002002 1총무부
2002003 팥쥐 영업부 300 2002003 1영업부
2002004 흥부 영업부 400 2002004 1영업부
2002005 놀부 영업부 500 2002005 1영업부
2002006 까치 영업부 600 2002006 1영업부
2002007 산적 개발부 700 2002007 1개발부
2002008 토끼 개발부 800 2002008 1개발부
2002009 거북이 개발부 900 2002009 1개발부
2002010 곰돌이푸 개발부 100 2002010 1개발부
2002011 짱구 개발부 200 2002011 1개발부
총무부소계 100 총무부소계 1총무부소계
총무부소계 200 총무부소계 1총무부소계
영업부소계 300 영업부소계 1영업부소계
영업부소계 400 영업부소계 1영업부소계
영업부소계 500 영업부소계 1영업부소계
영업부소계 600 영업부소계 1영업부소계
개발부소계 700 개발부소계 1개발부소계
개발부소계 800 개발부소계 1개발부소계
개발부소계 900 개발부소계 1개발부소계
개발부소계 100 개발부소계 1개발부소계
개발부소계 200 개발부소계 1개발부소계
합계 100 합계 2합계
합계 200 합계 2합계
합계 300 합계 2합계
합계 400 합계 2합계
합계 500 합계 2합계
합계 600 합계 2합계
합계 700 합계 2합계
합계 800 합계 2합계
합계 900 합계 2합계
합계 100 합계 2합계
합계 200 합계 2합계
(33개 행 적용됨)
이런식으로 결과가 나온다. 사원 list용으로 하나, 부서별소계용으로 하나, 전체합계용으로 하나
이렇게 3배로 뻥튀기한것이고, 사원 list용은 모든 컬럼이 다 나와야하지만, 부서별소계나
전체합계는 명칭과 합계금액만 나오면 되므로 위에서 case 문을 사용하여 컬럼을 변형시킨것이다.
그룹바이용컬럼과 오더바이용컬럼을 잘 살펴보자.
그룹바이용컬럼을 가지고 그룹바이를 한다고 생각을 해보면 사용리스트용은 사원번호가 개별적인
유니크한값이므로 그냥 낱개로 나올것이고, 부서별소계들은 명칭이 동일한것들끼리 합쳐질것이고
나머지 합계는 하나로 합쳐질것이다.
사원번호 사원명 부서명칭 급여 그룹바이용 오더바이용
---------- ---------- ------------- ----- -------------- ----------
2002001 홍길동 총무부 100 2002001 1총무부
2002002 콩쥐 총무부 200 2002002 1총무부
2002003 팥쥐 영업부 300 2002003 1영업부
2002004 흥부 영업부 400 2002004 1영업부
2002005 놀부 영업부 500 2002005 1영업부
2002006 까치 영업부 600 2002006 1영업부
2002007 산적 개발부 700 2002007 1개발부
2002008 토끼 개발부 800 2002008 1개발부
2002009 거북이 개발부 900 2002009 1개발부
2002010 곰돌이푸 개발부 100 2002010 1개발부
2002011 짱구 개발부 200 2002011 1개발부
select 절을 살펴보기 위해 사원리스트용만 가져온것이다.
저런데이터가 들어 있는 테이블이 있다고 가정하고 보자.
select min(case when no=1 then 사원번호 else '' end) 사원번호,
min(case when no=1 then 사원명 else '' end) 사원명,
min(case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end) 부서명칭,
sum(급여) 급여
from 저런테이블
group by 그룹바이용
보면, 그룹바이용컬럼자체가 유니크한값이므로 그컬럼을 가지고 group by를 해봐야
결과적으로 모든컬럼은 그대로 나와야한다.
일반적으로 이런경우
select case when no=1 then 사원번호 else '' end 사원번호,
case when no=1 then 사원명 else '' end 사원명,
case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end 부서명칭,
급여
from 저런테이블
group by 그룹바이용, 사원번호, 사원명, 부서명칭, 급여
이런식으로 group by절에 컬럼리스트를 쭈욱 나열하는데 그럴필요가 전혀 없다.
어짜피 모든컬럼이 있는 그대로 나오는것이기 때문에 min(), max() 등의 함수를
사용해도 그컬럼의 데이터 원형그대로 나오게 되어있다.
그래서, select 절에다가 min()함수를 사용한것이다.
마지막으로 order by 절은 각자 분석해보기로 하고, 저런식으로 order by 절에서
컬럼을 변형시킨것은 부서별 소계가 중간중간에 나와야하기때문에 저런식으로 쿼리한것이다.
/* ----------------------------------------------------------------------- */
사례3 : column형태를 row의 형태로 변형(Q&A에 올라온 질문)
> 인기순으로 들어가는 Code_Pop 이라는 테이블이 하나 있습니다.
> 컬럼은 code1, code2, code3, code4, code5 이라는 식으로 있구요
> 물론 code1에는 1위가... code5에는 5위가 들어갑니다.
>
> 그리고 각 code값에 대한 상세정보는 Code_Detail이라는 테이블에 있습니다.
> 컬럼들로는 code_no, name, price, amount, reg_day 등이 있구요.
>
> 문제는 1위부터 5위까지 순차적으로 상세정보를 보여주고 싶을 때
> 어떻게 해야 하는지 모르겠습니다.
>
> Code_Pop
> ---------------------------------------------
> code1 code2 code3 code4 code5
> ---------------------------------------------
> 95 55 74 91 88 <- 이런 식으로 code값이 들어있습니당
>
>
> Code_Detail
> ------------------------
> code_no name price
> ------------------------
> 55 AAA 1000
> 60 BBB 2000
> 74 CCC 3000
> 88 DDD 4000
> 91 EEE 5000
> 95 FFF 6000
> .......
>
>
> 제가 원하는 결과는..
> ------------------------
> code_no name price
> ------------------------
> 95 FFF 6000
> 55 AAA 1000
> 74 CCC 3000
> ......
> 88 DDD 4000
--먼저 샘플만들구
create table code_pop (code1 int,code2 int,code3 int,code4 int,code5 int)
create table code_detail(code_no int, name char(5),price int)
go
insert into code_pop values(95 , 55, 74, 91, 88)
insert into code_detail values(55, 'aaa', 1000)
insert into code_detail values(60, 'bbb', 2000)
insert into code_detail values(74, 'ccc', 3000)
insert into code_detail values(88, 'ddd', 4000)
insert into code_detail values(91, 'eee', 5000)
insert into code_detail values(95, 'fff', 6000)
go
옆으로 펼치진 컬럼을 밑으로 펼치면 쉽게 해결되지요.
무슨말인가 하면 code1, code2, ..code5 이런식으로 되어있는것을
code1
code2
:
code5
이런식으로 밑으로 row의 형태로 있다면 그냥 code_detail이랑 바로
조인하면 되겠지요.
그렇게 하기위해서 1부터 5까지 있는 더미테이블이 하나 있다면 간단히
cross join으로 해결됩니다.
말보단는 쿼리문을 보시는것이 좋을것 같네요.
이제 이 더미테이블을 이용하여 code_pop을 밑으로 펼치면
select y.no,
case y.no when 1 then x.code1
when 2 then x.code2
when 3 then x.code3
when 4 then x.code4
when 5 then x.code5
end code
from code_pop x cross join dumy_no y
where y.no <= 5
no code
----------- -----------
1 95
2 55
3 74
4 91
5 88
(5개 행 적용됨)
이제 위 쿼리를 인라인뷰로 묶어서 code_detail 테이블이랑 조인걸면
select b.*
from
(select y.no,
case y.no when 1 then x.code1
when 2 then x.code2
when 3 then x.code3
when 4 then x.code4
when 5 then x.code5
end code
from code_pop x cross join dumy_no y
where y.no <= 5) a inner join code_detail b
on b.code_no = a.code
order by a.no
Code_No Name Price
----------- ----- -----------
95 FFF 6000
55 AAA 1000
74 CCC 3000
91 EEE 5000
88 DDD 4000
(5개 행 적용됨)
/* ---------------------------------------------------------------------- */
사례4. 월마감장부가 있다. 이것을 가지고 2002년도 전월대비 당월증감액을 확인해보자.
set statistics io off
set statistics profile off
--역시나 샘플데이터 먼저 만들고
--drop table a
create table a(dt char(8) not null primary key, qty int)
go
set nocount on
declare @i datetime
set @i = '2001-12-01'
while (@i <= '2002-12-31')
begin
insert into a values (convert(char(8),@i,112), convert(int, round(rand()*100,0)) )
set @i = dateadd(dd, 1, @i)
end
set nocount off
go
ym 전월 당월 증감
-------- ----------- ----------- -----------
200201 1528 1984 456
200202 1984 1350 -634
200203 1350 1568 218
200204 1568 1438 -130
200205 1438 1375 -63
200206 1375 1508 133
200207 1508 1257 -251
200208 1257 1417 160
200209 1417 1679 262
200210 1679 1345 -334
200211 1345 1553 208
200212 1553 1721 168
원하는 출력결과이다.
아래 쿼리를 보지 말고 각자 해보세요...
힌트 : 2001년 12월 부터 2002년 12월 까지 13개월치를 읽어야한다.
--쿼리문
select case when y.no=1 then left(convert(varchar(8),
dateadd(mm,1,convert(datetime, x.ym+'01')), 112),6)
when y.no=2 then x.ym
end ym,
sum(case when y.no=1 then qty end) 전월,
sum(case when y.no=2 then qty end) 당월,
sum(case when y.no=2 then qty end) - sum(case when y.no=1 then qty end) 증감
from (select x.ym, sum(y.qty) qty
from dumy_ym x left outer join a y
on y.dt between x.ym_st and x.ym_en
where x.ym between '200112' and '200212'
group by x.ym) x cross join dumy_no y
where y.no <= 2
and x.ym between case y.no when 1 then '200112'
when 2 then '200201' end
and case y.no when 1 then '200211'
when 2 then '200212' end
group by case when y.no=1 then left(convert(varchar(8),
dateadd(mm,1,convert(datetime, x.ym+'01')), 112),6)
when y.no=2 then x.ym
end
/* ---------------------------------------------------------------------- */
사례5. 소계를 첫번째 레코드에 함께 출력
> > 쿼리 좀 부탁합니다..이것저것해봤는데 되지가 않네요...
> >
> > 대분류 소분류 개수 소계
> >
> > ㄱ 가 1 2
> > 거 1
> >
> > ㄴ 나 2 3
> > 너 1
> >
> > ㄷ 다 1 1
> > 더 0
> >
> > ㄹ 라 1 2
> > 러 1
> >
> > 합계
> > 8
> >
> > 이런식으로 나와야 되는데요...방법을 못찾겠습니다...
--먼저 샘플만들고
--drop table a
create table a(대분류 varchar(10), 소분류 varchar(10), 개수 int)
insert into a values ('ㄱ', '가', 1)
insert into a values ('ㄱ', '거', 1)
insert into a values ('ㄴ', '나', 2)
insert into a values ('ㄴ', '너', 1)
insert into a values ('ㄷ', '다', 1)
insert into a values ('ㄷ', '더', 0)
insert into a values ('ㄹ', '라', 1)
insert into a values ('ㄹ', '러', 1)
go
--------------------------------------------------------------------------
--쿼리해보자(아래 쿼리를 보지 말고 각자 해보세요...)
select case when 대분류1 <> '합계' and count(*) <= 1 then '' else 대분류1 end as 대분류,
min(case when 소분류1 = '합계' then '' else 소분류1 end) as 소분류,
isnull(sum(case when 소분류1 <> '소계' then 개수1 end),0) as 개수,
isnull(sum(case when 소분류1 = '소계' then 개수1 end),0) as 소계
from (
select case when no=3 then '합계' else 대분류 end 대분류1,
case no when 3 then '합계' when 2 then '소계' else 소분류 end 소분류1,
sum(개수) 개수1,
min(case when no=3 then '' else 소분류 end) 그룹바이용컬럼
from a cross join (select 1 as no union all select 2 union all select 3) b
group by case when no=3 then '합계' else 대분류 end,
case no when 3 then '합계' when 2 then '소계' else 소분류 end) x
group by 대분류1, 그룹바이용컬럼
order by 대분류1, 그룹바이용컬럼
--------------------------------------------------------------------------
이번 사례는 다른것은 별거없고 다만 소계를 각항목별 첫번째 레코드와 동일한 라인에
출력해야 한다는것이 조금 색다르다.
그것을 하기위해서 [그룹바이용컬럼]이라는 별도의 가공컬럼을 하나두었다
근데 어떻게 하면 각 그룹별 첫번째 레코드에 소계항목이 오게할수 있을까 ?
무엇인가 group by를 시킬 공통사항을 찾아내어야 한다.
그래서 기존컬럼에 없는 [그룹바이용컬럼]이라는 가공컬럼이 필요한것이다.
제일 안쪽의 in-line view를 잠시 살펴보면
select case when no=3 then '합계' else 대분류 end 대분류1,
case no when 3 then '합계' when 2 then '소계' else 소분류 end 소분류1,
sum(개수) 개수1,
min(case when no=3 then '' else 소분류 end) 그룹바이용컬럼
from a cross join (select 1 as no union all select 2 union all select 3
대분류1 소분류1 개수1 그룹바이용컬럼
---------- ---------- ----------- ----------
ㄱ 가 1 가
ㄱ 거 1 거
ㄴ 나 2 나
ㄴ 너 1 너
ㄷ 다 1 다
ㄷ 더 0 더
ㄹ 라 1 라
ㄹ 러 1 러
ㄱ 소계 2 가
ㄴ 소계 3 나
ㄷ 소계 1 다
ㄹ 소계 2 라
합계 합계 8
(13개 행 적용됨)
이제 이 [그룹바이용컬럼] 이라는 가공컬럼을 가지고 group by시키면
대분류1 소분류1 개수1 그룹바이용컬럼
---------- ---------- ----------- ----------
ㄱ 가 1 가
ㄱ 소계 2 가
더미(모조)테이블을 이용하자.
귀찮아서 주석을 대충 달았네요. 그래두 천천히 보면 어렵지는 않을듯.
먼저 cross join(상호조인)에 대해서 살펴보자.
온라인설명서를 보니 번역이 상호조인 이라고 되어 있네요.
상호 조인 사용 : WHERE 절이 없는 상호 조인은 조인에 포함된 테이블의 카티션 곱을 반환합니다.
카티션 곱 결과 집합의 크기는 첫 번째 테이블의 행 개수와 두 번째 테이블의 행 개수를 곱한
값입니다. 다음은 Transact-SQL 상호 조인의 예제입니다.
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
결과 집합에는 184개의 행이 포함됩니다.
authors의 행 개수인 23과 publishers의 행 개수인 8을 곱하면 184입니다.
이제 대강의 cross join에 대해서 살펴봤으니 본론으로 들어가서
/* *********************************************************************** */
1. cross join 을 사용한 뻥튀기(숫자 더미테이블 사용)
set statistics io off
set statistics profile off
--drop table dumy_no
create table dumy_no(no int, no2 varchar(2), no3 char(2))
go
set nocount on
declare @i int
set @i = 1
while (@i < 100)
begin
insert into dumy_no
values(@i, convert(varchar(2), @i), right('0'+convert(varchar(2),@i),2))
set @i = @i + 1
end
set nocount off
go
select * from dumy_no
/* ----------------------------------------------------------------------- */
사용예)
--drop table 부서
create table 부서(부서코드 varchar(3), 부서명칭 varchar(20))
go
insert into 부서 values('101', '총무부')
insert into 부서 values('102', '영업부')
insert into 부서 values('103', '개발부')
go
--drop table 사원
create table 사원(사원번호 varchar(10), 사원명 varchar(10), 부서코드 varchar(3), 급여 int)
go
insert into 사원 values('2002001', '홍길동', '101', 100)
insert into 사원 values('2002002', '콩쥐', '101', 200)
insert into 사원 values('2002003', '팥쥐', '102', 300)
insert into 사원 values('2002004', '흥부', '102', 400)
insert into 사원 values('2002005', '놀부', '102', 500)
insert into 사원 values('2002006', '까치', '102', 600)
insert into 사원 values('2002007', '산적', '103', 700)
insert into 사원 values('2002008', '토끼', '103', 800)
insert into 사원 values('2002009', '거북이', '103', 900)
insert into 사원 values('2002010', '곰돌이푸', '103', 100)
insert into 사원 values('2002011', '짱구', '103', 200)
go
/* ----------------------------------------------------------------------- */
사례1 : 부서별 급여계와 전체계를 구해보자
--1. rollup 사용
select case when grouping(부서명칭)=1 then '합계' else 부서명칭 end 부서,
sum(급여) 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드
group by 부서명칭 with rollup
--2. 더미테이블 사용
select case y.no when 1 then 부서명칭 else '합계' end 부서,
sum(급여) 급여
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
group by case y.no when 1 then 부서명칭 else '합계' end
부서 급여
-------------------- -----------
개발부 2700
영업부 1800
총무부 300
합계 4800
(4개 행 적용됨)
rollup을 사용한것이나 더미테이블을 사용한것이나 결과가 동일하고
오히려 rollup을 사용한것이 코딩이 더 깔끔하구 효율도 더 좋다.
더미테이블을 사용할필요가 없어 보인다. 그렇지만 가끔 필요한 경우가 있다.
/* ----------------------------------------------------------------------- */
좀더 진도를 나가기 전에 우선 위쿼리문에 대해서 조금만 살펴보자.
1. 필요한것은 부서별 급여합계이다. 먼저 부서별로 급여합계를 구한다.
select 부서코드, sum(급여) 급여
from 사원
group by 부서코드
2. 부서명칭이 필요하니까 부서코드테이블과 조인후에 부서명칭을 구해보자.
select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드
물론, 그냥
select 부서명칭, sum(급여) 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드
group by 부서명칭
이런식으로 사원과 부서를 조인한후에 급여합계를 구해도 되겠지만
조금이나마 불필요한 조인을 줄이기 위해서 위와 같이 한것이다.
3. 그 다음으로 해야할것은 일단, 부서별 급여합계와 함께 전체합계를 구해야 한다.
뭐, 커서를 사용해서 누적시켜나가던지, 해당테이블을 두번읽어서
한번은 부서별합계를 구하고, 또 한번읽어서 전체 합계를 구한다음 union등으로
합쳐서 보여줄수도 있겠지만, 이런식의 쿼리는 중학생들이나 하는 쿼리가 아닐런지...
굳이 테이블을 두번읽어서 성능을 떨어뜨리면서 사용하고싶다면 말리지는 않겠다.
여기서 더미테이블을 사용하여 cross join 을 하는것은 위 글에서 지적했듯이
테이블을 두번읽지않고 두번 읽는 효과를 주기위한방법인것이다.
cross join 은 알다시피 2row를 가진 테이블과 3row를 가진테이블을 cross join시키면
모든 가능한조합을 만들어 2 * 3 = 6row의 결과테이블이 나온다.
이것을 응용해보자는 것이다.
먼저, cross join을 시켜서 2배로 뻥튀기 해놓고 첫번째것은 부서별 합계를 그대로 보여주고,
두번째 것은 전체합계를 만들기 위한 용도로 사용한다.
먼저, cross join을 사용하여 뻥튀기 해보자.
select x.*, y.no
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
order by y.no
부서명칭 급여 no
-------------------- ----------- -----------
총무부 300 1
영업부 1800 1
개발부 2700 1
개발부 2700 2
영업부 1800 2
총무부 300 2
(6개 행 적용됨)
위 결과를 보면 2개씩(no가 1인것과 no가 2인것) 정확히 뻥튀기 되었다.
이제 group by시키면 된다.
4. group by 하기 전에, 그럼, 어떻게 해야 no가 1인것들은 그냥보여주고
no가 2인것들은 합칠수 있을까 ?
select case y.no when 1 then 부서명칭 else '합계' end 부서, 급여, no
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
order by y.no
부서 급여 no
-------------------- ----------- -----------
총무부 300 1
영업부 1800 1
개발부 2700 1
합계 2700 2
합계 1800 2
합계 300 2
바로, case를 사용하여 부서명칭을 변경시켜주는것이다.
이제 부서명칭으로 group by를 하게되면 총무부,영업부,개발부는 자기이름이
별도로 있으니까 그냥 나올것이고 나머지 합계라는 이름은 하나로 합쳐져서
전체합계가 되는것이다.
5. 진짜 group by 시켜보자.
select case y.no when 1 then 부서명칭 else '합계' end 부서,
sum(급여) 급여
from (select 부서명칭, 급여
from (select 부서코드, sum(급여) 급여
from 사원
group by 부서코드) x1 inner join 부서 y1
on y1.부서코드 = x1.부서코드) x
cross join dumy_no y
where y.no <= 2
group by case y.no when 1 then 부서명칭 else '합계' end
부서 급여
-------------------- -----------
개발부 2700
영업부 1800
총무부 300
합계 4800
(4개 행 적용됨)
잘 처리되었다. 더미테이블을 사용한 뻥튀기 방법을 이제는 알겠지요...
/* ----------------------------------------------------------------------- */
사례2 : 부서별 사원급여 리스트
조건 - 해당부서의 사원 리스트와 부서별 소계를 출력, 마지막줄에 합계 출력
(사례1을 이해했다면 어렵지않게 풀수있는 문제이다)
<원하는 출력형태>
사원번호 사원명 부서명칭 급여
---------- ---------- ------------------------ -----------
2002007 산적 개발부 700
2002008 토끼 개발부 800
2002009 거북이 개발부 900
2002010 곰돌이푸 개발부 100
2002011 짱구 개발부 200
개발부소계 2700
2002003 팥쥐 영업부 300
2002004 흥부 영업부 400
2002005 놀부 영업부 500
2002006 까치 영업부 600
영업부소계 1800
2002001 홍길동 총무부 100
2002002 콩쥐 총무부 200
총무부소계 300
합계 4800
아래 답을 보지말고 직접 풀어보세요.
select min(case when no=1 then 사원번호 else '' end) 사원번호,
min(case when no=1 then 사원명 else '' end) 사원명,
min(case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end) 부서명칭,
sum(급여) 급여
from (select 사원번호, 사원명, 부서명칭, 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드) x
cross join dumy_no y
where y.no <= 3
group by case no when 1 then 사원번호
when 2 then 부서명칭+'소계'
when 3 then '합계' end
order by min(case no when 1 then '1'+부서명칭
when 2 then '1'+부서명칭+'소계'
when 3 then '2'+'합계' end)
이번쿼리문도 살짝 분석(?)해 보면
나머지는 별거없구 select 절과, order by 절에 min()함수를 사용한 것이 조금특이(?)하다.
이것에 대해서 조금만 살펴보자.
기본적으로 group by 절에 없는 컬럼은 select 절에 그냥올수 없고 집계합수와 같이 와야한다.
물론, order by 절도 마찬가지이다. 결론적으로 그래서 min()함수를 사용한것이다.
select절 부터 보면
select case when no=1 then 사원번호 else '' end 사원번호,
case when no=1 then 사원명 else '' end 사원명,
case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end 부서명칭,
급여,
case no when 1 then 사원번호
when 2 then 부서명칭+'소계'
when 3 then '합계' end 그룹바이용,
case no when 1 then '1'+부서명칭
when 2 then '1'+부서명칭+'소계'
when 3 then '2'+'합계' end 오더바이용
from (select 사원번호, 사원명, 부서명칭, 급여
from 사원 inner join 부서
on 부서.부서코드 = 사원.부서코드) x
cross join dumy_no y
where y.no <= 3
사원번호 사원명 부서명칭 급여 그룹바이용 오더바이용
---------- ---------- ------------- ----- -------------- ----------
2002001 홍길동 총무부 100 2002001 1총무부
2002002 콩쥐 총무부 200 2002002 1총무부
2002003 팥쥐 영업부 300 2002003 1영업부
2002004 흥부 영업부 400 2002004 1영업부
2002005 놀부 영업부 500 2002005 1영업부
2002006 까치 영업부 600 2002006 1영업부
2002007 산적 개발부 700 2002007 1개발부
2002008 토끼 개발부 800 2002008 1개발부
2002009 거북이 개발부 900 2002009 1개발부
2002010 곰돌이푸 개발부 100 2002010 1개발부
2002011 짱구 개발부 200 2002011 1개발부
총무부소계 100 총무부소계 1총무부소계
총무부소계 200 총무부소계 1총무부소계
영업부소계 300 영업부소계 1영업부소계
영업부소계 400 영업부소계 1영업부소계
영업부소계 500 영업부소계 1영업부소계
영업부소계 600 영업부소계 1영업부소계
개발부소계 700 개발부소계 1개발부소계
개발부소계 800 개발부소계 1개발부소계
개발부소계 900 개발부소계 1개발부소계
개발부소계 100 개발부소계 1개발부소계
개발부소계 200 개발부소계 1개발부소계
합계 100 합계 2합계
합계 200 합계 2합계
합계 300 합계 2합계
합계 400 합계 2합계
합계 500 합계 2합계
합계 600 합계 2합계
합계 700 합계 2합계
합계 800 합계 2합계
합계 900 합계 2합계
합계 100 합계 2합계
합계 200 합계 2합계
(33개 행 적용됨)
이런식으로 결과가 나온다. 사원 list용으로 하나, 부서별소계용으로 하나, 전체합계용으로 하나
이렇게 3배로 뻥튀기한것이고, 사원 list용은 모든 컬럼이 다 나와야하지만, 부서별소계나
전체합계는 명칭과 합계금액만 나오면 되므로 위에서 case 문을 사용하여 컬럼을 변형시킨것이다.
그룹바이용컬럼과 오더바이용컬럼을 잘 살펴보자.
그룹바이용컬럼을 가지고 그룹바이를 한다고 생각을 해보면 사용리스트용은 사원번호가 개별적인
유니크한값이므로 그냥 낱개로 나올것이고, 부서별소계들은 명칭이 동일한것들끼리 합쳐질것이고
나머지 합계는 하나로 합쳐질것이다.
사원번호 사원명 부서명칭 급여 그룹바이용 오더바이용
---------- ---------- ------------- ----- -------------- ----------
2002001 홍길동 총무부 100 2002001 1총무부
2002002 콩쥐 총무부 200 2002002 1총무부
2002003 팥쥐 영업부 300 2002003 1영업부
2002004 흥부 영업부 400 2002004 1영업부
2002005 놀부 영업부 500 2002005 1영업부
2002006 까치 영업부 600 2002006 1영업부
2002007 산적 개발부 700 2002007 1개발부
2002008 토끼 개발부 800 2002008 1개발부
2002009 거북이 개발부 900 2002009 1개발부
2002010 곰돌이푸 개발부 100 2002010 1개발부
2002011 짱구 개발부 200 2002011 1개발부
select 절을 살펴보기 위해 사원리스트용만 가져온것이다.
저런데이터가 들어 있는 테이블이 있다고 가정하고 보자.
select min(case when no=1 then 사원번호 else '' end) 사원번호,
min(case when no=1 then 사원명 else '' end) 사원명,
min(case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end) 부서명칭,
sum(급여) 급여
from 저런테이블
group by 그룹바이용
보면, 그룹바이용컬럼자체가 유니크한값이므로 그컬럼을 가지고 group by를 해봐야
결과적으로 모든컬럼은 그대로 나와야한다.
일반적으로 이런경우
select case when no=1 then 사원번호 else '' end 사원번호,
case when no=1 then 사원명 else '' end 사원명,
case no when 1 then 부서명칭
when 2 then 부서명칭+'소계'
when 3 then '합계' end 부서명칭,
급여
from 저런테이블
group by 그룹바이용, 사원번호, 사원명, 부서명칭, 급여
이런식으로 group by절에 컬럼리스트를 쭈욱 나열하는데 그럴필요가 전혀 없다.
어짜피 모든컬럼이 있는 그대로 나오는것이기 때문에 min(), max() 등의 함수를
사용해도 그컬럼의 데이터 원형그대로 나오게 되어있다.
그래서, select 절에다가 min()함수를 사용한것이다.
마지막으로 order by 절은 각자 분석해보기로 하고, 저런식으로 order by 절에서
컬럼을 변형시킨것은 부서별 소계가 중간중간에 나와야하기때문에 저런식으로 쿼리한것이다.
/* ----------------------------------------------------------------------- */
사례3 : column형태를 row의 형태로 변형(Q&A에 올라온 질문)
> 인기순으로 들어가는 Code_Pop 이라는 테이블이 하나 있습니다.
> 컬럼은 code1, code2, code3, code4, code5 이라는 식으로 있구요
> 물론 code1에는 1위가... code5에는 5위가 들어갑니다.
>
> 그리고 각 code값에 대한 상세정보는 Code_Detail이라는 테이블에 있습니다.
> 컬럼들로는 code_no, name, price, amount, reg_day 등이 있구요.
>
> 문제는 1위부터 5위까지 순차적으로 상세정보를 보여주고 싶을 때
> 어떻게 해야 하는지 모르겠습니다.
>
> Code_Pop
> ---------------------------------------------
> code1 code2 code3 code4 code5
> ---------------------------------------------
> 95 55 74 91 88 <- 이런 식으로 code값이 들어있습니당
>
>
> Code_Detail
> ------------------------
> code_no name price
> ------------------------
> 55 AAA 1000
> 60 BBB 2000
> 74 CCC 3000
> 88 DDD 4000
> 91 EEE 5000
> 95 FFF 6000
> .......
>
>
> 제가 원하는 결과는..
> ------------------------
> code_no name price
> ------------------------
> 95 FFF 6000
> 55 AAA 1000
> 74 CCC 3000
> ......
> 88 DDD 4000
--먼저 샘플만들구
create table code_pop (code1 int,code2 int,code3 int,code4 int,code5 int)
create table code_detail(code_no int, name char(5),price int)
go
insert into code_pop values(95 , 55, 74, 91, 88)
insert into code_detail values(55, 'aaa', 1000)
insert into code_detail values(60, 'bbb', 2000)
insert into code_detail values(74, 'ccc', 3000)
insert into code_detail values(88, 'ddd', 4000)
insert into code_detail values(91, 'eee', 5000)
insert into code_detail values(95, 'fff', 6000)
go
옆으로 펼치진 컬럼을 밑으로 펼치면 쉽게 해결되지요.
무슨말인가 하면 code1, code2, ..code5 이런식으로 되어있는것을
code1
code2
:
code5
이런식으로 밑으로 row의 형태로 있다면 그냥 code_detail이랑 바로
조인하면 되겠지요.
그렇게 하기위해서 1부터 5까지 있는 더미테이블이 하나 있다면 간단히
cross join으로 해결됩니다.
말보단는 쿼리문을 보시는것이 좋을것 같네요.
이제 이 더미테이블을 이용하여 code_pop을 밑으로 펼치면
select y.no,
case y.no when 1 then x.code1
when 2 then x.code2
when 3 then x.code3
when 4 then x.code4
when 5 then x.code5
end code
from code_pop x cross join dumy_no y
where y.no <= 5
no code
----------- -----------
1 95
2 55
3 74
4 91
5 88
(5개 행 적용됨)
이제 위 쿼리를 인라인뷰로 묶어서 code_detail 테이블이랑 조인걸면
select b.*
from
(select y.no,
case y.no when 1 then x.code1
when 2 then x.code2
when 3 then x.code3
when 4 then x.code4
when 5 then x.code5
end code
from code_pop x cross join dumy_no y
where y.no <= 5) a inner join code_detail b
on b.code_no = a.code
order by a.no
Code_No Name Price
----------- ----- -----------
95 FFF 6000
55 AAA 1000
74 CCC 3000
91 EEE 5000
88 DDD 4000
(5개 행 적용됨)
/* ---------------------------------------------------------------------- */
사례4. 월마감장부가 있다. 이것을 가지고 2002년도 전월대비 당월증감액을 확인해보자.
set statistics io off
set statistics profile off
--역시나 샘플데이터 먼저 만들고
--drop table a
create table a(dt char(8) not null primary key, qty int)
go
set nocount on
declare @i datetime
set @i = '2001-12-01'
while (@i <= '2002-12-31')
begin
insert into a values (convert(char(8),@i,112), convert(int, round(rand()*100,0)) )
set @i = dateadd(dd, 1, @i)
end
set nocount off
go
ym 전월 당월 증감
-------- ----------- ----------- -----------
200201 1528 1984 456
200202 1984 1350 -634
200203 1350 1568 218
200204 1568 1438 -130
200205 1438 1375 -63
200206 1375 1508 133
200207 1508 1257 -251
200208 1257 1417 160
200209 1417 1679 262
200210 1679 1345 -334
200211 1345 1553 208
200212 1553 1721 168
원하는 출력결과이다.
아래 쿼리를 보지 말고 각자 해보세요...
힌트 : 2001년 12월 부터 2002년 12월 까지 13개월치를 읽어야한다.
--쿼리문
select case when y.no=1 then left(convert(varchar(8),
dateadd(mm,1,convert(datetime, x.ym+'01')), 112),6)
when y.no=2 then x.ym
end ym,
sum(case when y.no=1 then qty end) 전월,
sum(case when y.no=2 then qty end) 당월,
sum(case when y.no=2 then qty end) - sum(case when y.no=1 then qty end) 증감
from (select x.ym, sum(y.qty) qty
from dumy_ym x left outer join a y
on y.dt between x.ym_st and x.ym_en
where x.ym between '200112' and '200212'
group by x.ym) x cross join dumy_no y
where y.no <= 2
and x.ym between case y.no when 1 then '200112'
when 2 then '200201' end
and case y.no when 1 then '200211'
when 2 then '200212' end
group by case when y.no=1 then left(convert(varchar(8),
dateadd(mm,1,convert(datetime, x.ym+'01')), 112),6)
when y.no=2 then x.ym
end
/* ---------------------------------------------------------------------- */
사례5. 소계를 첫번째 레코드에 함께 출력
> > 쿼리 좀 부탁합니다..이것저것해봤는데 되지가 않네요...
> >
> > 대분류 소분류 개수 소계
> >
> > ㄱ 가 1 2
> > 거 1
> >
> > ㄴ 나 2 3
> > 너 1
> >
> > ㄷ 다 1 1
> > 더 0
> >
> > ㄹ 라 1 2
> > 러 1
> >
> > 합계
> > 8
> >
> > 이런식으로 나와야 되는데요...방법을 못찾겠습니다...
--먼저 샘플만들고
--drop table a
create table a(대분류 varchar(10), 소분류 varchar(10), 개수 int)
insert into a values ('ㄱ', '가', 1)
insert into a values ('ㄱ', '거', 1)
insert into a values ('ㄴ', '나', 2)
insert into a values ('ㄴ', '너', 1)
insert into a values ('ㄷ', '다', 1)
insert into a values ('ㄷ', '더', 0)
insert into a values ('ㄹ', '라', 1)
insert into a values ('ㄹ', '러', 1)
go
--------------------------------------------------------------------------
--쿼리해보자(아래 쿼리를 보지 말고 각자 해보세요...)
select case when 대분류1 <> '합계' and count(*) <= 1 then '' else 대분류1 end as 대분류,
min(case when 소분류1 = '합계' then '' else 소분류1 end) as 소분류,
isnull(sum(case when 소분류1 <> '소계' then 개수1 end),0) as 개수,
isnull(sum(case when 소분류1 = '소계' then 개수1 end),0) as 소계
from (
select case when no=3 then '합계' else 대분류 end 대분류1,
case no when 3 then '합계' when 2 then '소계' else 소분류 end 소분류1,
sum(개수) 개수1,
min(case when no=3 then '' else 소분류 end) 그룹바이용컬럼
from a cross join (select 1 as no union all select 2 union all select 3) b
group by case when no=3 then '합계' else 대분류 end,
case no when 3 then '합계' when 2 then '소계' else 소분류 end) x
group by 대분류1, 그룹바이용컬럼
order by 대분류1, 그룹바이용컬럼
--------------------------------------------------------------------------
이번 사례는 다른것은 별거없고 다만 소계를 각항목별 첫번째 레코드와 동일한 라인에
출력해야 한다는것이 조금 색다르다.
그것을 하기위해서 [그룹바이용컬럼]이라는 별도의 가공컬럼을 하나두었다
근데 어떻게 하면 각 그룹별 첫번째 레코드에 소계항목이 오게할수 있을까 ?
무엇인가 group by를 시킬 공통사항을 찾아내어야 한다.
그래서 기존컬럼에 없는 [그룹바이용컬럼]이라는 가공컬럼이 필요한것이다.
제일 안쪽의 in-line view를 잠시 살펴보면
select case when no=3 then '합계' else 대분류 end 대분류1,
case no when 3 then '합계' when 2 then '소계' else 소분류 end 소분류1,
sum(개수) 개수1,
min(case when no=3 then '' else 소분류 end) 그룹바이용컬럼
from a cross join (select 1 as no union all select 2 union all select 3
대분류1 소분류1 개수1 그룹바이용컬럼
---------- ---------- ----------- ----------
ㄱ 가 1 가
ㄱ 거 1 거
ㄴ 나 2 나
ㄴ 너 1 너
ㄷ 다 1 다
ㄷ 더 0 더
ㄹ 라 1 라
ㄹ 러 1 러
ㄱ 소계 2 가
ㄴ 소계 3 나
ㄷ 소계 1 다
ㄹ 소계 2 라
합계 합계 8
(13개 행 적용됨)
이제 이 [그룹바이용컬럼] 이라는 가공컬럼을 가지고 group by시키면
대분류1 소분류1 개수1 그룹바이용컬럼
---------- ---------- ----------- ----------
ㄱ 가 1 가
ㄱ 소계 2 가
