Devart Forum

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

SELECT distinct 
     a.id
     , a.txt
     , a.rub_id
     , a.rub
     , a.vid
     , a.id_obj_site
     , a.id_user_ekr
     , a.Deleted
     , a.paid
     , a.id_team
     , a.gdn
	 , a.rub_code

FROM
  kvartal_prem.ads a
left JOIN datas d
ON (d.id_ads = a.id)

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

  with fUniQuery do
  begin
    Close;
    DeleteWhere;
    KeyFields := 'id';

    //если выбрана какая-то бригада(ы)
    if team_list <> '' then
      AddWhere('a.id_team in (' + team_list + ')');

    //активные
    AddWhere('a.Deleted = 0');

    //отключаем ЖиДН (берем только в Премьер)
    AddWhere('a.gdn = 0');

    AddWhere('d.date_out = :dt_out');
    ParamByName('dt_out').AsDate := date_out;

    ShowMessage(SQL.Text);
  end;//with

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

SELECT distinct 
     a.id
     , a.txt
     , a.rub_id
     , a.rub
     , a.vid
     , a.id_obj_site
     , a.id_user_ekr
     , a.Deleted
     , a.paid
     , a.id_team
     , a.gdn
	 , a.rub_code

FROM
  kvartal_prem.ads a
left JOIN datas d
ON (d.id_ads = a.id)
WHERE (((a.id_team in (12,11,5,9,328)) AND a.Deleted = 0) AND a.gdn = 0) AND d.date_out = :dt_out 

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

WHERE a.id_team in (827,21,17,15) AND a.Deleted = 0 AND a.gdn = 0 AND d.date_out = :dt_out 

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

WHERE (a.id_team in (827,21,17,15)) AND (a.Deleted = 0) AND (a.gdn = 0) (AND d.date_out = :dt_out )

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

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
     , count(o.id) AS ex_count
     , (
       CASE count(o.id)
       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)

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

LEFT JOIN kvartal_prem.vid v
ON (v.id = a.vid AND v.deleted = 0 /*&dt_start &dt_end*/)

LEFT JOIN kvartal_ekr.object_n o
ON (o.id_infosource_exclusive = u.id AND o.is_deleted = 0)


WHERE
  u.is_deleted = 0
  AND u.is_blocked = 0
GROUP BY
  1

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

/**/

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

UPDATE users /* пробовал и так: kvartal_ekr.users*/
SET
  plus_sum_premier = :plus_sum_premier, prepayment_premier = :prepayment_premier
WHERE
  id = :Old_id

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

select id, name from table1 where date1 > :date1 and date1 < :date2

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

UniQuery1.ClearWhere;
UniQuery1.AddWhere('условие1', 'id > 100');
UniQuery1.AddWhere('условие2', 'id < 500');
UniQuery1.DeleteWhere('условие2');
или по индексу
UniQuery1.DeleteWhere(1);
UniQuery1.AddWhere('условие2', 'id < 500');
UniQuery1.ApplyWhere; и UniQuery1.CancelWhere;
UniQuery1.Open;


UniQuery1.RestorePrimarySQL;

Var
MyWhere : TUniWhere;

MyWhere := UniQuery1.FindWhere('условие');
if not assigned(MyWhere) then ShowMessage('Условие не найдено');