重复行的SQL和值到第一个（或最后一个）

I want to merge the duplicated rows (with same doctype, docnum and item) and sum the value of their 'qtd' column to the first one or the last one. Then delete the duplicate rows keeping just the one that caries the total value. (The line number is not important after deleting it can allow not sequential numbers)

我想合并重复的行(使用相同的doctype,docnum和item),并将'qtd'列的值与第一个或最后一个值相加。然后删除重复的行,只保留负载总值的行。 (删除后的行号不重要,可以不允许顺序号码)

Actually I only have the query to detect the duplicated rows, I need the query to merge the value and to delete the duplicated rows that remain after the merge.

实际上我只有查询来检测重复的行,我需要查询来合并值并删除合并后剩余的重复行。

PS: This is a big table so the doctype, docnum and item cannot be expressed in the query like this... where doctype='stket' and docum='1' and item='4506' they must be found "automatically"

PS:这是一个大表,所以doctype,docnum和item不能在这样的查询中表达...其中doctype ='stket'和docum ='1'和item ='4506'它们必须“自动”找到

Table: docs (initial with the duplicated rows)

表:docs(带有重复行的初始)

      doctype  docnum  line    item      qtd
       STKET     1       1   **4506**   10.00
       STKET     1       2     3860     27.00
       STKET     1       3   **4506**    4.00
       STKET     2       1     3860      7.00
       STKET     3       1   **4048**    2.00
       STKET     3       2   **4048**    5.00
       STKET     3       3     4876     11.00
       STKET     4       1     3860      1.00

Table: docs (expected result after sum)

表:docs(总和后的预期结果)

      doctype  docnum  line    item      qtd
       STKET     1       1     4506   **14.00**
       STKET     1       2     3860     27.00
       STKET     1       3     4506      4.00
       STKET     2       1     3860      7.00
       STKET     3       1     4048    **7.00**
       STKET     3       2     4048      5.00
       STKET     3       3     4876     11.00
       STKET     4       1     3860      1.00

Table: docs (expected result after deleting duplicated - in this case not the first!)

表:docs(删除重复后的预期结果 - 在这种情况下不是第一个!)

      doctype  docnum  line    item      qtd
       STKET     1       1     4506   **14.00**
       STKET     1       2     3860     27.00
       STKET     2       1     3860      7.00
       STKET     3       1     4048    **7.00**
       STKET     3       3     4876     11.00
       STKET     4       1     3860      1.00

[later edit] - from "answer"

[稍后编辑] - 来自“回答”

After the ZLK query I keep the table docs like this:

在ZLK查询之后,我保持表格文档如下:

  doctype  docnum  line    item      qtd
   STKET     1       1     4506   **10.00**
   STKET     1       2     3860     27.00
   STKET     2       1     3860      7.00
   STKET     3       1     4048    **2.00**
   STKET     3       3     4876     11.00
   STKET     4       1     3860      1.00

It finds the duplicates and deletes but doesn't sum the 'qtd' value of the deleted rows

它找到重复项和删除项,但不会对已删除行的“qtd”值求和

1 个解决方案

#1

So I'm assuming all you want is a basic update statement then delete statement.

所以我假设您只需要一个基本的更新语句然后删除语句。

UPDATE docs SET qtd = updateqtd
FROM docs d
JOIN (SELECT doctype, docnum, item, MIN(line) line, SUM(qtd) updateqtd
FROM docs
GROUP BY doctype, docnum, item) a ON a.doctype = d.doctype AND a.docnum = d.docnum AND a.item = d.item AND a.line = d.line;

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY doctype, docnum, item ORDER BY line) RN
    FROM docs)
DELETE CTE WHERE RN <> 1

This updates the lowest line number then deletes any that aren't the lowest line number.

这将更新最低行号,然后删除任何非最低行号。

#1