mysql 달력 쿼리 > 질문답변

• 목록

-- 달력 테이블 생성
CREATE TABLE TB_CALENDAR(
 CAL_YEAR SMALLINT NOT NULL,
 CAL_MONTH SMALLINT NOT NULL,
 CAL_DAY SMALLINT NOT NULL,
 CAL_DATE VARCHAR(10) NOT NULL,
 PRIMARY KEY (CAL_YEAR, CAL_MONTH, CAL_DAY)
);


-- 달력 테이블에 날짜 입력하는 PROCEDURE
CREATE
PROCEDURE SP_INSERT_DATE(IN sDate VARCHAR(10), IN eDate VARCHAR(10))
  BEGIN
    WHILE (sDate <= eDate) DO
    INSERT INTO TB_CALENDAR VALUES (YEAR(sDate), MONTH(sDate), DAY(sDate), sDate);
    SET sDate = DATE_ADD(sDate, INTERVAL 1 DAY);
  END WHILE;
END;


-- 달력 테이블에 날짜 입력
CALL SP_INSERT_DATE('2010-12-01', '2011-12-31');


-- 달력 가져오기
SELECT SUM(SUN) SUN,
      SUM(MON) MON,
      SUM(TUE) TUE,
      SUM(WED) WED,
      SUM(THU) THU,
      SUM(FRI) FRI,
      SUM(SAT) SAT
  FROM
      (SELECT
              CASE WHEN DAYOFWEEK(CAL_DATE) = 1 THEN CAL_DAY END 'SUN',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 2 THEN CAL_DAY END 'MON',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 3 THEN CAL_DAY END 'TUE',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 4 THEN CAL_DAY END 'WED',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 5 THEN CAL_DAY END 'THU',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 6 THEN CAL_DAY END 'FRI',
              CASE WHEN DAYOFWEEK(CAL_DATE) = 7 THEN CAL_DAY END 'SAT',
              WEEK(CAL_DATE, 0) WEEKS
        FROM TB_CALENDAR
        WHERE CAL_YEAR = 2010
              AND CAL_MONTH = 12
      ) A
  GROUP BY WEEKS;