编写SQL查询以查询并更新同一张表

Question

我对SQL不是很熟悉，所以需要在表上编写一个SQL更新/删除查询。要求如下。

表A：

1                 Code1        Value1            5

2                 Code2       Value2             2

3                 Code1         Value1            3

4                 Code3         Value3            8

考虑到上述表格，在其中有多行具有相同的Col2和Col3值。我想编写一个查询，它将删除Col2和Col3组合的重复项，然后对结果记录的数量求和。

结果应该像这样:

Col1(PK)     Col2            Col3            Quantity

1                 Code1        Value1            8

2                 Code2       Value2             2

4                 Code3         Value3            8

- Mayur Jadhav

---

我不认为在不使用事务或存储过程的情况下可以在一个查询中执行插入/更新和删除操作。你是如何运行这些查询的（命令行、服务器端语言……）？ - Mirceac21

我对SQL不是很友好，这让我咯咯笑了 :) - TT.

Answer 1

使用这个：

  select *
    from (select *, sum(Quantity) over (partition by col2,col3) as Quantity
          from tableA
         ) t

- Muhammad Muazzam

Answer 2

一种选择是将你想要的结果集仅使用SELECT语句插入到新表中，然后删除旧表：

CREATE TABLE A_new(Col1 INT PRIMARY KEY,
                   Col2 varchar(255),
                   Col3 varchar(255),
                   Quantity INT);

INSERT INTO A_new (Col1, Col2, Col3, Quantity)
SELECT MIN(Col1) AS Col1, Col2, Col3, SUM(Quantity) AS Quantity
FROM A
GROUP BY Col2, Col3

接下来，您可以删除表A并将A_new重命名为A：

DROP TABLE A
sp_rename A_New, A

- Tim Biegeleisen

---

我没有数据库的创建/删除权限。 - Mayur Jadhav

我认为@Mirceac21是正确的，存储过程或事务是唯一的希望。 - Tim Biegeleisen

如果您的实时数据库中有大量记录，则删除/创建不是一个好的做法。 - Sudhir Panda

作为一次性事件，它是可以商议的。 - Tim Biegeleisen

Answer 3

您需要分两个部分完成此操作，如果要确保数据的完整性，则这两个部分应该被包含在一个事务中。

第一部分更新所需行的数量，第二部分删除现在重复的行。

BEGIN TRANSACTION

UPDATE TableA
SET Quantity=upd.Quantity
FROM TableA a
INNER JOIN (
    SELECT MIN(Col1) AS Col1, SUM(Quantity)  AS Quantity
    FROM TableA
    GROUP BY Col2, Col3 
) upd
ON a.Col1 = upd.col1

;WITH DELETES
AS
(
  SELECT Col1,ROW_NUMBER() OVER (PARTITION BY Col2,Col3 ORDER BY Col1) rn
  FROM TableA
)
DELETE FROM TableA WHERE Col1 IN (
    SELECT Col1 FROM DELETES WHERE rn>1
)

COMMIT TRANSACTION

实例演示：http://www.sqlfiddle.com/#!3/9efa9/7

（编辑：已更新以解决评论中提到的问题）

- Jamiec

---

你说出了我口中所未曾言语的话。 - Tim Biegeleisen

你介意解释一下 DELETE 如何工作吗？它似乎是递归的。 - Tim Biegeleisen

@sm.abdullah - 你能详细解释一下你认为的问题是什么吗？这个问题的重点是删除具有“相同值的两个记录”的列。最好使用我的sqlfiddle作为基础来演示解释问题。 - Jamiec

编写了一个测试用例 http://www.sqlfiddle.com/#!3/daf670/1 您将看到重复记录。 您只删除最大的记录。 - sm.abdullah

是的，我想我发现了你所说的问题。正在修复中。 - Jamiec

Answer 4

对于第一次更新步骤（假设表A的名称为Table_1）：

Update Table_1 set Quantity = t.total 
from Table_1 As p inner join 
(select Min(Col1) as Col1,SUM(quantity) as total from Table_1 group by Col2,Col3) as t
on p.Col1=t.Col1

这将更新每一行的数量总和超过1行的行，然后您可以删除其code2具有相同值的相同行。

WITH CTE AS(
   SELECT 
       RN = ROW_NUMBER()OVER(PARTITION BY Col2,Col3 ORDER BY Col2,Col3)
   FROM Table_1
)
DELETE FROM CTE WHERE RN > 1;

抱歉，我以为Col2始终与Col3相同。*我已经编辑了我的陈述。如果您得到多行，则可能会删除除第一行之外的所有内容。

- Mark

---

样本数据： 列1 列2 列3 数量 1 代码1 值1 1<p> 2 代码2 值2 1<p> 5 代码1 值1 1<p> 4 代码3 值3 1<p> 6 代码1 值4 1 - Mayur Jadhav

为什么删除语句无法正常工作？在这里它完全正常工作。我还创建了相同的表并尝试了它。你能解释更多吗？ - Mark

Answer 5

请执行以下查询：

SELECT 
    Col2,
    Col3,
    SUM(Quantity) 
FROM table_1
GROUP BY
    Col2,
    Col3

- Bala Sakthis

---

我没有给你点踩，你的问题也是正确的方向。或许你可以进一步解释一下你的推理过程？ - Tim Biegeleisen

该问题还要求更新表格，而这只是选择了数据（确实是正确的）。 - Jamiec

@Jamiec，我错过了。我知道jsfiddle网站。从你的回答中，我知道了sqlfiddle网站。谢谢！ - Bala Sakthis