sql로 달력만들기

여러달 짜리

from ~ to

with reCurSive wra AS (
    SELECT 1 AS no
         , DATE('2021-07-01') as frdt
         , DATE('2021-12-31') as todt
    UNION ALL
    SELECT 1 + A.no
         , DATE_ADD(A.frdt, INTERVAL 1 DAY ) as frdt
         , DATE('2021-12-31') as todt
    FROM wra A
    /*WHERE DATE_FORMAT(A.frdt,'%Y-%m-%d') <> DATE_FORMAT(A.todt,'%Y-%m-%d')*/
    where A.frdt <> A.todt
), dt as (
    select a.no no
         , CURDATE() as today
         , a.frdt as frdt
         , a.todt as todt
         , DATE_FORMAT(a.frdt, '%d') as d_normal
         , case when CURDATE() = a.frdt then concat('>>', Lpad(DATE_FORMAT(a.frdt, '%d'),2,'0') ,'<<') else Lpad(DATE_FORMAT(a.frdt, '%d'),2,'0') end as d
         , WEEK(a.frdt) as week
         , DAYOFWEEK(a.frdt) as yoil
         , DATE_FORMAT(a.frdt, '%Y년 %m월') as ym
    from wra a
)
/*select * from dt ;*/
select a.ym as ym,
    concat(max(a.week), '주') as week,
    max(case when a.yoil = 1 then a.d else null end) as SUN,
    max(case when a.yoil = 2 then a.d else null end) as MON,
    max(case when a.yoil = 3 then a.d else null end) as TUE,
    max(case when a.yoil = 4 then a.d else null end) as WED,
    max(case when a.yoil = 5 then a.d else null end) as THR,
    max(case when a.yoil = 6 then a.d else null end) as FRI,
    max(case when a.yoil = 7 then a.d else null end) as SAT
from dt a
group by a.ym, a.week
;

한달짜리

한달짜루;

WITH reCurSive wra AS (
    SELECT 1 AS no
    UNION ALL
    SELECT 1 + A.no
    FROM wra A
    WHERE A.no < 31
), tDay as (
    select CURDATE() as today
), dt as (
    SELECT NO as no
    , CONCAT(DATE_FORMAT(t.today, '%Y-%m-'), Lpad(NO,2,'0')) as dt
    , DAYOFWEEK(date(CONCAT(DATE_FORMAT(t.today, '%Y-%m-'), Lpad(NO,2,'0')))) as yoil
    , WEEK(date(CONCAT(DATE_FORMAT(t.today, '%Y-%m-'), Lpad(NO,2,'0')))) as week
    , t.today as today
    , DATE_FORMAT(t.today, '%Y-%m') as ym
    , case when NO = DATE_FORMAT(t.today, '%d') then concat('[', Lpad(NO,2,'0') ,']') else Lpad(NO,2,'0') end as d
    FROM wrA
    left outer join tDay t
    on ( 1 = 1 )
)
/*select * from dt*/
select a.ym as ym,
    max(case when a.yoil = 1 then a.d else null end) as SUN,
    max(case when a.yoil = 2 then a.d else null end) as MON,
    max(case when a.yoil = 3 then a.d else null end) as TUE,
    max(case when a.yoil = 4 then a.d else null end) as WED,
    max(case when a.yoil = 5 then a.d else null end) as THR,
    max(case when a.yoil = 6 then a.d else null end) as FRI,
    max(case when a.yoil = 7 then a.d else null end) as SAT,
    max(a.week) as week
from dt a
group by a.ym, a.week
;

위에서 핵심이 되는 펑션들은 아래와 같다.

select WEEK(CURDATE())
select DAYOFWEEK(CURDATE())
select last_day(CURDATE())
select CURDATE()
select Date(DATE_FORMAT(CURDATE(),'%Y-%m-01')) as first_day
select WEEK(DATE('2021-01-03'))

일자를 표시하기위한 재귀호출의 구조는 요렇게 하면된다.

WITH RECURSIVE wra AS (
    SELECT 1 AS no
    UNION ALL
    SELECT 1+a.no
    FROM wra a
    WHERE a.no < 31
)
SELECT no
FROM wra
;

위 두개로 달력을 만들어 보면된다.

심화로, 특정일을 등록하여 공휴일, 일정을 관리하는 것을 짜보도록~!!! 한다.

완성된 화면은 아래와 같다.

데이터 베이스에 등록한 값은 아래와 같고, 그럼 잘 버무려 보기 바란다. ㅋ