by asdf [SQL Query] mysql 날짜 날짜 사이 두 컬럼 [2018.06.18 18:32:53]
기본 테이블
idx start_date end_date
1 2018-06-26 09:00:00 2018-06-28 18:00:00
2 2018-05-26 09:00:00 2018-05-28 18:00:00
이와 같이 데이터가 있을경우
출력 select 결과
idx start_date end_date
1 2018-06-26 09:00:00 2018-06-26 18:00:00
1 2018-06-27 09:00:00 2018-06-27 18:00:00
1 2018-06-28 09:00:00 2018-06-28 18:00:00
2 2018-06-25 09:00:00 2018-06-25 18:00:00
으로 출력하는 방법을 알고 싶습니다..
WITH TEMP AS ( SELECT @S := 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL UNION ALL SELECT @S := @S + 1 AS v1 FROM DUAL ), OT AS ( SELECT 1 AS idx, '2018-06-26 09:00:00' AS start_date, '2018-06-28 18:00:00' AS end_date UNION ALL SELECT 2 AS idx, '2018-05-26 09:00:00' AS start_date, '2018-05-28 18:00:00' AS end_date ) SELECT idx, @T := DATE_ADD( DATE_FORMAT(start_date, '%Y-%m-%d %H:%i:%s'), INTERVAL LEVEL - 1 DAY ) AS start_date, CONCAT( DATE_FORMAT(@T, '%Y-%m-%d'), ' ', DATE_FORMAT(end_date, '%H:%i:%s') ) AS end_date FROM ( SELECT T1.v1 + (T2.v1 - 1) * 10 AS LEVEL FROM TEMP T1, TEMP T2 ) TEMP, OT WHERE DATE_ADD( DATE_FORMAT(start_date, '%Y-%m-%d %H:%i:%s'), INTERVAL LEVEL - 1 DAY ) <= end_date ORDER BY idx, start_date
최신 버전이라면? 재귀쿼리도 가능하겠네요.
WITH RECURSIVE t AS ( SELECT idx, start_date, end_date , start_date + INTERVAL 0 HOUR sdt , start_date + INTERVAL 9 HOUR edt FROM test4 UNION ALL SELECT idx, start_date, end_date , sdt + INTERVAL 1 DAY sdt , edt + INTERVAL 1 DAY edt FROM t WHERE edt < end_date ) SELECT * FROM t ORDER BY idx, sdt ;