一、建表
CREATE TABLE [dbo].[Table_del](
[id] [int] IDENTITY(1,1) NOT NULL,
[tcode] [varchar](20) NULL,
[tyear] [varchar](20) NULL,
[tmon] [varchar](10) NULL,
[tmmemo] [varchar](10) NULL,
[tvalue] [numeric](18, 2) NULL
) ON [PRIMARY]
二、测试数据
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1001','2012','12','a',0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1001','2012','12','b',-1)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1001','2012','12','c',1)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1002','2012','12','a',0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1002','2012','12','b',0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1002','2012','12','c',0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1003','2013','01','a'0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1003','2013','01','b'2)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1003','2013','01','c'-1)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1004','2013','01','a'0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1004','2013','01','b'0)
insert into Table_del(tcode, tyear,tmon,tmmemo,tvalue) values('1004','2013','01','c'0)
...............
三、求一个sql删除语句。（删除条件：tcode, tyear,tmon相同，三行的tvalue均为0.）
------解决方案--------------------
delete table_del where tcode in(
select tcode from table_del
where tvalue=0
group by tcode
having COUNT(1)=3)
------解决方案--------------------
delete table_del where exists(
select 1 from table_del as a
where a.tvalue=0 and table_del.tcode=a.tcode
and table_del.tyear=a.tyear and table_del.tmon=a.tmon
group by tcode,tyear,tmon
having COUNT(1)=3)