MySQL 의 날짜와 로우넘버

MySQL 에서 Date 와 Rownum 을 만드는 쿼리 임

세상에는 나 보다 똑똑 하신 분들이 많네

/**
    make date calander 
*/


--
-- 현재 일자 부터 과거 로 몇일 
--
 SELECT dt.yyyymmdd
    FROM (
           SELECT
               curdate() - INTERVAL (100*a.a + (10 * b.a) + (c.a)) DAY AS yyyymmdd
           FROM (SELECT 0 AS a
                 UNION ALL SELECT 1
                 UNION ALL SELECT 2
                 UNION ALL SELECT 3
                 ) AS a
             CROSS JOIN (SELECT 0 AS a
                         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) AS b
             CROSS JOIN (SELECT 0 AS a
                         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) AS c
         ) dt
    where yyyymmdd BETWEEN curdate() - INTERVAL 10 day and curdate()
    order by 1
;

--
-- 년도별 칼렌더
--

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 AS dt
        FROM
          (
            SELECT 0 a
            UNION ALL SELECT 1
            UNION ALL SELECT 2
            UNION ALL SELECT 3
          ) aa,
          (
            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
          ) bb,
          (
            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
          ) cc,
          (
            SELECT '2018' y
          ) dd
        WHERE a * 100 + b * 10 + c < DayOfYear(concat(y, '1231'))
      ) a
  ) a
GROUP BY ym, w
;



/**
    MySQL Mk Rownum

*/

select
    @rnum := @rnum + 1 as Rownum
from
    ( select @rnum := -1 ) r