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.....
이런식으로 데이터를 만들고 싶은데 그룹핑이랑 롤업.. 그룹바이 썼는데도... 안되서요... 어떤 함수를 써야하나요........
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 ;
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 ;
마농님이 작성하신 내용 참고해서 만들어 보았습니다.