MySQL 달력 만들기 1 3 23,351

by 마농 [마농] 달력 MySQL달력 [2015.07.06 09:59:41]


SELECT ym
     , MIN(CASE dw WHEN 1 THEN d END) Sun
     , MIN(CASE dw WHEN 2 THEN d END) Mon
     , MIN(CASE dw WHEN 3 THEN d END) Tue
     , MIN(CASE dw WHEN 4 THEN d END) Wed
     , MIN(CASE dw WHEN 5 THEN d END) Thu
     , MIN(CASE dw WHEN 6 THEN d END) Fri
     , MIN(CASE dw WHEN 7 THEN d END) Sat
  FROM (SELECT date_format(dt,'%Y%m') ym
             , Week(dt) w
             , Day(dt) d
             , DayofWeek(dt) dw
          FROM (SELECT CONCAT(y, '0101') + INTERVAL a*100 + b*10 + c DAY dt
                  FROM (SELECT 0 a
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        ) a
                     , (SELECT 0 b
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        UNION ALL SELECT 4
                        UNION ALL SELECT 5
                        UNION ALL SELECT 6
                        UNION ALL SELECT 7
                        UNION ALL SELECT 8
                        UNION ALL SELECT 9
                        ) b
                     , (SELECT 0 c
                        UNION ALL SELECT 1
                        UNION ALL SELECT 2
                        UNION ALL SELECT 3
                        UNION ALL SELECT 4
                        UNION ALL SELECT 5
                        UNION ALL SELECT 6
                        UNION ALL SELECT 7
                        UNION ALL SELECT 8
                        UNION ALL SELECT 9
                        ) c
                     , (SELECT '2015' y) d
                 WHERE a*100 + b*10 + c < DayOfYear(CONCAT(y, '1231'))
                ) a
        ) a
 GROUP BY ym, w
;

 

by 고슴도치 [2015.07.06 11:06:56]

감사합니다. 많이 배우고 있습니다.


by 너구링 [2015.07.08 09:46:47]

대단하네요.... 감사합니다. 많이배웁니다. 


by 데이타베이스초보 [2017.09.13 11:17:07]

완전초보라그런데 select ym 은 어떤쿼리로 짜야되는건가요 ㅠㅠ?

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