将多条记录合并到表的一行中 [英] Merge multiple records into one row in a table

问题描述

我有一个表，该表包含具有相同销售代理I​​D但销售额不同的多个记录。我如何删除多行，而只得到总价值的汇总。

I have a table which has multiple records of the same sales agent id but different sales amount. How can I delete the multiple rows and just have the aggregate of the total value.

让我们假设表结构如下-

Let us for example assume the table structure as follows -

SalesAgentId, SalesAgentName, SalesAmount
111         , John Doe      ,  8437.00
112         , John O Connor ,  5849.00
111         , John Doe      ,   438.00
112         , John O Connor ,  1234.00

我想要的是（即使它在另一个表中）以下内容-

What I would want is (even if it is into a different table) the following -

SalesAgentId, SalesAgentName, SalesAmount
111         , John Doe      ,  8875.00
112         , John O Connor ,  7083.00

我们可以使用SQL语句执行此操作吗？还是仅应使用存储过程？如果它使用的是SP，是否需要遍历每条记录...检查它是否始终存在，如果是，只需将SalesAmount字段添加到现有表中并实现它即可？

Can we do this using SQL statements or should it be an Stored Procedure only? If it is using an SP, do we have to iterate through each of the records...check if it is always there, if so, just add the SalesAmount field to the existing table and implement it?

推荐答案

SELECT SalesAgentId, SalesAgentName, SUM(SalesAmount) AS SalesAmount
  INTO #AggSales
  FROM Sales
 GROUP BY SalesAgentId, SalesAgentName;

TRUNCATE TABLE Sales;    

INSERT INTO Sales
SELECT * FROM #AggSales;

DROP TABLE #AggSales;

这篇关于将多条记录合并到表的一行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！