데이터베이스 질문과 답변
날짜 그룹핑은 어떻게 해야하나요 롤업 그룹써봐도 답이 안나와서요
0
3
769
by cast [2019.03.15 15:29:13]
mysql 입니다 col1 col2 2019-03-04 aa 2019-03-05 dd 2019-03-06 qq 2019-03-07 11 2019-03-08 12 2019-03-09 cc 2019-03-09 gg 2019-03-10 ww 2019-03-11 aa 2019-03-12 ee 2019-03-13 dd 2019-03-14 bb 2019-03-14 ww 2019-03-15 ee 2019-03-15 rr 2019-03-15 tt
예를들어 테이블에 이런 데이터가 있다고 가정하에요
2019-03-15
2019-03-15 ee
2019-03-15 rr
2019-03-15 tt
2019-03-14
2019-03-14 ww
2019-03-14 bb
2019-03-13.....
이런식으로 데이터를 만들고 싶은데 그룹핑이랑 롤업.. 그룹바이 썼는데도... 안되서요... 어떤 함수를 써야하나요........
by 마농
[2019.03.15 15:55:15]
WITH t AS
(
SELECT '2019-03-04' col1, 'aa' col2
UNION ALL SELECT '2019-03-05', 'dd'
UNION ALL SELECT '2019-03-06', 'qq'
UNION ALL SELECT '2019-03-07', '11'
UNION ALL SELECT '2019-03-08', '12'
UNION ALL SELECT '2019-03-09', 'cc'
UNION ALL SELECT '2019-03-09', 'gg'
UNION ALL SELECT '2019-03-10', 'ww'
UNION ALL SELECT '2019-03-11', 'aa'
UNION ALL SELECT '2019-03-12', 'ee'
UNION ALL SELECT '2019-03-13', 'dd'
UNION ALL SELECT '2019-03-14', 'bb'
UNION ALL SELECT '2019-03-14', 'ww'
UNION ALL SELECT '2019-03-15', 'ee'
UNION ALL SELECT '2019-03-15', 'rr'
UNION ALL SELECT '2019-03-15', 'tt'
)
SELECT *
FROM (SELECT col1, col2
FROM t a
GROUP BY col1, col2
WITH ROLLUP
HAVING col1 IS NOT NULL
) a
ORDER BY col1 DESC, col2
;
by inbeater
[2019.03.18 10:11:19]
WITH t AS
(
SELECT '2019-03-04' col1, 'aa' col2 from dual union all
SELECT '2019-03-05' col1, 'dd' col2 from dual union all
SELECT '2019-03-06' col1, 'qq' col2 from dual union all
SELECT '2019-03-07' col1, '11' col2 from dual union all
SELECT '2019-03-08' col1, '12' col2 from dual union all
SELECT '2019-03-09' col1, 'cc' col2 from dual union all
SELECT '2019-03-09' col1, 'gg' col2 from dual union all
SELECT '2019-03-10' col1, 'ww' col2 from dual union all
SELECT '2019-03-11' col1, 'aa' col2 from dual union all
SELECT '2019-03-12' col1, 'ee' col2 from dual union all
SELECT '2019-03-13' col1, 'dd' col2 from dual union all
SELECT '2019-03-14' col1, 'bb' col2 from dual union all
SELECT '2019-03-14' col1, 'ww' col2 from dual union all
SELECT '2019-03-15' col1, 'ee' col2 from dual union all
SELECT '2019-03-15' col1, 'rr' col2 from dual union all
SELECT '2019-03-15' col1, 'tt' col2 from dual
)
select col1, col2
from t
group by grouping sets (col1, (col1, col2))
order by 1, 2 desc;
SELECT *
FROM (SELECT col1, col2
FROM t a
GROUP BY ROLLUP (col1, col2)
--WITH ROLLUP
HAVING col1 IS NOT NULL
) a
ORDER BY col1 DESC, col2
;
마농님이 작성하신 내용 참고해서 만들어 보았습니다.
by 마농
[2019.03.21 08:18:04]
오라클의 경우엔 롤업 사용시
일부 항목만 롤업 처리도 가능하고, 괄호로 묶어서 롤업도 가능합니다.
GROUP BY col1, ROLLUP(col2) 하면 Having 절은 필요 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.