날짜 그룹핑은 어떻게 해야하나요 롤업 그룹써봐도 답이 안나와서요 0 3 368

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 절은 필요 없습니다.

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