T-Sql查找重复的行值

Question

我想写一个存储过程。

在那个存储过程中，我想从表中找到重复的行值，并计算这些行上的和操作到同一个表。

比方说，我有一个CustomerSales表;

ID SalesRepresentative Customer Quantity 
1 Michael    CustA  55  
2 Michael    CustA  10 

，我需要把表...

ID SalesRepresentative Customer Quantity 
1 Michael    CustA  65  
2 Michael    CustA  0 

当我发现在同一时间SalesRepresentative和Customer重复，我想总结这些行的所有Quantity值，并分配给一张桌子的第一排，其他人将是'0'。

你能帮助我吗？

Answer 1

聚集重复到一行：

SELECT min(ID) AS ID, SalesRepresentative, Customer 
     ,sum(Quantity) AS Quantity 
FROM CustomerSales 
GROUP BY SalesRepresentative, Customer 
ORDER BY min(ID) 

或者，如果你真的想与0那些额外行作为结果Quantity：

SELECT ID, SalesRepresentative, Customer 
     ,CASE 
      WHEN (count(*) OVER (PARTITION BY SalesRepresentative,Customer)) = 1 
      THEN Quantity 
      WHEN (row_number() OVER (PARTITION BY SalesRepresentative,Customer 
            ORDER BY ID)) = 1 
      THEN sum(Quantity) OVER (PARTITION BY SalesRepresentative,Customer) 
      ELSE 0 
     END AS Quantity 
FROM CustomerSales 
ORDER BY ID 

这使得大量使用的window functions。

替代版本，而窗函数：

SELECT min(ID) AS ID, SalesRepresentative, Customer, sum(Quantity) AS Quantity 
FROM CustomerSales 
GROUP BY SalesRepresentative, Customer 

UNION ALL 
SELECT ID, SalesRepresentative, Customer, 0 AS Quantity 
FROM CustomerSales c 
GROUP BY SalesRepresentative, Customer 
LEFT JOIN (
    SELECT min(ID) AS ID 
    FROM CustomerSales 
    GROUP BY SalesRepresentative, Customer 
    ) x ON (x.ID = c.ID) 
WHERE x.ID IS NULL 
ORDER BY ID