sql - 如何将重复的行更新为一行

Question

我需要合并相同的条目并逐个数量行汇总数量。例如：

glass type  height   width    quantity
---------------------------------------
4DC          1500     600        1
4DC          1500     600        2
4DC          1200     500        5
4DC          1200     500        2
3DC          1500     600        2

将会：

glass type   height   width   quantity
---------------------------------------
4DC           1500     600      3
4DC           1200     500      7
3DC           1500     600      2

但我不想要任何选择查询，我需要更新表并删除重复的行并用总和更新其中一个。

我该怎么做？

标签： sqlsql-serverduplicatesrows

Answer 1

我的建议是更换表格：

select glasstype, height, width, sum(quantity) as quantity
into temp_t
from t
group by glasstype, height, width;

truncate table t;  -- backup first!

insert into temp_t (glasstype, height, width, quantity)
    select glasstype, height, width, quantity
    from temp_t;

drop table temp_t;

或者，您可以分两步执行此操作：

with toupdate as (
      select t.*, sum(quantity) over (partition by glasstype, height, width) as new_quantity
      from t
     )
update toupdate
    set quantity = new_quantity;

with todelete as (
      select t.*,
             row_number() over (partition by glasstype, height, width order by glasstype) as seqnum
      from t
     )
delete from todelete
    where seqnum > 1;