데이터베이스 질문과 답변
뭔가 좋은 쿼리가 있을거 같은데..
제가 너무 돌아 가는거 같아서..
질문 올려봅니다.
아래와 같은 데이터를 기준으로
with 점수테이블(name, lecture, month, count) as(
select 'a', '국어', '4월', 90 from dual union all
select 'a', '수학', '4월', 100 from dual union all
select 'a', '영어', '4월', 80 from dual union all
select 'a', '국어', '5월', 100 from dual union all
select 'a', '수학', '5월', 100 from dual union all
select 'a', '영어', '5월', 75 from dual union all
select 'a', '국어', '6월', 50 from dual union all
select 'a', '수학', '6월', 100 from dual union all
select 'a', '영어', '6월', 90 from dual union all
select 'b', '국어', '4월', 90 from dual union all
select 'b', '수학', '4월', 100 from dual union all
select 'b', '영어', '4월', 80 from dual union all
select 'b', '국어', '5월', 100 from dual union all
select 'b', '수학', '5월', 100 from dual union all
select 'b', '영어', '5월', 75 from dual union all
select 'b', '국어', '6월', 50 from dual union all
select 'b', '수학', '6월', 100 from dual union all
select 'b', '영어', '6월', 90 from dual union all
select 'c', '국어', '4월', 90 from dual union all
select 'c', '수학', '4월', 100 from dual union all
select 'c', '영어', '4월', 80 from dual union all
select 'c', '국어', '5월', 100 from dual union all
select 'c', '수학', '5월', 100 from dual union all
select 'c', '영어', '5월', 75 from dual union all
select 'c', '국어', '6월', 50 from dual union all
select 'c', '수학', '6월', 100 from dual union all
select 'c', '영어', '6월', 90 from dual
)
, 월(month) as(
select '1월' from dual union all
select '2월' from dual union all
select '3월' from dual union all
select '4월' from dual union all
select '5월' from dual union all
select '6월' from dual union all
select '7월' from dual union all
select '8월' from dual union all
select '9월' from dual union all
select '10월' from dual union all
select '11월' from dual union all
select '12월' from dual
)
다음과 같은 데이터를 만들고 싶습니다.
| NAME | MONTH | 국어 | 수학 | 영어 | 계 |
| a | 1월 | ||||
| a | 2월 | ||||
| a | 3월 | ||||
| a | 4월 | 90 | 100 | 80 | 270 |
| a | 5월 | 100 | 100 | 75 | 275 |
| a | 6월 | 50 | 100 | 90 | 240 |
| a | 7월 | ||||
| a | 8월 | ||||
| a | 9월 | ||||
| a | 10월 | ||||
| a | 11월 | ||||
| a | 12월 | ||||
| a | 합계 | 240 | 300 | 245 | 785 |
| b | 1월 | ||||
| b | 2월 | ||||
| b | 3월 | ||||
| b | 4월 | 90 | 100 | 80 | 270 |
| b | 5월 | 100 | 100 | 75 | 275 |
| b | 6월 | 50 | 100 | 90 | 240 |
| b | 7월 | ||||
| b | 8월 | ||||
| b | 9월 | ||||
| b | 10월 | ||||
| b | 11월 | ||||
| b | 12월 | ||||
| b | 합계 | 240 | 300 | 245 | 785 |
| c | 1월 | ||||
| c | 2월 | ||||
| c | 3월 | ||||
| c | 4월 | 90 | 100 | 80 | 270 |
| c | 5월 | 100 | 100 | 75 | 275 |
| c | 6월 | 50 | 100 | 90 | 240 |
| c | 7월 | ||||
| c | 8월 | ||||
| c | 9월 | ||||
| c | 10월 | ||||
| c | 11월 | ||||
| c | 12월 | ||||
| c | 합계 | 240 | 300 | 245 | 785 |
| 계 | 1월 | ||||
| 계 | 2월 | ||||
| 계 | 3월 | ||||
| 계 | 4월 | 270 | 300 | 240 | 810 |
| 계 | 5월 | 300 | 300 | 225 | 825 |
| 계 | 6월 | 150 | 300 | 270 | 720 |
| 계 | 7월 | ||||
| 계 | 8월 | ||||
| 계 | 9월 | ||||
| 계 | 10월 | ||||
| 계 | 11월 | ||||
| 계 | 12월 | ||||
| 계 | 합계 | 720 | 900 | 735 | 2355 |
감사합니다.
하기쿼리 참고하세요~
MSSQL 기준으로 작성되어서
타 DB 사용중이시면 구문을 조금 수정하셔야 될겁니다.
---쿼리시작---
;with 점수(name,lecture,month,count) As
(
Select 'a','국어','4월',90 Union All
Select 'a','수학','4월',100 Union All
Select 'a','영어','4월',80 Union All
Select 'a','국어','5월',100 Union All
Select 'a','수학','5월',100 Union All
Select 'a','영어','5월',75 Union All
Select 'a','국어','6월',50 Union All
Select 'a','수학','6월',100 Union All
Select 'a','영어','6월',90 Union All
Select 'b','국어','4월',90 Union All
Select 'b','수학','4월',100 Union All
Select 'b','영어','4월',80 Union All
Select 'b','국어','5월',100 Union All
Select 'b','수학','5월',100 Union All
Select 'b','영어','5월',75 Union All
Select 'b','국어','6월',50 Union All
Select 'b','수학','6월',100 Union All
Select 'b','영어','6월',90 Union All
Select 'c','국어','4월',90 Union All
Select 'c','수학','4월',100 Union All
Select 'c','영어','4월',80 Union All
Select 'c','국어','5월',100 Union All
Select 'c','수학','5월',100 Union All
Select 'c','영어','5월',75 Union All
Select 'c','국어','6월',50 Union All
Select 'c','수학','6월',100 Union All
Select 'c','영어','6월',90
)
,날짜(month) As
(
Select '1월' Union All
Select '2월' Union All
Select '3월' Union All
Select '4월' Union All
Select '5월' Union All
Select '6월' Union All
Select '7월' Union All
Select '8월' Union All
Select '9월' Union All
Select '10월' Union All
Select '11월' Union All
Select '12월'
)
,ResA(name,month,국어,영어,수학,합계) As
(
Select a.name
,a.month
,Sum(b.국어)
,Sum(b.영어)
,Sum(b.수학)
,Sum(b.국어) + Sum(b.영어) + Sum(b.수학)
From (
Select a.month
,b.name
From 날짜 a Full Outer Join 점수 b On 1 = 1
Group By a.month
,b.name
) a
Left Outer Join
(
Select a.name
,a.month
,국어 = (Case When a.lecture = '국어' Then a.count Else 0 End )
,영어 = (Case When a.lecture = '영어' Then a.count Else 0 End )
,수학 = (Case When a.lecture = '수학' Then a.count Else 0 End )
From 점수 a
) b
On a.month = b.month
And a.name = b.name
Group By a.name
,a.month
)
Select
Replace(a.name,'zz','') As name
,a.month As month
,a.국어
,a.영어
,a.수학
,a.합계
--,a.sort
--,a.sort2
From
(
Select a.name
,a.month
,a.국어
,a.영어
,a.수학
,a.합계
,CONVERT(Integer,REPLACE(a.month,'월','')) As sort
,1 As sort2
From ResA a
Union All
Select a.name
,'계' As month
,Sum(a.국어) 국어
,Sum(a.영어) 영어
,Sum(a.수학) 수학
,Sum(a.합계) 합계
,13 As sort
,2 As sort2
From ResA a
Group By a.name
Union All
Select 'zz계' As name
,a.month As month
,Sum(a.국어) 국어
,Sum(a.영어) 영어
,Sum(a.수학) 수학
,Sum(a.합계) 합계
,14 As sort
,CONVERT(Integer,Replace(a.month,'월','')) As sort2
From ResA a
Group By a.month
Union All
Select 'zz계' As name
,'합계' As month
,Sum(a.국어) 국어
,Sum(a.영어) 영어
,Sum(a.수학) 수학
,Sum(a.합계) 합계
,14 As sort
,99 As sort2
From ResA a
) a
Order By a.name asc
,a.sort Asc
,a.sort2 Asc
---쿼리끝---
월별 총계가 없네요?
이렇게 union으로 붙이지 않고 rollup을 이용하면 좋을거 같은데요..
-- MSSQL -- SELECT CASE WHEN a.name IS NULL THEN '합계' ELSE a.name END name , ISNULL(b.month, '합계') month , SUM(CASE lecture WHEN '국어' THEN count END) 국어 , SUM(CASE lecture WHEN '수학' THEN count END) 수학 , SUM(CASE lecture WHEN '영어' THEN count END) 영어 , SUM(count) 계 FROM (SELECT DISTINCT name FROM 점수테이블) a CROSS JOIN 월 b LEFT OUTER JOIN 점수테이블 c ON a.name = c.name AND b.month = c.month GROUP BY a.name, b.month WITH CUBE ORDER BY ISNULL(a.name, 'z'), ISNULL(REPLACE(b.month, '월', '')*1, 13) ;
-- Oracle -- SELECT NVL(b.name, '계') name , NVL(a.month, '합계') month , SUM(DECODE(lecture, '국어', count)) 국어 , SUM(DECODE(lecture, '수학', count)) 수학 , SUM(DECODE(lecture, '영어', count)) 영어 , SUM(count) 계 FROM 월 a LEFT OUTER JOIN 점수테이블 b PARTITION BY (b.name) ON a.month = b.month GROUP BY CUBE(b.name, a.month) ORDER BY b.name, TO_NUMBER(REPLACE(a.month, '월')) ;
아.. PARTITION BY가 생각 안났네요..
역시 뭔가가 있었어요..
) 버튼을 클릭하여 작성 하시면 됩니다.