Блог

Я совершенно новичок в sql. Я думаю, что лучший способ сделать это; У меня есть таблица поступлений с этими полями:

 Receipt
-------
ReceiptID, AssociatedReceiptID, Value, Total
  

Я хочу обновить общее поле всех строк одним и тем же AssociatedReceiptID с суммой их полей значений. Итак, я попробовал следующий оператор sql:

 UPDATE Receipt r1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = r1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)
  

С более чем 100000 записей в этой таблице, это длится более 17 часов. Поскольку я обновляю ту же таблицу, которую запрашиваю, я решил разделить ее на два оператора updates, сохранив результат sum во временной таблице (которая имеет), а затем обновив таблицу поступлений этими значениями.

 UPDATE TemporaryTable t1
SET Total = (SELECT sum(Value)
             FROM Receipt r2
             WHERE r2.AssociatedReceiptID = t1.AssociatedReceiptID
             GROUP BY r2.AssociatedReceiptID)

UPDATE Receipt r1
SET Total = (SELECT Total
             FROM TemporaryTable t1
             WHERE t1.ReceiptID = r1.ReceiptID)
  

Используя эти инструкции, процесс обновления занимает 6-7 часов. Но я уверен, что должен быть лучший способ сделать это. Итак, вкратце, это мои вопросы:

• Как сделать это лучше?
• Подзапрос в операторах обновления выполняется один раз для каждой обновленной строки, не так ли? Итак, если есть 10 строк с одинаковым AssociatedReceiptID, сумма вычисляется 10 раз. Как я могу вычислить сумму только один раз для каждого ассоциированного идентификатора в инструкции update?

Заранее спасибо.

попробуйте создать временную таблицу в памяти:

 DECLARE @temp_receipts TABLE (
AssociatedReceiptID int,
sum_value int)
  

затем:

 insert into @temp_receipts
SELECT AssociatedReceiptID, sum(Value)
FROM Receipt
GROUP BY AssociatedReceiptID
  

а затем обновите итоги основной таблицы:

 UPDATE Receipt r
SET Total = (SELECT sum_value
             FROM @temp_receipts tt
             WHERE r.AssociatedReceiptID = tt.AssociatedReceiptID)
  

Однако я бы создал таблицу с именем receipt_totals или что-то в этом роде и использовал ее вместо этого. Нет смысла иметь общее количество каждой связанной квитанции в каждой отдельной связанной строке. если вы делаете это для удобства запроса, рассмотрите возможность создания представления между квитанциями и receipt_totals

Первоначально вы можете пойти так, как вы предлагаете в вопросе.

Для каждого изменения для одной строки, я думаю, было бы лучше использовать триггеры базы данных. Они обновят значение для каждой строки.

Вы можете прочитать о триггерах в MySQL здесь .

Возможно, вам потребуется использовать InnoDB в качестве механизма хранения.

Если вы не используете MySQL, пожалуйста, проверьте ссылку, соответствующую вашей СУБД.

 MERGE INTO Receipt r
USING (
        SELECT sum(Value) s, AssociatedReceiptID
        FROM Receipt           
        GROUP BY AssociatedReceiptID
      ) r_sum 
ON( r.AssociatedReceiptID = r_sum.AssociatedReceiptID)
WHEN MATCHED THEN UPDATE
set r.Total = r_sum.s
;
  

Я знаю, что это старый вопрос, но я думаю, что лучший способ — это.

 UPDATE r1
    SET r1.Total = r2.sumValue

FROM Receipt r1
    INNER JOIN 
        (SELECT sum(Value) sumValue,AssociatedReceiptID
        FROM Receipt rSum
        GROUP BY rSum.AssociatedReceiptID) r2 ON r2.AssociatedReceiptID = r1.AssociatedReceiptID
  

Вот все в одном запросе и вычислении только одного.

Надеюсь, это полезно.

Для больших таблиц копирование таблицы в новую (и одновременное внесение изменений) выполняется намного быстрее (по крайней мере, в Oracle DB), чем ее обновление.

Например:

 update table1 set some_num = some_num  1 where year = 2010;
  

намного медленнее, чем:

 create table table1b as
  select (case when year = 2010 then some_num 1 else some_num) as some_num,
      other, columns, of, the, table
  from table1;
drop table1;
rename table1b to table1; -- also fix or recreate constraints
  

(это верно и для удаления строк из таблицы: скопируйте все строки, которые должны остаться, в новую таблицу, а затем переименуйте ее вместо обычного удаления в исходной таблице)

Так что в вашем случае это было бы:

 create table ReceiptNew as
    select ReceiptID, AssociatedReceiptID, Value,
        sum(value) over (partition by AssociatedReceiptID) 
        as Total
    from Receipt;

drop table Receipt;
rename ReceiptNew to Receipt;
  

Опять же, вы должны заново установить ограничения для таблицы (кроме «NOT NULL», они переносятся автоматически).