С нескольких строк в одну

Question

Доброе время суток форумчане,
Есть вот такой запрос:

SELECT t1.sum_free_days,
       ud1.parent_user_id AS USER_ID,
       ud1.full_name,
       ( CASE
           WHEN t1.freeze_days_vocation = 1 THEN 'Заморожено'
           ELSE ''
         END )            AS FREEZE_DAYS
FROM   users_data ud1
       RIGHT JOIN (SELECT Sum(min_free_day)     AS SUM_FREE_DAYS,
                          Max(t.user_id)        AS USER_ID,
                          Max(t.parent_user_id) AS PARENT_USER_ID,
                          t.freeze_days_vocation
                   FROM   (SELECT Min(uv.remainder_days) AS MIN_FREE_DAY,
                                  Max(ud.id)             AS USER_ID,
                                  ud.parent_user_id,
                                  uv.freeze_days_vocation,
                                  ud.archive
                           FROM   users_vocation uv
                                  LEFT JOIN users_data ud
                                         ON ud.parent_user_id =
                                            uv.parent_user_id
                                            AND ud.archive = 1
                           WHERE  uv.type_record IN ( 0, 1 )
                                  AND uv.period_last_day = 1
                           GROUP  BY uv.period_from,
                                     uv.period_to,
                                     ud.id,
                                     ud.parent_user_id,
                                     ud.full_name,
                                     ud.archive,
                                     uv.freeze_days_vocation) t
                   GROUP  BY t.freeze_days_vocation) t1
               ON t1.user_id = ud1.id

он выдает результат (см. картинку), можно ли этот результат чуть переделать/подогнать к результату (см.картинку)?

Заранее спасибо за ответ

Answer 1

Возможно вам подойдет последующая группировка по id, например:

SELECT SUM(
    IF(
      t1.freeze_days_vocation != 1, t1.sum_free_days, 0
    )
  ) AS FREE_DAYS,
  ud1.parent_user_id AS USER_ID,
  ud1.full_name,
  SUM(
    IF(t1.freeze_days_vocation = 1, t1.sum_free_days, 0)
  ) AS FREEZE_DAYS
FROM users_data ud1
  RIGHT JOIN (
    SELECT Sum(min_free_day) AS SUM_FREE_DAYS,
      Max(t.user_id) AS USER_ID,
      Max(t.parent_user_id) AS PARENT_USER_ID,
      t.freeze_days_vocation
    FROM (
        SELECT Min(uv.remainder_days) AS MIN_FREE_DAY,
          Max(ud.id) AS USER_ID,
          ud.parent_user_id,
          uv.freeze_days_vocation,
          ud.archive
        FROM users_vocation uv
          LEFT JOIN users_data ud ON ud.parent_user_id = uv.parent_user_id
          AND ud.archive = 1
        WHERE uv.type_record IN (0, 1)
          AND uv.period_last_day = 1
        GROUP BY uv.period_from,
          uv.period_to,
          ud.id,
          ud.parent_user_id,
          ud.full_name,
          ud.archive,
          uv.freeze_days_vocation
      ) t
    GROUP BY t.freeze_days_vocation
  ) t1 ON t1.user_id = ud1.id
GROUP BY ud1.id

Answer 2

select res1.SUM_FREE_DAYS, res4.PARENT_USER_ID, ud2.FULL_NAME, res4.SUM_FREEZE_DAYS,
    (case when (res4.SUM_FREEZE_DAYS is null and res1.SUM_FREE_DAYS is not null) then res1.SUM_FREE_DAYS
    when (res4.SUM_FREEZE_DAYS is not null and res1.SUM_FREE_DAYS is null) then res4.SUM_FREEZE_DAYS
    when (res4.SUM_FREEZE_DAYS is not null and res1.SUM_FREE_DAYS is not null) then res4.SUM_FREEZE_DAYS+res1.SUM_FREE_DAYS
     end) as SUM_FREEZE_FREE
  from
    (select distinct(uu.PARENT_USER_ID), res2.SUM_FREE_DAYS as SUM_FREEZE_DAYS, res2.FULL_NAME
     from users_vocation uu
     left join
      (select t1.SUM_FREE_DAYS, ud1.PARENT_USER_ID, ud1.FULL_NAME, t1.FREEZE_DAYS_VOCATION as FREEZE_DAYS
         from USERS_DATA ud1
           right join (select sum(MIN_FREE_DAY) as SUM_FREE_DAYS, max(t.USER_ID) as USER_ID, max(t.PARENT_USER_ID) as PARENT_USER_ID, t.FREEZE_DAYS_VOCATION
                         from (select min(uv.REMAINDER_DAYS) as MIN_FREE_DAY, max(ud.ID) as USER_ID, ud.PARENT_USER_ID, uv.FREEZE_DAYS_VOCATION, ud.ARCHIVE
                                 from USERS_VOCATION uv
                                 left join USERS_DATA ud on ud.PARENT_USER_ID = uv.PARENT_USER_ID and ud.ARCHIVE = 1
                               where uv.TYPE_RECORD in (0, 1, 3) and uv.PERIOD_LAST_DAY = 1
                                 group by uv.PERIOD_FROM, uv.PERIOD_TO, ud.ID, ud.PARENT_USER_ID, ud.FULL_NAME, ud.ARCHIVE, uv.FREEZE_DAYS_VOCATION) t
         where t.FREEZE_DAYS_VOCATION = 1
           group by t.FREEZE_DAYS_VOCATION, t.PARENT_USER_ID) t1 on t1.USER_ID = ud1.ID) res2 on res2.PARENT_USER_ID = uu.PARENT_USER_ID) res4

   left join
     (select t1.SUM_FREE_DAYS, ud1.PARENT_USER_ID, ud1.FULL_NAME, t1.FREEZE_DAYS_VOCATION as FREEZE_DAYS
         from USERS_DATA ud1
         right join (select sum(MIN_FREE_DAY) as SUM_FREE_DAYS, max(t.USER_ID) as USER_ID, max(t.PARENT_USER_ID) as PARENT_USER_ID, t.FREEZE_DAYS_VOCATION
                       from (select min(uv.REMAINDER_DAYS) as MIN_FREE_DAY, max(ud.ID) as USER_ID, ud.PARENT_USER_ID, uv.FREEZE_DAYS_VOCATION, ud.ARCHIVE
                               from USERS_VOCATION uv
                               left join USERS_DATA ud on ud.PARENT_USER_ID = uv.PARENT_USER_ID and ud.ARCHIVE = 1
                             where uv.TYPE_RECORD in (0, 1) and uv.PERIOD_LAST_DAY = 1
                               group by uv.PERIOD_FROM, uv.PERIOD_TO, ud.ID, ud.PARENT_USER_ID, ud.FULL_NAME, ud.ARCHIVE, uv.FREEZE_DAYS_VOCATION) t
  where t.FREEZE_DAYS_VOCATION = 0
    group by t.FREEZE_DAYS_VOCATION, t.PARENT_USER_ID) t1 on t1.USER_ID = ud1.ID) res1 on res1.PARENT_USER_ID = res4.PARENT_USER_ID
   left join USERS_DATA ud2 on ud2.PARENT_USER_ID=res4.PARENT_USER_ID and ud2.ARCHIVE = 1
;

БЛОГ НА HUSL

С нескольких строк в одну

Ответы (2 шт):