Devart Forum

Код: Выделить всё

SELECT u.id
     , u.fio
     , u.login
     , u.id_brigadir
     , u.plus_sum_premier
     , u.prepayment_premier
     , sum(if(dayofweek(d.date_out) = 2, if(a.paid = 0, v.price_d, 0), if(a.paid = 0, v.price, 0))) AS sum_free
     , sum(if(dayofweek(d.date_out) = 2, if(a.paid = 1, v.price_d, 0), if(a.paid = 1, v.price, 0))) AS sum_paid
     , (SELECT count(o.id)
        FROM
          kvartal_ekr.object_n o
        WHERE
          o.id_infosource_exclusive = u.id
          AND o.is_deleted = 0) AS ex_count
     , (
       CASE (SELECT count(o.id)
             FROM
               kvartal_ekr.object_n o
             WHERE
               o.id_infosource_exclusive = u.id
               AND o.is_deleted = 0)

       WHEN 0 THEN
         20.4
       WHEN 1 THEN
         25.5
       WHEN 2 THEN
         30.6
       ELSE
         35.7
       END) AS sum_default


FROM
  kvartal_ekr.users u

LEFT JOIN kvartal_prem.ads a
ON (u.id = a.id_user_ekr AND a.Deleted = 0 AND a.gdn = 0)

LEFT JOIN kvartal_prem.datas d
ON (d.id_ads = a.id & dt_start & dt_end)

LEFT JOIN kvartal_prem.vid v
ON (v.id = a.vid AND v.deleted = 0)

WHERE
  u.id = 331 and
  u.is_deleted = 0
  AND u.is_blocked = 0


GROUP BY
  1