编写SQL查询以查询和更新同一个表

I am not very friendly with SQL so I need to write a SQL update/delete query on a table. The requirement is as below.

我对SQL不是很友好所以我需要在表上编写SQL更新/删除查询。要求如下。

Table A:

Col1(PK) Col2 Col3 Quantity

1 Code1 Value1 5

2 Code2 Value2 2

3 Code1 Value1 3

4 Code3 Value3 8

Considering above table, in which there are multiple rows with same value for Col2 and Col3. I want to write a query which will delete duplicate combination of Col2 and Col3 and sum the Quantity for the resulting record.

考虑上表,其中有多个行具有相同的Col2和Col3值。我想写一个查询,它将删除Col2和Col3的重复组合,并对结果记录的数量求和。

The result should be like this:

结果应该是这样的:

Col1(PK) Col2 Col3 Quantity

1 Code1 Value1 8

2 Code2 Value2 2

4 Code3 Value3 8

5 个解决方案

#1

You will need to do this in two parts, and if you want to ensure the integrity of the data, the two parts should be wrapped in a transaction.

您需要分两部分进行此操作,如果要确保数据的完整性,则应将两部分包装在事务中。

The first part updates the required rows' Quantity, the second deletes the now duplicated rows.

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

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

Live example: http://www.sqlfiddle.com/#!3/9efa9/7

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

(EDIT: Updated to fix issue noted in comments)

(编辑:更新以修复评论中提到的问题)

#2

Use this:

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

#3

One option would be to just SELECT the result set you want into a new table, and then drop the previous table:

一种选择是将所需的结果集选择到新表中,然后删除上一个表:

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

Next you can drop table A and rename A_new to A:

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

DROP TABLE A
sp_rename A_New, A

#4

For the first Update step(Assume that Table A has the name of Table_1) :

对于第一个Update步骤(假设表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

this will update each row that has more than 1 row with its SUM of quantity.

这将更新具有多个SUM数量的多行的每一行。

then you can delete the same row which its code2 has the same value with :

那么你可以删除其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;

Sorry, I thought that Col2 would always be the same with Col3. *I have edited my statements. If you got more than 1 row, it could Delete all, except the first row.

对不起,我以为Col2与Col3总是一样的。 *我已经编辑了我的陈述。如果你有超过1行,它可以删除所有,除了第一行。

#5

-1

Please execute the below query:

请执行以下查询:

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

#1