group by子句查询

Question

我有一个表，里面有这样的值，

我想按客户名称分组，其中本地金额的总和应超过50000，否则我需要删除那些不满足的记录？

如何在sql server 2005中实现？

TRN 259 3   9/9/2010    6622    68667(Rs) ABHIJIT KATARE  
TRN 260 3   9/9/2010    6622    14635(Rs)   ABHIJIT KATARE  
TRN 235 3   9/9/2010    6586    68128   AJAY PARASRAMPURIA  
TRN 236 3   9/9/2010    6586    14490   AJAY PARASRAMPURIA  
TRN 257 3   9/9/2010    6621    68667   ANAND DESAI  
TRN 258 3   9/9/2010    6621    14635   ANAND DESAI  
TRN 287 3   9/9/2010    6817    119095  ANAND KATAKAM  
TRN 242 3   9/9/2010    6594    95689   ANILKUMAR MUTHUNPARA  
TRN 211 3   9/9/2010    6507    52239   ARBIND KUMAR GUPTA  
TRN 212 3   9/9/2010    6538    63183   ASHOK KELKAR  
TRN 185 3   9/9/2010    6431    140610  BANSAL Y.K  
TRN 186 3   9/9/2010    6431    46845   BANSAL Y.K  
TRN 248 3   9/9/2010    6600    72565   BENNO HANS LUKE  

Answer 1

这将向您显示少于50000的客户：

select CustomerName, sum(LocalAmount)
from MyTable
group by CustomerName
having sum(LocalAmount) <= 50000

请注意，如果有CustomerID，最好使用grouping进行分组，因为可能会有重复的名称。然后，您可以执行以下操作来删除：

delete from MyTable 
where CustomerID in (
    select CustomerID 
    from MyTable
    group by CustomerID 
    having sum(LocalAmount) <= 50000
)

Answer 2

尝试对select执行以下操作：

SELECT * FROM Table
GROUP BY CustomerName
HAVING SUM(LocalAmount) > 50000

尝试删除(@RedFilter的修改版本)：

delete from MyTable 
where CustomerName in (
    select CustomerName 
    from Table
    group by CustomerName 
    having sum(LocalAmount) <= 50000
)

Answer 3

select * from MyTable group by CustomerName having sum(LocalAmount) > 50000