[SQL] 샘플쿼리

==================================================================
------------------------- 조인의 원리 ----------------------------
==================================================================
create table test01 (
col1 varchar2(2),
col2 number(2)
);

insert into test01 values('A',10);
insert into test01 values('B',20);
insert into test01 values('C',10);
insert into test01 values('D',30);

SELECT a.col1,
a.col2,
SUM(b.col2) col3
FROM test01 a, test01 b
WHERE a.ROWID >= b.ROWID
GROUP BY a.col1, a.col2, a.ROWID
ORDER BY a.ROWID
/



2005.01.18 : 이런 쿼리도 나옵니다

SELECT b.col1,
SUM(DECODE(a.col1, b.col1, a.col2)) col2,
SUM(DECODE(GREATEST(a.col1, b.col1), b.col1, a.col2)) col3
FROM test01 a, test01 b
GROUP BY b.col1

/

SELECT b.col1 col1,
b.col2 col2,
SUM(DECODE(SIGN(a.rnum - b.rnum),1,NULL, a.col2)) col3
FROM (SELECT ROWNUM rnum, col1, col2 FROM test01) a,
(SELECT ROWNUM rnum, col1, col2 FROM test01) b
GROUP BY b.col1, b.col2


/



SELECT col1,
col2,
SUM(col2) over(ORDER BY col1 ROWS unbounded preceding) col3
FROM test01
/


CO COL2 SUM(B.COL2)
-- ---------- -----------
A 10 10
B 20 30
C 10 40
D 30 70

==================================================================
----------------------- 집합의 다양한 가공 -----------------------
==================================================================
create table test02 (
nm varchar2(10),
job varchar2(20),
gbn varchar2(1)
);

insert into test02 values('김영민','대표이사','A');
insert into test02 values('김영민','고문','A');
insert into test02 values('김영민','회장','B');
insert into test02 values('김영민','HALF SUB 2','C');
insert into test02 values('김영민','FULL SUB 3','C');
insert into test02 values('문성구','교수','A');
insert into test02 values('문성구','홍보','B');
insert into test02 values('문성구','대회준비장','B');
insert into test02 values('문성구','FULL SUB 4','C');
insert into test02 values('문성구','HALF SUB 3','C');
insert into test02 values('문성구','10KM SUB 1','C');
insert into test02 values('서승환','주임','A');
insert into test02 values('서승환','총무','B');
insert into test02 values('서승환','섭외','B');
insert into test02 values('서승환','대회준비위원','B');
insert into test02 values('서승환','10KM SUB 1','C');

select decode(cnt, 1, nm) nm,
max(decode(gbn, 'A', job)) j01,
max(decode(gbn, 'B', job)) j02,
max(decode(gbn, 'C', job)) j03
from (
select a.nm nm, a.job job, a.gbn gbn, count(a.nm) cnt
from test02 a, test02 b
where a.nm = b.nm
and a.gbn = b.gbn
and a.rowid >= b.rowid
group by a.nm, a.job, a.gbn
)
group by nm, cnt
/

NM J01 J02 J03
---------- -------------------- -------------------- --------------------
김영민 대표이사 회장 HALF SUB 2
고문 FULL SUB 3
문성구 교수 홍보 FULL SUB 4
대회준비장 HALF SUB 3
10KM SUB 1
서승환 주임 총무 10KM SUB 1
섭외
대회준비위원

==================================================================
------------------------------ 월 집계 ---------------------------
==================================================================
create table test03 (
STORE VARCHAR2(6),
YYMM VARCHAR2(6),
GCODE VARCHAR2(10),
MCODE VARCHAR2(10),
ITEM VARCHAR2(6),
AMT NUMBER(6)
);

insert into test03 values('마포','200011','남성의류','골프웨어','A0001',100);
insert into test03 values('마포','200011','남성의류','골프웨어','A0002',200);
insert into test03 values('마포','200011','남성의류','골프웨어','A0003',300);
insert into test03 values('마포','200011','남성의류','골프웨어','A0004',400);
insert into test03 values('마포','200011','남성의류','골프웨어','A0005',500);
insert into test03 values('마포','200011','남성의류','골프웨어','A0006',600);
insert into test03 values('마포','200011','남성의류','신사정장','B0001',700);
insert into test03 values('마포','200011','남성의류','신사정장','B0002',800);
insert into test03 values('마포','200011','남성의류','신사정장','B0003',900);
insert into test03 values('마포','200011','남성의류','신사정장','B0004',1000);
insert into test03 values('마포','200011','남성의류','신사정장','B0005',1100);
insert into test03 values('마포','200011','남성의류','신사정장','B0006',1200);
insert into test03 values('마포','200011','남성의류','신사정장','B0007',1300);
insert into test03 values('마포','200011','남성의류','신사정장','B0008',2000);
insert into test03 values('마포','200011','남성의류','신사정장','B0009',1400);
insert into test03 values('마포','200011','남성의류','신사정장','B0010',2300);

SELECT S2.store, S2.gcode, S2.mcode, S2.item,
S2.amt, S2.igroup, S1.sol
FROM ( SELECT T.store, T.yymm, T.gcode, T.mcode, T.igroup,
FLOOR(SUM(T.amt) / T.amt_total * 10) * 10 sol
FROM ( SELECT Y.store, Y.yymm, Y.gcode, Y.mcode,
Y.item, Y.amt, X.amt_total,
DECODE(SIGN(10 - SUM(X.per)), 1, 'A', 0, 'A',
DECODE(SIGN(40 - SUM(X.per)), 1, 'B', 0, 'B',
DECODE(SIGN(80 - SUM(X.per)), 1, 'C', 0, 'C', 'D'))) igroup
FROM test03 Y,
( SELECT A.store, A.yymm, A.gcode, A.mcode, A.item, A.amt,
B.amt_total, 100 / B.cnt per
FROM test03 A,
( SELECT store, yymm, gcode, mcode,
count(*) cnt, sum(amt) amt_total
FROM test03
WHERE store = '마포'
AND yymm = '200011'
AND gcode = '남성의류'
GROUP BY store, yymm, gcode, mcode ) B
WHERE A.store = B.store
AND A.yymm = B.yymm
AND A.gcode = B.gcode
AND A.mcode = B.mcode ) X
WHERE Y.store = X.store
AND Y.yymm = X.yymm
AND Y.gcode = X.gcode
AND Y.mcode = X.mcode
AND Y.amt <= X.amt
GROUP BY Y.store, Y.yymm, Y.gcode, Y.mcode,
Y.item, Y.amt, X.amt_total ) T
GROUP BY T.store, T.yymm, T.gcode, T.mcode, T.amt_total, T.igroup ) S1,
( SELECT Y.store, Y.yymm, Y.gcode, Y.mcode,
Y.item, Y.amt, X.amt_total,
DECODE(SIGN(10 - SUM(X.per)), 1, 'A', 0, 'A',
DECODE(SIGN(40 - SUM(X.per)), 1, 'B', 0, 'B',
DECODE(SIGN(80 - SUM(X.per)), 1, 'C', 0, 'C', 'D'))) igroup
FROM test03 Y,
( SELECT A.store, A.yymm, A.gcode, A.mcode, A.item, A.amt,
B.amt_total, 100 / B.cnt per
FROM test03 A,
( SELECT store, yymm, gcode, mcode,
count(*) cnt, sum(amt) amt_total
FROM test03
WHERE store = '마포'
AND yymm = '200011'
AND gcode = '남성의류'
GROUP BY store, yymm, gcode, mcode ) B
WHERE A.store = B.store
AND A.yymm = B.yymm
AND A.gcode = B.gcode
AND A.mcode = B.mcode ) X
WHERE Y.store = X.store
AND Y.yymm = X.yymm
AND Y.gcode = X.gcode
AND Y.mcode = X.mcode
AND Y.amt <= X.amt
GROUP BY Y.store, Y.yymm, Y.gcode, Y.mcode,
Y.item, Y.amt, X.amt_total ) S2
WHERE S2.store = S1.store
AND S2.yymm = S1.yymm
AND S2.gcode = S1.gcode
AND S2.mcode = S1.mcode
AND S2.igroup = S1.igroup
ORDER BY S2.mcode, S2.amt DESC

STORE GCODE MCODE ITEM AMT I SOL
------ ---------- ---------- ------ ---------- - ----------
마포 남성의류 골프웨어 A0006 600 B 50
마포 남성의류 골프웨어 A0005 500 B 50
마포 남성의류 골프웨어 A0004 400 C 30
마포 남성의류 골프웨어 A0003 300 C 30
마포 남성의류 골프웨어 A0002 200 D 10
마포 남성의류 골프웨어 A0001 100 D 10
마포 남성의류 신사정장 B0010 2300 A 10
마포 남성의류 신사정장 B0008 2000 B 30
마포 남성의류 신사정장 B0009 1400 B 30
마포 남성의류 신사정장 B0007 1300 B 30
마포 남성의류 신사정장 B0006 1200 C 30
마포 남성의류 신사정장 B0005 1100 C 30
마포 남성의류 신사정장 B0004 1000 C 30
마포 남성의류 신사정장 B0003 900 C 30
마포 남성의류 신사정장 B0002 800 D 10
마포 남성의류 신사정장 B0001 700 D 10

==================================================================
------------------------- 부서별 매출 2위 ------------------------
==================================================================
create table test04 (
dept varchar2(10),
name varchar2(10),
yymm varchar2(6),
qt number(3)
);

insert into test04 values('총무부','홍길동','200101',200);
insert into test04 values('총무부','임꺽정','200101',250);
insert into test04 values('총무부','장길산','200101',100);
insert into test04 values('총무부','최고봉','200101',200);
insert into test04 values('총무부','황비홍','200101',120);
insert into test04 values('영업부','궁해','200101',300);
insert into test04 values('영업부','왕곤','200101',310);
insert into test04 values('영업부','견온','200101',220);
insert into test04 values('생산부','박임자','200101',350);
insert into test04 values('생산부','전본인','200101',300);
insert into test04 values('생산부','노친구','200101',300);
insert into test04 values('생산부','김갱제','200101',230);

select b.dept, max(mod(a.qt,m_sal)) mod_sal
from test04 a,
(select dept, max(qt) m_sal from test04 group by dept) b
where a.dept = b.dept
group by b.dept

DEPT MOD_SAL
---------- ----------
생산부 300
영업부 300
총무부 200

==================================================================
------------------------ 집합의 변형 -----------------------------
==================================================================
create table test05 (
MAJOR VARCHAR2(6),
MINOR VARCHAR2(10)
);

insert into test05 values('영업','가마니');
insert into test05 values('영업','나오미');
insert into test05 values('영업','마동탁');
insert into test05 values('물류','사시미');
insert into test05 values('물류','아사달');
insert into test05 values('회계','자몽돌');
insert into test05 values('회계','차이나');
insert into test05 values('회계','카이로');
insert into test05 values('전산','파김치');

SELECT MAJOR,
MAX(DECODE(DEGREE,1,MINOR,NULL)) AS MINOR1,
MAX(DECODE(DEGREE,2,MINOR,NULL)) AS MINOR2,
MAX(DECODE(DEGREE,3,MINOR,NULL)) AS MINOR3
FROM (SELECT A.MAJOR,A.MINOR,COUNT(*) AS DEGREE
FROM TEST05 A, TEST05 B
WHERE A.MAJOR = B.MAJOR
AND B.MINOR <= A.MINOR
GROUP BY A.MAJOR,A.MINOR
)
GROUP BY MAJOR
/

MAJOR MINOR1 MINOR2 MINOR3
------ ---------- ---------- ----------
물류 사시미 아사달
영업 가마니 나오미 마동탁
전산 파김치
회계 자몽돌 차이나 카이로

==================================================================
------------------------ 그룹별 순위 -----------------------------
==================================================================
create table test06 (
gp varchar2(10),
nm varchar2(10),
qt number(5)
)
/
insert into test06 values('경영지원팀','김하나',100);
insert into test06 values('경영지원팀','박하나',200);
insert into test06 values('경영지원팀','서하나',300);
insert into test06 values('B2B사업팀','김투비',100);
insert into test06 values('B2B사업팀','박투비',200);
insert into test06 values('B2B사업팀','서투비',300);
insert into test06 values('B2C사업팀','한투씨',100);
insert into test06 values('B2C사업팀','서투씨',200);
insert into test06 values('B2C사업팀','김투씨',300);
insert into test06 values('B2C사업팀','오투씨',200);
insert into test06 values('B2C사업팀','이투씨',300);
insert into test06 values('B2C사업팀','권투씨',500);

select a.gp, a.nm, count(b.qt)-(cnt-1) rank, a.qt
from test06 a,
test06 b,
(select gp, qt, count(*) cnt from test06 group by gp, qt) c
where a.qt <= b.qt
and a.gp = b.gp
and a.gp = c.gp
and a.qt = c.qt
group by a.gp, a.nm, a.qt, cnt
order by a.gp, a.qt desc

GP NM RANK QT
---------- ---------- ---------- ----------
B2B사업팀 서투비 1 300
B2B사업팀 박투비 2 200
B2B사업팀 김투비 3 100
B2C사업팀 권투씨 1 500
B2C사업팀 김투씨 2 300
B2C사업팀 이투씨 2 300
B2C사업팀 서투씨 4 200
B2C사업팀 오투씨 4 200
B2C사업팀 한투씨 6 100
경영지원팀 서하나 1 300
경영지원팀 박하나 2 200
경영지원팀 김하나 3 100




==================================================================
------------------------ t_copy의 활용 ---------------------------
==================================================================
create table t_copy (
num number(2)
)
/
insert into t_copy values(1);
insert into t_copy values(2);
insert into t_copy values(3);
insert into t_copy values(4);

create table test07 (
dt varchar2(8),
gp varchar2(10),
nm varchar2(10),
qt number(5)
)
/
insert into test07 values('20020101','경영지원팀','김경영',100);
insert into test07 values('20020101','경영지원팀','박경영',200);
insert into test07 values('20020201','경영지원팀','서경영',300);
insert into test07 values('20020202','경영지원팀','김경영',100);
insert into test07 values('20020301','경영지원팀','박경영',200);
insert into test07 values('20020302','경영지원팀','서경영',300);
insert into test07 values('20020303','경영지원팀','김경영',100);
insert into test07 values('20020401','경영지원팀','박경영',200);
insert into test07 values('20020403','경영지원팀','서경영',300);
insert into test07 values('20020404','경영지원팀','김경영',100);
insert into test07 values('20020501','경영지원팀','박경영',200);
insert into test07 values('20020502','경영지원팀','서경영',300);
insert into test07 values('20020101','B2B사업팀','김투비',100);
insert into test07 values('20020101','B2B사업팀','박투비',200);
insert into test07 values('20020201','B2B사업팀','서투비',300);
insert into test07 values('20020202','B2B사업팀','김투비',100);
insert into test07 values('20020301','B2B사업팀','박투비',200);
insert into test07 values('20020302','B2B사업팀','서투비',300);
insert into test07 values('20020303','B2B사업팀','김투비',100);
insert into test07 values('20020401','B2B사업팀','박투비',200);
insert into test07 values('20020403','B2B사업팀','서투비',300);
insert into test07 values('20020404','B2B사업팀','김투비',100);
insert into test07 values('20020501','B2B사업팀','박투비',200);
insert into test07 values('20020502','B2B사업팀','서투비',300);
insert into test07 values('20020101','B2C사업팀','김투씨',100);
insert into test07 values('20020101','B2C사업팀','박투씨',200);
insert into test07 values('20020201','B2C사업팀','서투씨',300);
insert into test07 values('20020202','B2C사업팀','한투씨',100);
insert into test07 values('20020301','B2C사업팀','서투씨',200);
insert into test07 values('20020302','B2C사업팀','김투씨',300);
insert into test07 values('20020303','B2C사업팀','오투씨',200);
insert into test07 values('20020401','B2C사업팀','이투씨',300);
insert into test07 values('20020403','B2C사업팀','권투씨',500);
insert into test07 values('20020404','B2C사업팀','오투씨',200);
insert into test07 values('20020501','B2C사업팀','이투씨',300);
insert into test07 values('20020502','B2C사업팀','권투씨',500);


01. GROUP BY 이용
select
decode(b.num, 3, ', a.gp) gp,
decode(b.num, 3, ', 2, ', a.nm) nm,
decode(b.num, 3, ', 2, ', to_char(to_date(a.dt,'yyyymmdd'), 'yyyy-mm-dd')) dt,
decode(b.num, 3, '총계', 2, '소계') gbn,
sum(decode(b.num, 1, a.qt, 2, a.qt, 3, a.qt)) qt,
count(b.num) cnt
from test07 a, t_copy b
where b.num <= 3
group by
decode(b.num, 3, ', a.gp),
decode(b.num, 3, ', 2, ', a.nm),
decode(b.num, 3, ', 2, ', to_char(to_date(a.dt,'yyyymmdd'), 'yyyy-mm-dd')),
decode(b.num, 3, '총계', 2, '소계'),
b.num
/



02. GROUPING_ID, ROLLUP 이용

SELECT gr, gp, nm, dt, SUM, cnt
FROM (
SELECT GROUPING_ID(gp, nm, dt) gr,
gp, nm, dt, SUM(qt) SUM, COUNT(ROWNUM) cnt
FROM test07
GROUP BY ROLLUP(gp, nm, dt)
)
WHERE gr IN (0, 3, 7)

/


------------------- 부서별 소계/총계 ----------------------
GP NM DT GBN QT CNT
---------- ---------- ---------- ---- ---------- ----------
B2B사업팀 김투비 2002-01-01 100 1
B2B사업팀 김투비 2002-02-02 100 1
B2B사업팀 김투비 2002-03-03 100 1
B2B사업팀 김투비 2002-04-04 100 1
B2B사업팀 박투비 2002-01-01 200 1
B2B사업팀 박투비 2002-03-01 200 1
B2B사업팀 박투비 2002-04-01 200 1
B2B사업팀 박투비 2002-05-01 200 1
B2B사업팀 서투비 2002-02-01 300 1
B2B사업팀 서투비 2002-03-02 300 1
B2B사업팀 서투비 2002-04-03 300 1
B2B사업팀 서투비 2002-05-02 300 1
B2B사업팀 소계 2400 12
B2C사업팀 권투씨 2002-04-03 500 1
B2C사업팀 권투씨 2002-05-02 500 1
B2C사업팀 김투씨 2002-01-01 100 1
B2C사업팀 김투씨 2002-03-02 300 1
B2C사업팀 박투씨 2002-01-01 200 1
B2C사업팀 서투씨 2002-02-01 300 1
B2C사업팀 서투씨 2002-03-01 200 1
B2C사업팀 오투씨 2002-03-03 200 1
B2C사업팀 오투씨 2002-04-04 200 1
B2C사업팀 이투씨 2002-04-01 300 1
B2C사업팀 이투씨 2002-05-01 300 1
B2C사업팀 한투씨 2002-02-02 100 1
B2C사업팀 소계 3200 12
경영지원팀 김경영 2002-01-01 100 1
경영지원팀 김경영 2002-02-02 100 1
경영지원팀 김경영 2002-03-03 100 1
경영지원팀 김경영 2002-04-04 100 1
경영지원팀 박경영 2002-01-01 200 1
경영지원팀 박경영 2002-03-01 200 1
경영지원팀 박경영 2002-04-01 200 1
경영지원팀 박경영 2002-05-01 200 1
경영지원팀 서경영 2002-02-01 300 1
경영지원팀 서경영 2002-03-02 300 1
경영지원팀 서경영 2002-04-03 300 1
경영지원팀 서경영 2002-05-02 300 1
경영지원팀 소계 2400 12
총계 8000 36

------------------- 월별 부서별 소계/총계 ------------------
select
decode(b.num, 4, ', a.gp) gp,
decode(b.num, 4, ', 3, ', substr(a.dt,1,6)) dt,
decode(b.num, 1, a.nm) nm,
decode(b.num, 4, '총계', 3, '팀계',2, '월계') tot,
sum(decode(b.num, 1, a.qt, 2, a.qt, 3, a.qt, 4,a.qt)) qt,
count(b.num) cnt
from test07 a, t_copy b
where b.num <= 4
group by
decode(b.num, 4, ', a.gp),
decode(b.num, 4, ', 3, ', substr(a.dt,1,6)),
b.num,
decode(b.num, 1, a.nm),
decode(b.num, 4, '총계', 3, '팀계',2, '월계')
/

GP DT NM TOT QT CNT
---------- ------------ ---------- ---- ---------- ----------
B2B사업팀 200201 김투비 100 1
B2B사업팀 200201 박투비 200 1
B2B사업팀 200201 월계 300 2
B2B사업팀 200202 김투비 100 1
B2B사업팀 200202 서투비 300 1
B2B사업팀 200202 월계 400 2
B2B사업팀 200203 김투비 100 1
B2B사업팀 200203 박투비 200 1
B2B사업팀 200203 서투비 300 1
B2B사업팀 200203 월계 600 3
B2B사업팀 200204 김투비 100 1
B2B사업팀 200204 박투비 200 1
B2B사업팀 200204 서투비 300 1
B2B사업팀 200204 월계 600 3
B2B사업팀 200205 박투비 200 1
B2B사업팀 200205 서투비 300 1
B2B사업팀 200205 월계 500 2
B2B사업팀 팀계 2400 12
B2C사업팀 200201 김투씨 100 1
B2C사업팀 200201 박투씨 200 1
B2C사업팀 200201 월계 300 2
B2C사업팀 200202 서투씨 300 1
B2C사업팀 200202 한투씨 100 1
B2C사업팀 200202 월계 400 2
B2C사업팀 200203 김투씨 300 1
B2C사업팀 200203 서투씨 200 1
B2C사업팀 200203 오투씨 200 1
B2C사업팀 200203 월계 700 3
B2C사업팀 200204 권투씨 500 1
B2C사업팀 200204 오투씨 200 1
B2C사업팀 200204 이투씨 300 1
B2C사업팀 200204 월계 1000 3
B2C사업팀 200205 권투씨 500 1
B2C사업팀 200205 이투씨 300 1
B2C사업팀 200205 월계 800 2
B2C사업팀 팀계 3200 12
경영지원팀 200201 김경영 100 1
경영지원팀 200201 박경영 200 1
경영지원팀 200201 월계 300 2
경영지원팀 200202 김경영 100 1
경영지원팀 200202 서경영 300 1
경영지원팀 200202 월계 400 2
경영지원팀 200203 김경영 100 1
경영지원팀 200203 박경영 200 1
경영지원팀 200203 서경영 300 1
경영지원팀 200203 월계 600 3
경영지원팀 200204 김경영 100 1
경영지원팀 200204 박경영 200 1
경영지원팀 200204 서경영 300 1
경영지원팀 200204 월계 600 3
경영지원팀 200205 박경영 200 1
경영지원팀 200205 서경영 300 1
경영지원팀 200205 월계 500 2
경영지원팀 팀계 2400 12
총계 8000 36