表一：
保单号 保费
110001 3000.00
110002 -2000.00
110003 2000.00
110004 -2000.00
110004 2000.00
110004 -2000.00
110004 2000.00
110005 2000.00
110005 1000.00
110006 -1000.00
110006 1000.00
按保单号汇总保费，如果汇总的保费为零，则删除相应的分类行，得到下列结果
表二
保单号 保费
110001 3000.00
110002 -2000.00
110003 2000.00
110005 2000.00
110005 1000.00
请问大神们，这个SQL语句该怎么写？
非常感谢！！！
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2014-03-15 09:12:25
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([保单号] int,[保费] numeric(6,2))
insert [tb]
select 110001,3000.00 union all
select 110002,-2000.00 union all
select 110003,2000.00 union all
select 110004,-2000.00 union all
select 110004,2000.00 union all
select 110004,-2000.00 union all
select 110004,2000.00 union all
select 110005,2000.00 union all
select 110005,1000.00 union all
select 110006,-1000.00 union all
select 110006,1000.00
--------------开始查询--------------------------
;WITH f AS
(
SELECT 保单号,SUM(保费) AS num FROM tb GROUP BY 保单号
)
DELETE t FROM TB T WHERE EXISTS(SELECT 1 FROM f WHERE 保单号=t.保单号 AND num=0)
SELECT * FROM tb
----------------结果----------------------------
/* 保单号 保费
----------- ---------------------------------------
110001 3000.00
110002 -2000.00
110003 2000.00
110005 2000.00
110005 1000.00
*/
------解决方案--------------------
create table 表一
(保单号 varchar(10),保费 decimal(10,2))
insert into 表一
select '110001',3000.00 union all
select '110002',-2000.00 union all
select '110003',2000.00 union all
select '110004',-2000.00 union all
select '110004',2000.00 union all
select '110004',-2000.00 union all
select '110004',2000.00 union all
select '110005',2000.00 union all
select '110005',1000.00 union all
select '110006',-1000.00 union all
select '110006',1000.00
-- 删除
delete a
from 表一 a
inner join
(select 保单号,sum(保费) 's'
from 表一
group by 保单号) b on a.保单号=b.保单号
where b.s=0
-- 结果
select * from 表一
/*
保单号 保费
---------- ---------------------------------------
110001 3000.00
110002 -2000.00
110003 2000.00
110005 2000.00
110005 1000.00
(5 row(s) affected)
*/