2022.08.15.

SELECT CASE
           WHEN A.SUBSTR = DATE_FORMAT(now(), '%y%m%d') THEN DATE_FORMAT(now(), '%y:%m:%d')
           ELSE
               CASE
                   WHEN LENGTH(A.SUBSTR) = 2 THEN A.SUBSTR
                   WHEN LENGTH(A.SUBSTR) = 4 THEN CONCAT(SUBSTR(A.SUBSTR, 1, 2), SUBSTR(A.SUBSTR, 3, 2))
                   WHEN LENGTH(A.SUBSTR) = 6 THEN CONCAT(SUBSTR(A.SUBSTR, 1, 2), SUBSTR(A.SUBSTR, 3, 2), SUBSTR(A.SUBSTR, 5, 2))
                   END
    END                                                AS "기준"
     , FORMAT(A.TODAY, 0)                              AS "금일"
     , FORMAT(A.DAY1, 0)                               AS "-1일"
     , FORMAT(A.DAY2, 0)                               AS "-2일"
     , FORMAT(A.DAY3, 0)                               AS "-3일"
     , FORMAT(A.DAY4, 0)                               AS "-4일"
     , FORMAT(A.DAY5, 0)                               AS "-5일"
     , FORMAT(A.DAY6, 0)                               AS "-6일"
     , FORMAT(A.DAY7, 0)                               AS "-7일"
     , IF(DAY1 = 0, 0, ROUND((TODAY / DAY1) * 100, 1)) AS "전일 대비(%)"
     , IF(DAY7 = 0, 0, ROUND((TODAY / DAY7) * 100, 1)) AS "전주 대비(%)"
FROM (SELECT A.SUBSTR
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE, '%y%m%d'), 1, 0))                  "TODAY"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 1 DAY, '%y%m%d'), 1, 0)) "DAY1"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 2 DAY, '%y%m%d'), 1, 0)) "DAY2"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 3 DAY, '%y%m%d'), 1, 0)) "DAY3"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 4 DAY, '%y%m%d'), 1, 0)) "DAY4"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 5 DAY, '%y%m%d'), 1, 0)) "DAY5"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 6 DAY, '%y%m%d'), 1, 0)) "DAY6"
           , SUM(IF(A.일자 = DATE_FORMAT(:BASE_DATE - INTERVAL 7 DAY, '%y%m%d'), 1, 0)) "DAY7"
      FROM (SELECT DATE_FORMAT(A.CREATED_AT, '%y%m%d')               as 일자
                 #            , DATE_FORMAT(A.CREATED_AT, '%h%i%s')               as CREATE_TIME
                 # 시 분 초 구분
                 , SUBSTR(DATE_FORMAT(A.CREATED_AT, '%h%i%s'), 1, 2) as SUBSTR
#                  , SUBSTR(DATE_FORMAT(A.CREATED_AT, '%h%i%s'), 1, 4) AS SUBSTR
#                              , SUBSTR(DATE_FORMAT(A.CREATED_AT, '%h%i%s'), 1, 6) AS SUBSTR
            FROM book A
            WHERE 1 = 1
              AND DATE_FORMAT(A.CREATED_AT, '%y%m%d') IN (
                                                          DATE_FORMAT(:BASE_DATE, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 1 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 2 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 3 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 4 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 5 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 6 DAY, '%y%m%d'),
                                                          DATE_FORMAT(:BASE_DATE - INTERVAL 7 DAY, '%y%m%d')
                )) A
      GROUP BY A.SUBSTR
     ) A
ORDER BY A.SUBSTR
;

select
    a.id
     , a.title
     , (select group_concat(b.id SEPARATOR '|') from study b where b.book_id = a.id) as studies
from book a
group by a.id
;