데이터베이스 질문과 답변

SQL 문의 드립니다. 0 1 751

by zzhozang [SQL Query] [2018.07.04 16:55:26]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH TEMP AS (
SELECT 'CUSTOMER1' AS CUSTOMER, 'PART1' AS PART, 'A' AS FLAG, 1000 AS QTY1, 2000 AS QTY2, 3000 AS QTY3 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART1', 'B',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART1', 'C',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART1', 'D',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART1', 'E',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART2', 'A',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART2', 'B',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART2', 'C',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART2', 'D',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER1', 'PART2', 'E',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER2', 'PART3', 'A',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER2', 'PART3', 'B',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER2', 'PART3', 'C',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER2', 'PART3', 'D',1000,2000,3000 FROM DUAL UNION ALL
SELECT 'CUSTOMER2', 'PART3', 'E',1000,2000,3000 FROM DUAL
)
SELECT *
FROM TEMP

 

안녕하세요.

CUSTOMER PART FLAG QTY1 QTY2 QTY3
CUSTOMER1 PART1 A 1000 2000 3000
CUSTOMER1 PART1 B 1000 2000 3000
CUSTOMER1 PART1 C 1000 2000 3000
CUSTOMER1 PART1 D 1000 2000 3000
CUSTOMER1 PART1 E 1000 2000 3000
CUSTOMER1 PART2 A 1000 2000 3000
CUSTOMER1 PART2 B 1000 2000 3000
CUSTOMER1 PART2 C 1000 2000 3000
CUSTOMER1 PART2 D 1000 2000 3000
CUSTOMER1 PART2 E 1000 2000 3000
CUSTOMER2 PART3 A 1000 2000 3000
CUSTOMER2 PART3 B 1000 2000 3000
CUSTOMER2 PART3 C 1000 2000 3000
CUSTOMER2 PART3 D 1000 2000 3000
CUSTOMER2 PART3 E 1000 2000 3000

 

위와같은 TABLE 이 있을때 아래와 같이 조회 가능한지 문의 드립니다.

 

CUSTOMER PART FLAG QTY1 QTY2 QTY3
CUSTOMER1 PART1 A 1000 2000 3000
CUSTOMER1 PART1 B 1000 2000 3000
CUSTOMER1 PART1 C 1000 2000 3000
CUSTOMER1 PART1 D 1000 2000 3000
CUSTOMER1 PART1 E 1000 2000 3000
CUSTOMER1 PART2 A 1000 2000 3000
CUSTOMER1 PART2 B 1000 2000 3000
CUSTOMER1 PART2 C 1000 2000 3000
CUSTOMER1 PART2 D 1000 2000 3000
CUSTOMER1 PART2 E 1000 2000 3000
CUSTOMER1 CUSTOMER1 TOTAL A 2000 4000 6000
CUSTOMER1 CUSTOMER1 TOTAL B 2000 4000 6000
CUSTOMER1 CUSTOMER1 TOTAL C 2000 4000 6000
CUSTOMER1 CUSTOMER1 TOTAL D 2000 4000 6000
CUSTOMER1 CUSTOMER1 TOTAL E 2000 4000 6000
CUSTOMER2 PART3 A 1000 2000 3000
CUSTOMER2 PART3 B 1000 2000 3000
CUSTOMER2 PART3 C 1000 2000 3000
CUSTOMER2 PART3 D 1000 2000 3000
CUSTOMER2 PART3 E 1000 2000 3000
CUSTOMER2 CUSTOMER2 TOTAL A 1000 2000 3000
CUSTOMER2 CUSTOMER2 TOTAL B 1000 2000 3000
CUSTOMER2 CUSTOMER2 TOTAL C 1000 2000 3000
CUSTOMER2 CUSTOMER2 TOTAL D 1000 2000 3000
CUSTOMER2 CUSTOMER2 TOTAL E 1000 2000 3000
TOTAL TOTAL A 3000 6000 9000
TOTAL TOTAL B 3000 6000 9000
TOTAL TOTAL C 3000 6000 9000
TOTAL TOTAL D 3000 6000 9000
TOTAL TOTAL E 3000 6000 9000

 

CUSTOMER 의 FLAG 별로 총합을 끼워 넣어야 하는데...

방법이 있을지 문의드립니다.ㅠ

 

by 마농 [2018.07.04 17:47:53]
?
1
2
3
4
5
6
7
8
9
10
SELECT NVL(customer, 'TOTAL') customer
     , TRIM(NVL(part, customer || ' TOTAL')) part
     , flag
     , SUM(qty1) qty1
     , SUM(qty2) qty2
     , SUM(qty3) qty3
  FROM temp a
 GROUP BY ROLLUP(customer, part), flag
 ORDER BY a.customer, a.part, flag
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입