Как объединить две или более строк и СУММИРОВАТЬ столбец в ОБНОВЛЕНИИ без первичного ключа?
У меня есть такая таблица в SQL Server:
По сути, эта таблица содержит сведения о товаре с соответствующим CTransaction. Я хочу объединить и обновить строки с одинаковыми CTransaction и CItemCode, суммируя их CQuantity.
После обновления в результате получится что-то вроде таблицы ниже...
Я нашел аналогичный вопрос в здесь, однако пример включает первичный ключ. В моей таблице нет первичного ключа, и я понятия не имею, как это сделать. Возможно ли это вообще? Что я могу попробовать дальше?
Это было бы легко, если бы я просто собирал SELECT строки следующим образом:
SELECT
CTransaction, SUM(CQuantity), CItemCode, CUOM
FROM
Table
GROUP BY
CTransaction, CItemCode, CUOM
Но я не хочу этого. Я хочу обновить таблицу, чтобы навсегда удалить дубликаты.
@TimBiegeleisen Нет, я буду делать это периодически. Я не понимаю, что вы имеете в виду, говоря о создании новой таблицы и INSERT. Спасибо.
Просто создайте представление поверх этой таблицы.
Обратите внимание, что мы предпочитаем здесь технический стиль письма. Мы мягко не поощряем приветствия, «надеюсь, что вы сможете помочь», «спасибо», «заранее спасибо», благодарственные письма, приветы, добрые пожелания, подписи, «пожалуйста, можете ли вы помочь», материалы для болтовни и сокращенный txtspk, просьбы, как долго вы застрял, советы по голосованию, мета-комментарии и т. д. Просто объясните свою проблему и покажите, что вы пробовали, чего ожидали и что на самом деле произошло.
Ответы 2
Каждая таблица должна иметь первичный ключ. В этом случае первичный ключ предположительно должен быть CTransaction, CItemCode, CUOM, и вам следует исправить процесс, чтобы обновить его, а не добавлять новые повторяющиеся строки.
Однако для этого можно использовать MERGE. Один из способов приведен ниже (обновить произвольный файл для каждой группы и удалить остальные).
WITH Tgt AS
(
SELECT *,
SumCQuantity = SUM(CQuantity) OVER (PARTITION BY CTransaction, CItemCode, CUOM ),
rn = ROW_NUMBER() OVER (PARTITION BY CTransaction, CItemCode, CUOM ORDER BY CTransaction)
FROM YourTable
)
Merge Tgt with (HOLDLOCK)
USING (VALUES(1)) src(rn) ON Tgt.rn = 1
WHEN MATCHED AND CQuantity IS DISTINCT FROM SumCQuantity THEN
UPDATE SET CQuantity = SumCQuantity
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Или другая версия, предоставленная @Charlieface в комментариях (которая может быть более эффективной, поскольку позволяет избежать общей очереди подвыражений, вычисляя только текущую сумму - что можно сделать более потоковым способом, поскольку нет необходимости добавлять вычисленное значение вернуться к предыдущим строкам)
WITH target AS (
SELECT *,
SUM(t.CQuantity) OVER (PARTITION BY CTransaction, CItemCode ORDER BY CQuantity ROWS UNBOUNDED PRECEDING) AS TotalQty,
LEAD(1) OVER (PARTITION BY CTransaction, CItemCode ORDER BY CQuantity) AS IsNotLast
FROM YourTable t
)
MERGE target t
USING (VALUES(1)) AS v(dummy)
ON 1=1
WHEN MATCHED AND IsNotLast = 1 THEN DELETE
WHEN MATCHED AND CQuantity IS DISTINCT FROM TotalQty THEN
UPDATE
SET CQuantity = TotalQty;
Я понятия не имею, что вы только что сделали, но это работает как шарм. Большое спасибо. Мне придется изучить каждую часть вашего оператора SQL, чтобы мне не приходилось задавать подобные вопросы в будущем. Еще раз огромное спасибо.
@ chris_techno25 — он использует оконные функции/агрегаты для вычисления суммы по группе и применения последовательной нумерации строк внутри каждой группы. Вы можете увидеть результат здесь dbfiddle.uk/2yIceFym и MERGE просто обновляет строки, в которых был номер строки 1, и удаляет остальные (так как это будут нежелательные дубликаты)
Учитывая, что вы заявили, что вам может потребоваться периодическое объединение, вероятно, имеет смысл создать представление поверх исходной таблицы, используя уже созданный вами агрегатный запрос:
CREATE VIEW yourView AS
SELECT CTransaction, CItemCode, CUOM, SUM(CQuantity)
FROM yourTable
GROUP BY CTransaction, CItemCode, CUOM;
Тогда просто используйте SELECT * FROM yourView всякий раз, когда вам понадобится доступ к этим данным.
Если это одноразовая вещь, просто создайте новую таблицу, а затем используйте
INSERT INTO ..., а затем свой выбор.