Различия
Здесь показаны различия между двумя версиями данной страницы.
| — |
администрирование:скрипты:цбд:web:рейтинг.докторов [2019/07/04 08:24] (текущий) |
||
|---|---|---|---|
| Строка 1: | Строка 1: | ||
| + | ====== Рейтинг докторов ====== | ||
| + | <code sql> | ||
| + | /* | ||
| + | * Запрос обновления пользовательского рейтинга по докторам по 5 параметрам | ||
| + | * Запрос переработан из стандартного | ||
| + | * | ||
| + | * Автор: Коробка А.А. | ||
| + | */ | ||
| + | MERGE INTO markrates mr | ||
| + | USING (select markrecid, markrectype, marktypeid | ||
| + | |||
| + | ,nullif(count(cast(markvalue as tfloat)),0) marknumber | ||
| + | ,avg(cast(markvalue as tfloat)) ratevalue | ||
| + | |||
| + | ,nullif(count(cast(user$markvalue2 as tfloat)),0) user$marknumber2 | ||
| + | ,avg(cast(user$markvalue2 as tfloat)) user$ratevalue2 | ||
| + | |||
| + | ,nullif(count(cast(user$markvalue3 as tfloat)),0) user$marknumber3 | ||
| + | ,avg(cast(user$markvalue3 as tfloat)) user$ratevalue3 | ||
| + | |||
| + | ,nullif(count(cast(user$markvalue4 as tfloat)),0) user$marknumber4 | ||
| + | ,avg(cast(user$markvalue4 as tfloat)) user$ratevalue4 | ||
| + | |||
| + | ,nullif(count(cast(user$markvalue5 as tfloat)),0) user$marknumber5 | ||
| + | ,avg(cast(user$markvalue5 as tfloat)) user$ratevalue5 | ||
| + | |||
| + | from markvalues | ||
| + | where markrectype = 9 | ||
| + | group by markrecid, markrectype, marktypeid | ||
| + | ) mv ON ( mr.raterecid = mv.markrecid and | ||
| + | mr.raterectype = mv.markrectype and | ||
| + | mr.marktypeid = mv.marktypeid ) | ||
| + | WHEN MATCHED THEN | ||
| + | UPDATE SET mr.ratevalue = mv.ratevalue, mr.marknumber = mv.marknumber, | ||
| + | mr.user$ratevalue2 = mv.user$ratevalue2, mr.user$marknumber2 = mv.user$marknumber2, | ||
| + | mr.user$ratevalue3 = mv.user$ratevalue3, mr.user$marknumber3 = mv.user$marknumber3, | ||
| + | mr.user$ratevalue4 = mv.user$ratevalue4, mr.user$marknumber4 = mv.user$marknumber4, | ||
| + | mr.user$ratevalue5 = mv.user$ratevalue5, mr.user$marknumber5 = mv.user$marknumber5 | ||
| + | WHEN NOT MATCHED THEN | ||
| + | INSERT (mr.raterecid, mr.raterectype, mr.marktypeid, mr.ratevalue, mr.marknumber, | ||
| + | mr.user$ratevalue2, mr.user$marknumber2, | ||
| + | mr.user$ratevalue3, mr.user$marknumber3, | ||
| + | mr.user$ratevalue4, mr.user$marknumber4, | ||
| + | mr.user$ratevalue5, mr.user$marknumber5 | ||
| + | ) | ||
| + | VALUES (mv.markrecid, mv.markrectype, mv.marktypeid, mv.ratevalue, mv.marknumber, | ||
| + | mv.user$ratevalue2, mv.user$marknumber2, | ||
| + | mv.user$ratevalue3, mv.user$marknumber3, | ||
| + | mv.user$ratevalue4, mv.user$marknumber4, | ||
| + | mv.user$ratevalue5, mv.user$marknumber5) | ||
| + | </code> | ||
