by子句查询组(group by clause query)

我有一个表，像这样的价值观，

我想按cusotmer名称，其中作为本地金额总和不得超过50000别的我需要删除那些不符合的记录？

如何实现它在2005年的SQL Server？

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:

尝试一下本作的选择：

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 2:

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

Answer 3:

看到好行使用：

SELECT
    cusotmerName ,SUM(localAmount)
    FROM YourTable
    GROUP BY cusotmerName
    HAVING SUM(localAmount)>50000 

删除有问题的行使用：

DELETE YourTable
WHERE
    CustomerName IN (SELECT
                             cusotmerName
                             FROM YourTable
                             GROUP BY cusotmerName
                             HAVING SUM(localAmount)<50000 
                        )

Answer 4:

这将显示有超过50000以内的客户：

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

请注意，你最好通过分组CustomerID （如果有），因为有可能是重名。 然后，你可以这样做：删除：

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

Answer 5:

这句法看起来有点怪异，但它的工程！

;with cte as
(
select sum(LocalAmount) over (partition by cname) s 
from yourtable
)
delete from cte where s <= 50000 

文章来源: group by clause query