데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 41630 게시물 읽기

No. 41630

쿼리문을 바꾸려 합니다.
작성자	초보	작성일	2018-09-08 15:13	조회수	4,373

프로그램에서 하던 원장을 쿼리문으로 바꾸려 합니다.

데이터베이스 작업을 한지가 오래되서 구현이 잘 안되네요.

정중히 도움을 청합니다.

사용 환경 : 오라클11g, 12c/ SQL SERVER 2016, 2017

(SELECT 'QT06' rawcod,'20141111' iodate,'I' io,05 seq,'N' iotype,'003' vendcd,'AM19' grade,'01' colorcd,'1429207' lotno,475 pur_qty,0 out_qty,'2014-11-20 12:28:50' cre_date FROM DUAL UNION ALL

SELECT 'QT01', '20141114', 'I', 01, 'N', '002', 'AM03', '01', '4H72U', 1000, 0, '2014-11-20 12:04:33' FROM DUAL UNION ALL

SELECT 'QT01', '20141119', 'I', 07, 'N', '002', 'AM05', '01', '4J9030U', 200, 0, '2014-11-20 12:30:36' FROM DUAL UNION ALL

SELECT 'QT01', '20141119', 'I', 04, 'N', '002', 'AM04', '01', '4F3022U', 500, 0, '2014-11-20 12:28:02' FROM DUAL UNION ALL

SELECT 'QT01', '20141121', 'I', 09, 'N', '002', 'AM02', '01', '4K6020U', 825, 0, '2014-11-26 14:21:20' FROM DUAL UNION ALL

SELECT 'QT06', '20141125', 'I', 10, 'N', '003', 'AM20', '01', '1428707', 850, 0, '2014-11-26 14:22:21' FROM DUAL UNION ALL

SELECT 'QT01', '20141126', 'I', 13, 'N', '002', 'AM02', '01', '4K6020U', 1000, 0, '2014-11-26 14:42:41' FROM DUAL UNION ALL

SELECT 'QT01', '20141126', 'I', 12, 'N', '002', 'AM03', '01', '4111P', 1000, 0, '2014-11-26 14:23:15' FROM DUAL UNION ALL

SELECT 'QT01', '20141126', 'I', 11, 'N', '002', 'AM03', '01', '4G30U', 1000, 0, '2014-11-26 14:22:51' FROM DUAL UNION ALL

SELECT 'QT01', '20141128', 'I', 16, 'N', '002', 'AM03', '01', '4H27U', 1000, 0, '2014-11-28 09:17:06' FROM DUAL UNION ALL

SELECT 'QT01', '20141128', 'I', 15, 'N', '002', 'AM02', '01', '4J6021U', 1000, 0, '2014-11-28 09:16:36' FROM DUAL UNION ALL

SELECT 'QT06', '20141128', 'I', 14, 'N', '003', 'AM19', '01', '1431907', 1000, 0, '2014-11-28 09:15:56' FROM DUAL UNION ALL

SELECT 'QT01', '20150102', 'I', 17, 'N', '002', 'AM03', '01', '4L28U', 75, 0, '2015-01-08 14:30:02' FROM DUAL UNION ALL

SELECT 'QT02', '20150106', 'I', 24, 'N', '002', 'AM05', '01', '4K9022U', 75, 0, '2015-01-08 14:36:02' FROM DUAL UNION ALL

SELECT 'QT02', '20150106', 'I', 23, 'N', '002', 'AC06', '01', '4I9011U', 200, 0, '2015-01-08 14:34:49' FROM DUAL UNION ALL

SELECT 'QT06', '20150106', 'I', 22, 'N', '003', 'AM20', '01', '1428707', 200, 0, '2015-01-08 14:33:55' FROM DUAL UNION ALL

SELECT 'QT05', '20150106', 'I', 21, 'N', '003', 'AM12', '01', '14340B1', 175, 0, '2015-01-08 14:33:18' FROM DUAL UNION ALL

SELECT 'QT06', '20150106', 'I', 20, 'N', '003', 'AM19', '01', '1435607', 500, 0, '2015-01-08 14:32:35' FROM DUAL UNION ALL

SELECT 'QT01', '20150106', 'I', 19, 'N', '002', 'AM02', '01', '4L4122U', 750, 0, '2015-01-08 14:31:43' FROM DUAL UNION ALL

SELECT 'QT01', '20150106', 'I', 18, 'N', '002', 'AM03', '01', '4L02P', 1200, 0, '2015-01-08 14:31:05' FROM DUAL UNION ALL

SELECT 'QT02', '20150210', 'I', 30, 'N', '002', 'AM05', '01', '987654', 75, 0, '2015-02-10 18:13:19' FROM DUAL UNION ALL

SELECT 'QT01', '20150210', 'I', 29, 'N', '002', 'AM03', '01', '123456', 75, 0, '2015-02-10 17:58:46' FROM DUAL UNION ALL

SELECT 'QT01', '20150210', 'I', 28, 'N', '002', 'AM03', '01', '567890-', 125, 0, '2015-02-10 17:35:36' FROM DUAL UNION ALL

SELECT 'QT01', '20150504', 'I', 37, 'N', '001', 'AM07', '01', '4F26U', 50, 0, '2015-06-08 08:21:33' FROM DUAL UNION ALL

SELECT 'QT06', '20150506', 'I', 36, 'N', '001', 'AB23', '01', '15010003', 250, 0, '2015-06-08 08:20:42' FROM DUAL UNION ALL

SELECT 'QT01', '20150522', 'I', 31, 'N', '001', 'AM02', '01', '5E6021U', 575, 0, '2015-06-08 08:12:26' FROM DUAL UNION ALL

SELECT 'QT06', '20150604', 'I', 32, 'N', '001', 'AM19', '01', '1514507', 250, 0, '2015-06-08 08:16:56' FROM DUAL UNION ALL

SELECT 'QT06', '20150606', 'I', 34, 'N', '001', 'AM20', '01', '150907', 250, 0, '2015-06-08 08:18:57' FROM DUAL UNION ALL

SELECT 'QT01', '20150608', 'I', 38, 'N', '002', 'AM02', '01', '5E6021U', 575, 0, '2015-06-08 08:23:26' FROM DUAL UNION ALL

SELECT 'QT01', '20150707', 'I', 39, 'N', '002', 'AM02', '01', '1111', 250, 0, '2015-07-07 09:53:34' FROM DUAL UNION ALL

SELECT 'QT01', '20160125', 'I', 44, 'N', '002', 'AM02', '01', '5L4027U', 350, 0, '2016-01-26 15:35:06' FROM DUAL UNION ALL

SELECT 'QT01', '20160126', 'I', 43, 'N', '002', 'AM03', '01', '5L09P', 1000, 0, '2016-01-26 15:31:27' FROM DUAL UNION ALL

SELECT 'QT01', '20160126', 'I', 45, 'N', '002', 'AM02', '01', '6A2005U', 1000, 0, '2016-01-26 15:36:57' FROM DUAL UNION ALL

SELECT 'QT01', '20160716', 'I', 42, 'M', '002', 'AM02', '01', 'F4020', 36, 0, '2015-07-16 15:36:26' FROM DUAL UNION ALL

SELECT 'QT01', '20150210', 'O', 03, 'MP', '11212305', 'AM03', '01', 'NQT0120150210001', 0, 5, '2015-02-10 17:39:22' FROM DUAL UNION ALL

SELECT 'QT01', '20150210', 'O', 02, 'NM', '11212305', 'AM03', '01', 'NQT0120150210001', 0, 25, '2015-02-10 17:36:52' FROM DUAL UNION ALL

SELECT 'QT02', '20150210', 'O', 07, 'MP', '11212505', 'AM05', '01', 'NQT0220150210001', 0, 15, '2015-02-10 18:15:50' FROM DUAL UNION ALL

SELECT 'QT02', '20150210', 'O', 06, 'NP', '11212505', 'AM05', '01', 'NQT0220150210001', 0, 25, '2015-02-10 18:15:17' FROM DUAL UNION ALL

SELECT 'QT02', '20150210', 'O', 05, 'NR', '11212505', 'AM05', '01', 'NQT0220150210001', 0, 25, '2015-02-10 18:14:00' FROM DUAL UNION ALL

SELECT 'QT01', '20150210', 'O', 04, 'NM', '11212508', 'AM03', '01', 'NQT0120150210007', 0, 25, '2015-02-10 18:06:00' FROM DUAL

)

SELECT s.rawcod

, s.iodate

, s.io

, s.seq

, s.iotype, t.name AS iotype

, s.vendcd, v.company

, s.grade, g.name AS gradenm

, s.colorcd, c.name AS colornm

, s.lotno

, s.pur_qty

, s.out_qty

, s.cre_date

FROM slips s

LEFT JOIN sycmms01 t ON t.major = 'RT' AND t.minor = s.iotype

LEFT JOIN sycmms01 r ON r.major = 'QT' AND r.minor = s.rawcod

LEFT JOIN sycmms01 g ON g.major = 'GR' AND g.minor = s.grade

LEFT JOIN sycmms01 c ON c.major = 'CL' AND c.minor = s.colorcd

LEFT JOIN sybsms00 v ON v.sitecd = s.vendcd

WHERE s.iodate BETWEEN '20141115' AND '20151231'

ORDER BY rawcod, iodate, cre_date

RAWCOD IODATE IO SEQ IOTYPE IONAME VENDCD COMPANY GRADE GRADENM COLORCD COLORNM LOTNO PUR_QTY OUT_QTY CRE_DATE

QT01 20141114 I 1 N 신재 002 KEP AM03 FU2020 01 NAT 4H72U 1000 0 2014-11-20 12:04:33

QT01 20141119 I 4 N 신재 002 KEP AM04 F20-52 01 NAT 4F3022U 500 0 2014-11-20 12:28:02

QT01 20141119 I 7 N 신재 002 KEP AM05 F20-52 LOF 01 NAT 4J9030U 200 0 2014-11-20 12:30:36

QT01 20141121 I 9 N 신재 002 KEP AM02 F20-03 01 NAT 4K6020U 825 0 2014-11-26 14:21:20

QT01 20141126 I 11 N 신재 002 KEP AM03 FU2020 01 NAT 4G30U 1000 0 2014-11-26 14:22:51

QT01 20141126 I 12 N 신재 002 KEP AM03 FU2020 01 NAT 4111P 1000 0 2014-11-26 14:23:15

QT01 20141126 I 13 N 신재 002 KEP AM02 F20-03 01 NAT 4K6020U 1000 0 2014-11-26 14:42:41

QT01 20141128 I 15 N 신재 002 KEP AM02 F20-03 01 NAT 4J6021U 1000 0 2014-11-28 09:16:36

QT01 20141128 I 16 N 신재 002 KEP AM03 FU2020 01 NAT 4H27U 1000 0 2014-11-28 09:17:06

QT01 20150102 I 17 N 신재 002 KEP AM03 FU2020 01 NAT 4L28U 75 0 2015-01-08 14:30:02

QT01 20150106 I 18 N 신재 002 KEP AM03 FU2020 01 NAT 4L02P 1200 0 2015-01-08 14:31:05

QT01 20150106 I 19 N 신재 002 KEP AM02 F20-03 01 NAT 4L4122U 750 0 2015-01-08 14:31:43

QT01 20150210 I 28 N 신재 002 KEP AM03 FU2020 01 NAT 567890- 125 0 2015-02-10 17:35:36

QT01 20150210 O 2 NM 신재->배합창고 11212305 150TON10호기 AM03 FU2020 01 NAT NQT0120150210001 0 25 2015-02-10 17:36:52

QT01 20150210 O 3 MP 사용대기(배합)->생산 11212305 150TON10호기 AM03 FU2020 01 NAT NQT0120150210001 0 5 2015-02-10 17:39:22

QT01 20150210 I 29 N 신재 002 KEP AM03 FU2020 01 NAT 123456 75 0 2015-02-10 17:58:46

QT01 20150210 O 4 NM 신재->배합창고 11212508 120TON9호기 AM03 FU2020 01 NAT NQT0120150210007 0 25 2015-02-10 18:06:00

QT01 20150504 I 37 N 신재 001 신일상사 AM07 F2U2020 LOF 01 NAT 4F26U 50 0 2015-06-08 08:21:33

QT01 20150522 I 31 N 신재 001 신일상사 AM02 F20-03 01 NAT 5E6021U 575 0 2015-06-08 08:12:26

QT01 20150608 I 38 N 신재 002 KEP AM02 F20-03 01 NAT 5E6021U 575 0 2015-06-08 08:23:26

QT01 20150707 I 39 N 신재 002 KEP AM02 F20-03 01 NAT 1111 250 0 2015-07-07 09:53:34

QT01 20160125 I 44 N 신재 002 KEP AM02 F20-03 01 NAT 5L4027U 350 0 2016-01-26 15:35:06

QT01 20160126 I 43 N 신재 002 KEP AM03 FU2020 01 NAT 5L09P 1000 0 2016-01-26 15:31:27

QT01 20160126 I 45 N 신재 002 KEP AM02 F20-03 01 NAT 6A2005U 1000 0 2016-01-26 15:36:57

QT01 20160716 I 42 M 배합 002 KEP AM02 F20-03 01 NAT F4020 36 0 2015-07-16 15:36:26

QT02 20150106 I 23 N 신재 002 KEP AC06 F20-03 LOF 01 NAT 4I9011U 200 0 2015-01-08 14:34:49

QT02 20150106 I 24 N 신재 002 KEP AM05 F20-52 LOF 01 NAT 4K9022U 75 0 2015-01-08 14:36:02

QT02 20150210 I 30 N 신재 002 KEP AM05 F20-52 LOF 01 NAT 987654 75 0 2015-02-10 18:13:19

QT02 20150210 O 5 NR 신재->대기 11212505 150TON10호기 AM05 F20-52 LOF 01 NAT NQT0220150210001 0 25 2015-02-10 18:14:00

QT02 20150210 O 6 NP 대기->생산 11212505 150TON10호기 AM05 F20-52 LOF 01 NAT NQT0220150210001 0 25 2015-02-10 18:15:17

QT02 20150210 O 7 MP 사용대기(배합->생산) 11212505 150TON10호기 AM05 F20-52 LOF 01 NAT NQT0220150210001 0 15 2015-02-10 18:15:50

QT05 20150106 I 21 N 신재 003 SOLVAY AM12 1015CR 01 NAT 14340B1 175 0 2015-01-08 14:33:18

QT06 20141111 I 5 N 신재 003 SOLVAY AM19 2110R 01 NAT 1429207 475 0 2014-11-20 12:28:50

QT06 20141125 I 10 N 신재 003 SOLVAY AM20 2710HI 01 NAT 1428707 850 0 2014-11-26 14:22:21

QT06 20141128 I 14 N 신재 003 SOLVAY AM19 2110R 01 NAT 1431907 1000 0 2014-11-28 09:15:56

QT06 20150106 I 20 N 신재 003 SOLVAY AM19 2110R 01 NAT 1435607 500 0 2015-01-08 14:32:35

QT06 20150106 I 22 N 신재 003 SOLVAY AM20 2710HI 01 NAT 1428707 200 0 2015-01-08 14:33:55

QT06 20150506 I 36 N 신재 001 신일상사 AB23 1021ST 01 NAT 15010003 250 0 2015-06-08 08:20:42

QT06 20150604 I 32 N 신재 001 신일상사 AM19 2110R 01 NAT 1514507 250 0 2015-06-08 08:16:56

QT06 20150606 I 34 N 신재 001 신일상사 AM20 2710HI 01 NAT 150907 250 0 2015-06-08 08:18:57

아래 같이 원장 형식으로 조회하려 합니다.

일 자 구 분 매입처/설비명 GRADE 색상 LOTNO 입 고 출 고 재 고 저장 일시

2014-11-15 ** 이 월 ** 1,000 1,000 2010-12-31 13:01:00

2014-11-19 신재 KEP F20-52 NAT 4F3022U 500 1,500 2014-11-20 12:28:02

2014-11-19 신재 KEP F20-52 LOF NAT 4J9030U 200 1,700 2014-11-20 12:30:36

2014-11-21 신재 KEP F20-03 NAT 4K6020U 825 2,525 2014-11-26 14:21:20

2014-11-26 신재 KEP FU2020 NAT 4G30U 1,000 3,525 2014-11-26 14:22:51

2014-11-26 신재 KEP FU2020 NAT 4111P 1,000 4,525 2014-11-26 14:23:15

2014-11-26 신재 KEP F20-03 NAT 4K6020U 1,000 5,525 2014-11-26 14:42:41

2014-11-28 신재 KEP F20-03 NAT 4J6021U 1,000 6,525 2014-11-28 09:16:36

2014-11-28 신재 KEP FU2020 NAT 4H27U 1,000 7,525 2014-11-28 09:17:06

** 월 계 ** 7,525 7,525 2014-11-28 09:17:06

** 누 계 ** 7,525 7,525 2014-11-28 09:17:06

2015-01-02 신재 KEP FU2020 NAT 4L28U 75 7,600 2015-01-08 14:30:02

2015-01-06 신재 KEP FU2020 NAT 4L02P 1,200 8,800 2015-01-08 14:31:05

2015-01-06 신재 KEP F20-03 NAT 4L4122U 750 9,550 2015-01-08 14:31:43

** 월 계 ** 2,025 9,550 2015-01-08 14:31:43

** 누 계 ** 9,550 9,550 2015-01-08 14:31:43

2015-02-10 신재 KEP FU2020 NAT 567890- 125 9,675 2015-02-10 14:35:36

2015-02-10 신재->배합창고 150TON 10호기 FU2020 NAT NQT0120150210001 25 9,650 2015-02-10 17:36:52

2015-02-10 사용대기(배합)->생산 150TON 10호기 FU2020 NAT NQT0120150210001 5 9,645 2015-02-10 17:39:22

2015-02-10 신재 KEP FU2020 NAT 123456 75 9,720 2015-02-10 17:58:46

2015-02-10 신재->배합창고 120TON 9호기 FU2020 NAT NQT0120150210007 25 9,695 2015-02-10 18:06:00

** 월 계 ** 200 55 9,695 2015-02-10 18:06:00

** 누 계 ** 9,750 55 9,695 2015-02-10 18:06:00

2015-05-04 신재 신일상사 F2U2020 LOF NAT 4F26U 50 10,320 2015-06-08 20:21:33

2015-05-22 신재 신일상사 F20-03 NAT 5E6021U 575 10,270 2015-06-08 20:12:26

** 월 계 ** 625 10,320 2015-06-08 20:21:33

** 누 계 ** 10,375 55 10,320 2015-06-08 20:21:33

2015-06-08 신재 KEP F20-03 NAT 5E6021U 575 10,895 2015-06-08 20:23:26

** 월 계 ** 575 10,895 2015-06-08 20:23:26

** 누 계 ** 10,950 55 10,895 2015-06-08 20:23:26

2015-07-07 신재 KEP F20-03 NAT 1111 250 11,145 2015-07-07 09:53:34

** 월 계 ** 250 11,145 2015-07-07 09:53:34

** 누 계 ** 11,200 55 11,145 2015-07-07 09:53:34

이 글에 대한 댓글이 총 3건 있습니다.

-- Oracle --
WITH slips AS
(
SELECT 'QT06' rawcod, '20141111' iodate, 'I' io, 05 seq, 'N' iotype, '003' vendcd, 'AM19' grade
, '01' colorcd, '1429207' lotno, 475 pur_qty, 0 out_qty, '2014-11-20 12:28:50' cre_date FROM dual
UNION ALL SELECT 'QT01', '20141114', 'I', 01, 'N', '002',      'AM03', '01', '4H72U',            1000, 0, '2014-11-20 12:04:33' FROM dual
UNION ALL SELECT 'QT01', '20141119', 'I', 07, 'N', '002',      'AM05', '01', '4J9030U',           200, 0, '2014-11-20 12:30:36' FROM dual
UNION ALL SELECT 'QT01', '20141119', 'I', 04, 'N', '002',      'AM04', '01', '4F3022U',           500, 0, '2014-11-20 12:28:02' FROM dual
UNION ALL SELECT 'QT01', '20141121', 'I', 09, 'N', '002',      'AM02', '01', '4K6020U',           825, 0, '2014-11-26 14:21:20' FROM dual
UNION ALL SELECT 'QT06', '20141125', 'I', 10, 'N', '003',      'AM20', '01', '1428707',           850, 0, '2014-11-26 14:22:21' FROM dual
UNION ALL SELECT 'QT01', '20141126', 'I', 13, 'N', '002',      'AM02', '01', '4K6020U',          1000, 0, '2014-11-26 14:42:41' FROM dual
UNION ALL SELECT 'QT01', '20141126', 'I', 12, 'N', '002',      'AM03', '01', '4111P',            1000, 0, '2014-11-26 14:23:15' FROM dual
UNION ALL SELECT 'QT01', '20141126', 'I', 11, 'N', '002',      'AM03', '01', '4G30U',            1000, 0, '2014-11-26 14:22:51' FROM dual
UNION ALL SELECT 'QT01', '20141128', 'I', 16, 'N', '002',      'AM03', '01', '4H27U',            1000, 0, '2014-11-28 09:17:06' FROM dual
UNION ALL SELECT 'QT01', '20141128', 'I', 15, 'N', '002',      'AM02', '01', '4J6021U',          1000, 0, '2014-11-28 09:16:36' FROM dual
UNION ALL SELECT 'QT06', '20141128', 'I', 14, 'N', '003',      'AM19', '01', '1431907',          1000, 0, '2014-11-28 09:15:56' FROM dual
UNION ALL SELECT 'QT01', '20150102', 'I', 17, 'N', '002',      'AM03', '01', '4L28U',              75, 0, '2015-01-08 14:30:02' FROM dual
UNION ALL SELECT 'QT02', '20150106', 'I', 24, 'N', '002',      'AM05', '01', '4K9022U',            75, 0, '2015-01-08 14:36:02' FROM dual
UNION ALL SELECT 'QT02', '20150106', 'I', 23, 'N', '002',      'AC06', '01', '4I9011U',           200, 0, '2015-01-08 14:34:49' FROM dual
UNION ALL SELECT 'QT06', '20150106', 'I', 22, 'N', '003',      'AM20', '01', '1428707',           200, 0, '2015-01-08 14:33:55' FROM dual
UNION ALL SELECT 'QT05', '20150106', 'I', 21, 'N', '003',      'AM12', '01', '14340B1',           175, 0, '2015-01-08 14:33:18' FROM dual
UNION ALL SELECT 'QT06', '20150106', 'I', 20, 'N', '003',      'AM19', '01', '1435607',           500, 0, '2015-01-08 14:32:35' FROM dual
UNION ALL SELECT 'QT01', '20150106', 'I', 19, 'N', '002',      'AM02', '01', '4L4122U',           750, 0, '2015-01-08 14:31:43' FROM dual
UNION ALL SELECT 'QT01', '20150106', 'I', 18, 'N', '002',      'AM03', '01', '4L02P',            1200, 0, '2015-01-08 14:31:05' FROM dual
UNION ALL SELECT 'QT02', '20150210', 'I', 30, 'N', '002',      'AM05', '01', '987654',             75, 0, '2015-02-10 18:13:19' FROM dual
UNION ALL SELECT 'QT01', '20150210', 'I', 29, 'N', '002',      'AM03', '01', '123456',             75, 0, '2015-02-10 17:58:46' FROM dual
UNION ALL SELECT 'QT01', '20150210', 'I', 28, 'N', '002',      'AM03', '01', '567890-',           125, 0, '2015-02-10 17:35:36' FROM dual
UNION ALL SELECT 'QT01', '20150504', 'I', 37, 'N', '001',      'AM07', '01', '4F26U',              50, 0, '2015-06-08 08:21:33' FROM dual
UNION ALL SELECT 'QT06', '20150506', 'I', 36, 'N', '001',      'AB23', '01', '15010003',          250, 0, '2015-06-08 08:20:42' FROM dual
UNION ALL SELECT 'QT01', '20150522', 'I', 31, 'N', '001',      'AM02', '01', '5E6021U',           575, 0, '2015-06-08 08:12:26' FROM dual
UNION ALL SELECT 'QT06', '20150604', 'I', 32, 'N', '001',      'AM19', '01', '1514507',           250, 0, '2015-06-08 08:16:56' FROM dual
UNION ALL SELECT 'QT06', '20150606', 'I', 34, 'N', '001',      'AM20', '01', '150907',            250, 0, '2015-06-08 08:18:57' FROM dual
UNION ALL SELECT 'QT01', '20150608', 'I', 38, 'N', '002',      'AM02', '01', '5E6021U',           575, 0, '2015-06-08 08:23:26' FROM dual
UNION ALL SELECT 'QT01', '20150707', 'I', 39, 'N', '002',      'AM02', '01', '1111',              250, 0, '2015-07-07 09:53:34' FROM dual
UNION ALL SELECT 'QT01', '20160125', 'I', 44, 'N', '002',      'AM02', '01', '5L4027U',           350, 0, '2016-01-26 15:35:06' FROM dual
UNION ALL SELECT 'QT01', '20160126', 'I', 43, 'N', '002',      'AM03', '01', '5L09P',            1000, 0, '2016-01-26 15:31:27' FROM dual
UNION ALL SELECT 'QT01', '20160126', 'I', 45, 'N', '002',      'AM02', '01', '6A2005U',          1000, 0, '2016-01-26 15:36:57' FROM dual
UNION ALL SELECT 'QT01', '20160716', 'I', 42, 'M', '002',      'AM02', '01', 'F4020',              36, 0, '2015-07-16 15:36:26' FROM dual
UNION ALL SELECT 'QT01', '20150210', 'O', 03, 'MP', '11212305', 'AM03', '01', 'NQT0120150210001',    0, 5, '2015-02-10 17:39:22' FROM dual
UNION ALL SELECT 'QT01', '20150210', 'O', 02, 'NM', '11212305', 'AM03', '01', 'NQT0120150210001',    0, 25, '2015-02-10 17:36:52' FROM dual
UNION ALL SELECT 'QT02', '20150210', 'O', 07, 'MP', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 15, '2015-02-10 18:15:50' FROM dual
UNION ALL SELECT 'QT02', '20150210', 'O', 06, 'NP', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 25, '2015-02-10 18:15:17' FROM dual
UNION ALL SELECT 'QT02', '20150210', 'O', 05, 'NR', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 25, '2015-02-10 18:14:00' FROM dual
UNION ALL SELECT 'QT01', '20150210', 'O', 04, 'NM', '11212508', 'AM03', '01', 'NQT0120150210007',    0, 25, '2015-02-10 18:06:00' FROM dual
)
, sybsms00 AS
(
SELECT '001' sitecd, '신일상사' company     FROM dual
UNION ALL SELECT '002'     , 'KEP'          FROM dual
UNION ALL SELECT '003'     , 'SOLVAY'       FROM dual
UNION ALL SELECT '11212305', '150TON10호기' FROM dual
UNION ALL SELECT '11212505', '150TON10호기' FROM dual
UNION ALL SELECT '11212508', '120TON9호기' FROM dual
)
, sycmms01 AS
(
SELECT 'RT' major, 'N' minor, '신재' name            FROM dual
UNION ALL SELECT 'RT', 'NM' , '신재->배합창고'       FROM dual
UNION ALL SELECT 'RT', 'MP' , '사용대기(배합)->생산' FROM dual
UNION ALL SELECT 'RT', 'M'   , '배합'                 FROM dual
UNION ALL SELECT 'RT', 'NR' , '신재->대기'           FROM dual
UNION ALL SELECT 'RT', 'NP' , '대기->생산'           FROM dual
UNION ALL SELECT 'GR', 'AB23', '1021ST'               FROM dual
UNION ALL SELECT 'GR', 'AC06', 'F20-03 LOF'           FROM dual
UNION ALL SELECT 'GR', 'AM02', 'F20-03'               FROM dual
UNION ALL SELECT 'GR', 'AM03', 'FU2020'               FROM dual
UNION ALL SELECT 'GR', 'AM04', 'F20-52'               FROM dual
UNION ALL SELECT 'GR', 'AM05', 'F20-52 LOF'           FROM dual
UNION ALL SELECT 'GR', 'AM07', 'F2U2020 LOF'          FROM dual
UNION ALL SELECT 'GR', 'AM12', '1015CR'               FROM dual
UNION ALL SELECT 'GR', 'AM19', '2110R'                FROM dual
UNION ALL SELECT 'GR', 'AM20', '2710HI'               FROM dual
UNION ALL SELECT 'CL', '01' , 'NAT'                  FROM dual
UNION ALL SELECT 'QT', 'QT01', 'QT01'                 FROM dual
UNION ALL SELECT 'QT', 'QT02', 'QT02'                 FROM dual
UNION ALL SELECT 'QT', 'QT05', 'QT03'                 FROM dual
UNION ALL SELECT 'QT', 'QT06', 'QT04'                 FROM dual
)
SELECT rawcod
     , iodate
     , iotype
     , CASE WHEN GROUPING(1) = 0
            THEN NVL(company, '** 월 계 **')
            ELSE '** 누 계 **'
             END company
     , gradenm
     , colornm
     , lotno
     , CASE WHEN GROUPING(1) = 0
            THEN SUM(pur_qty)
            ELSE SUM(SUM(pur_qty)) OVER(
                 PARTITION BY rawcod, GROUPING(1) ORDER BY ioym)
             END pur_qty
     , CASE WHEN GROUPING(1) = 0
            THEN SUM(out_qty)
            ELSE SUM(SUM(out_qty)) OVER(
                 PARTITION BY rawcod, GROUPING(1) ORDER BY ioym)
             END out_qty
     , SUM(SUM(rem_qty)) OVER(
       PARTITION BY rawcod, GROUPING_ID(1, iodate)
           ORDER BY ioym, iodate, MAX(cre_date)) rem_qty
     , MAX(cre_date) cre_date
FROM (SELECT s.rawcod
             , CASE WHEN s.iodate >= '20141115' THEN SUBSTR(s.iodate, 1, 6)
                                                ELSE '201411'   END ioym
             , CASE WHEN s.iodate >= '20141115' THEN s.iodate
                                                ELSE '20141115' END iodate
             , CASE WHEN s.iodate >= '20141115' THEN t.name     END iotype
             , CASE WHEN s.iodate >= '20141115' THEN v.company
                                             ELSE '** 이 월 **' END company
             , CASE WHEN s.iodate >= '20141115' THEN g.name     END gradenm
             , CASE WHEN s.iodate >= '20141115' THEN c.name     END colornm
             , CASE WHEN s.iodate >= '20141115' THEN s.lotno    END lotno
             , s.pur_qty
             , s.out_qty
             , s.pur_qty - s.out_qty AS rem_qty
             , s.cre_date
          FROM slips         s
          LEFT JOIN sycmms01 t ON t.major = 'RT' AND t.minor = s.iotype
          LEFT JOIN sycmms01 r ON r.major = 'QT' AND r.minor = s.rawcod
          LEFT JOIN sycmms01 g ON g.major = 'GR' AND g.minor = s.grade
          LEFT JOIN sycmms01 c ON c.major = 'CL' AND c.minor = s.colorcd
          LEFT JOIN sybsms00 v ON v.sitecd = s.vendcd
       --WHERE s.iodate BETWEEN '20141115' AND '20151231'
         WHERE s.iodate <= '20151231'
        ) a
GROUP BY rawcod, ioym
        , ROLLUP(1, (iodate, iotype, company, gradenm, colornm, lotno))
ORDER BY rawcod, ioym, GROUPING(1), iodate, cre_date
;

마농(manon94)님이 2018-09-10 13:30에 작성한 댓글입니다.
이 댓글은 2018-09-10 13:56에 마지막으로 수정되었습니다.

-- MSSQL --
WITH slips AS
(
SELECT 'QT06' rawcod, '20141111' iodate, 'I' io, 05 seq, 'N' iotype, '003' vendcd, 'AM19' grade
, '01' colorcd, '1429207' lotno, 475 pur_qty, 0 out_qty, '2014-11-20 12:28:50' cre_date
UNION ALL SELECT 'QT01', '20141114', 'I', 01, 'N', '002',      'AM03', '01', '4H72U',            1000, 0, '2014-11-20 12:04:33'
UNION ALL SELECT 'QT01', '20141119', 'I', 07, 'N', '002',      'AM05', '01', '4J9030U',           200, 0, '2014-11-20 12:30:36'
UNION ALL SELECT 'QT01', '20141119', 'I', 04, 'N', '002',      'AM04', '01', '4F3022U',           500, 0, '2014-11-20 12:28:02'
UNION ALL SELECT 'QT01', '20141121', 'I', 09, 'N', '002',      'AM02', '01', '4K6020U',           825, 0, '2014-11-26 14:21:20'
UNION ALL SELECT 'QT06', '20141125', 'I', 10, 'N', '003',      'AM20', '01', '1428707',           850, 0, '2014-11-26 14:22:21'
UNION ALL SELECT 'QT01', '20141126', 'I', 13, 'N', '002',      'AM02', '01', '4K6020U',          1000, 0, '2014-11-26 14:42:41'
UNION ALL SELECT 'QT01', '20141126', 'I', 12, 'N', '002',      'AM03', '01', '4111P',            1000, 0, '2014-11-26 14:23:15'
UNION ALL SELECT 'QT01', '20141126', 'I', 11, 'N', '002',      'AM03', '01', '4G30U',            1000, 0, '2014-11-26 14:22:51'
UNION ALL SELECT 'QT01', '20141128', 'I', 16, 'N', '002',      'AM03', '01', '4H27U',            1000, 0, '2014-11-28 09:17:06'
UNION ALL SELECT 'QT01', '20141128', 'I', 15, 'N', '002',      'AM02', '01', '4J6021U',          1000, 0, '2014-11-28 09:16:36'
UNION ALL SELECT 'QT06', '20141128', 'I', 14, 'N', '003',      'AM19', '01', '1431907',          1000, 0, '2014-11-28 09:15:56'
UNION ALL SELECT 'QT01', '20150102', 'I', 17, 'N', '002',      'AM03', '01', '4L28U',              75, 0, '2015-01-08 14:30:02'
UNION ALL SELECT 'QT02', '20150106', 'I', 24, 'N', '002',      'AM05', '01', '4K9022U',            75, 0, '2015-01-08 14:36:02'
UNION ALL SELECT 'QT02', '20150106', 'I', 23, 'N', '002',      'AC06', '01', '4I9011U',           200, 0, '2015-01-08 14:34:49'
UNION ALL SELECT 'QT06', '20150106', 'I', 22, 'N', '003',      'AM20', '01', '1428707',           200, 0, '2015-01-08 14:33:55'
UNION ALL SELECT 'QT05', '20150106', 'I', 21, 'N', '003',      'AM12', '01', '14340B1',           175, 0, '2015-01-08 14:33:18'
UNION ALL SELECT 'QT06', '20150106', 'I', 20, 'N', '003',      'AM19', '01', '1435607',           500, 0, '2015-01-08 14:32:35'
UNION ALL SELECT 'QT01', '20150106', 'I', 19, 'N', '002',      'AM02', '01', '4L4122U',           750, 0, '2015-01-08 14:31:43'
UNION ALL SELECT 'QT01', '20150106', 'I', 18, 'N', '002',      'AM03', '01', '4L02P',            1200, 0, '2015-01-08 14:31:05'
UNION ALL SELECT 'QT02', '20150210', 'I', 30, 'N', '002',      'AM05', '01', '987654',             75, 0, '2015-02-10 18:13:19'
UNION ALL SELECT 'QT01', '20150210', 'I', 29, 'N', '002',      'AM03', '01', '123456',             75, 0, '2015-02-10 17:58:46'
UNION ALL SELECT 'QT01', '20150210', 'I', 28, 'N', '002',      'AM03', '01', '567890-',           125, 0, '2015-02-10 17:35:36'
UNION ALL SELECT 'QT01', '20150504', 'I', 37, 'N', '001',      'AM07', '01', '4F26U',              50, 0, '2015-06-08 08:21:33'
UNION ALL SELECT 'QT06', '20150506', 'I', 36, 'N', '001',      'AB23', '01', '15010003',          250, 0, '2015-06-08 08:20:42'
UNION ALL SELECT 'QT01', '20150522', 'I', 31, 'N', '001',      'AM02', '01', '5E6021U',           575, 0, '2015-06-08 08:12:26'
UNION ALL SELECT 'QT06', '20150604', 'I', 32, 'N', '001',      'AM19', '01', '1514507',           250, 0, '2015-06-08 08:16:56'
UNION ALL SELECT 'QT06', '20150606', 'I', 34, 'N', '001',      'AM20', '01', '150907',            250, 0, '2015-06-08 08:18:57'
UNION ALL SELECT 'QT01', '20150608', 'I', 38, 'N', '002',      'AM02', '01', '5E6021U',           575, 0, '2015-06-08 08:23:26'
UNION ALL SELECT 'QT01', '20150707', 'I', 39, 'N', '002',      'AM02', '01', '1111',              250, 0, '2015-07-07 09:53:34'
UNION ALL SELECT 'QT01', '20160125', 'I', 44, 'N', '002',      'AM02', '01', '5L4027U',           350, 0, '2016-01-26 15:35:06'
UNION ALL SELECT 'QT01', '20160126', 'I', 43, 'N', '002',      'AM03', '01', '5L09P',            1000, 0, '2016-01-26 15:31:27'
UNION ALL SELECT 'QT01', '20160126', 'I', 45, 'N', '002',      'AM02', '01', '6A2005U',          1000, 0, '2016-01-26 15:36:57'
UNION ALL SELECT 'QT01', '20160716', 'I', 42, 'M', '002',      'AM02', '01', 'F4020',              36, 0, '2015-07-16 15:36:26'
UNION ALL SELECT 'QT01', '20150210', 'O', 03, 'MP', '11212305', 'AM03', '01', 'NQT0120150210001',    0, 5, '2015-02-10 17:39:22'
UNION ALL SELECT 'QT01', '20150210', 'O', 02, 'NM', '11212305', 'AM03', '01', 'NQT0120150210001',    0, 25, '2015-02-10 17:36:52'
UNION ALL SELECT 'QT02', '20150210', 'O', 07, 'MP', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 15, '2015-02-10 18:15:50'
UNION ALL SELECT 'QT02', '20150210', 'O', 06, 'NP', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 25, '2015-02-10 18:15:17'
UNION ALL SELECT 'QT02', '20150210', 'O', 05, 'NR', '11212505', 'AM05', '01', 'NQT0220150210001',    0, 25, '2015-02-10 18:14:00'
UNION ALL SELECT 'QT01', '20150210', 'O', 04, 'NM', '11212508', 'AM03', '01', 'NQT0120150210007',    0, 25, '2015-02-10 18:06:00'
)
, sybsms00 AS
(
SELECT '001' sitecd, '신일상사' company
UNION ALL SELECT '002'     , 'KEP'
UNION ALL SELECT '003'     , 'SOLVAY'
UNION ALL SELECT '11212305', '150TON10호기'
UNION ALL SELECT '11212505', '150TON10호기'
UNION ALL SELECT '11212508', '120TON9호기'
)
, sycmms01 AS
(
SELECT 'RT' major, 'N' minor, '신재' name
UNION ALL SELECT 'RT', 'NM' , '신재->배합창고'
UNION ALL SELECT 'RT', 'MP' , '사용대기(배합)->생산'
UNION ALL SELECT 'RT', 'M'   , '배합'
UNION ALL SELECT 'RT', 'NR' , '신재->대기'
UNION ALL SELECT 'RT', 'NP' , '대기->생산'
UNION ALL SELECT 'GR', 'AB23', '1021ST'
UNION ALL SELECT 'GR', 'AC06', 'F20-03 LOF'
UNION ALL SELECT 'GR', 'AM02', 'F20-03'
UNION ALL SELECT 'GR', 'AM03', 'FU2020'
UNION ALL SELECT 'GR', 'AM04', 'F20-52'
UNION ALL SELECT 'GR', 'AM05', 'F20-52 LOF'
UNION ALL SELECT 'GR', 'AM07', 'F2U2020 LOF'
UNION ALL SELECT 'GR', 'AM12', '1015CR'
UNION ALL SELECT 'GR', 'AM19', '2110R'
UNION ALL SELECT 'GR', 'AM20', '2710HI'
UNION ALL SELECT 'CL', '01' , 'NAT'
UNION ALL SELECT 'QT', 'QT01', 'QT01'
UNION ALL SELECT 'QT', 'QT02', 'QT02'
UNION ALL SELECT 'QT', 'QT05', 'QT03'
UNION ALL SELECT 'QT', 'QT06', 'QT04'
)
SELECT rawcod
     , iodate
     , iotype
     , CASE WHEN x = 1
            THEN '** 누 계 **'
            ELSE ISNULL(company, '** 월 계 **')
             END company
     , gradenm
     , colornm
     , lotno
     , CASE WHEN x = 1
            THEN SUM(SUM(pur_qty)) OVER(
                 PARTITION BY rawcod, x ORDER BY ioym)
            ELSE SUM(pur_qty)
             END pur_qty
     , CASE WHEN x = 1
            THEN SUM(SUM(out_qty)) OVER(
                 PARTITION BY rawcod, x ORDER BY ioym)
            ELSE SUM(out_qty)
             END out_qty
     , SUM(SUM(rem_qty)) OVER(
       PARTITION BY rawcod, x, GROUPING(iodate)
           ORDER BY ioym, iodate, MAX(cre_date)) rem_qty
     , MAX(cre_date) cre_date
FROM (SELECT s.rawcod
             , CASE WHEN s.iodate >= '20141115' THEN SUBSTRING(s.iodate,1,6)
                                                ELSE '201411'   END ioym
             , CASE WHEN s.iodate >= '20141115' THEN s.iodate
                                                ELSE '20141115' END iodate
             , CASE WHEN s.iodate >= '20141115' THEN t.name     END iotype
             , CASE WHEN s.iodate >= '20141115' THEN v.company
                                             ELSE '** 이 월 **' END company
             , CASE WHEN s.iodate >= '20141115' THEN g.name     END gradenm
             , CASE WHEN s.iodate >= '20141115' THEN c.name     END colornm
             , CASE WHEN s.iodate >= '20141115' THEN s.lotno    END lotno
             , s.pur_qty
             , s.out_qty
             , s.pur_qty - s.out_qty AS rem_qty
             , s.cre_date
             , 1 x
          FROM slips         s
          LEFT JOIN sycmms01 t ON t.major = 'RT' AND t.minor = s.iotype
          LEFT JOIN sycmms01 r ON r.major = 'QT' AND r.minor = s.rawcod
          LEFT JOIN sycmms01 g ON g.major = 'GR' AND g.minor = s.grade
          LEFT JOIN sycmms01 c ON c.major = 'CL' AND c.minor = s.colorcd
          LEFT JOIN sybsms00 v ON v.sitecd = s.vendcd
       --WHERE s.iodate BETWEEN '20141115' AND '20151231'
         WHERE s.iodate <= '20151231'
        ) a
GROUP BY GROUPING SETS
         ( (rawcod, ioym, iodate, iotype, company, gradenm, colornm, lotno)
          , (rawcod, ioym)     -- 월계
          , (rawcod, ioym, x) -- 누계
          )
ORDER BY rawcod, ioym, ISNULL(x, 0), ISNULL(iodate, '99991231'), cre_date
;

마농(manon94)님이 2018-09-10 13:35에 작성한 댓글입니다.
이 댓글은 2018-09-17 14:11에 마지막으로 수정되었습니다.

마농님 진심 감사드립니다.

갓마농님 입니다....^_^

쿼리의 신이라 부르고 싶습니다.

어쩌면 쿼리를 이렇게 이쁘게 깔끔하게 교과서처럼 잘 구현 하십니까??

존경스럽습니다.

Oracle 10g 조금 접했는데, 많이 변화했네요..

감사히 잘 사용하겠습니다.

건강하시고 다가오는 명정 잘 지내세요..

거듭 감사합니다.

초보님이 2018-09-11 16:35에 작성한 댓글입니다. Edit Ｘ

[Top]

No.	제목	작성자	작성일	조회
41633	첫쨰주 토요일과 셋째주 토요일을 구하는 문제인데요 .. [2]	이병훈	2018-09-12	4110
41632	세로데이터를 가로로 변환 부탁드립니다 [1]	블루이글	2018-09-11	4652
41631	패키지 내에서 패키지 호출방법 [1]	도와주세요	2018-09-10	3832
41630	쿼리문을 바꾸려 합니다. [3]	초보	2018-09-08	4373
41629	dbf 파일 oracle import 방법문의드립니다.	빅초이	2018-09-07	3937
41628	12C 설치가 하루 걸리나요 [2]	신승익	2018-09-05	3996
41627	PK확인은 어디서 하나요? [1]	하늘	2018-09-02	4033