一、建表
CREATE TABLE [dbo].[Table_del](
[id] [int] IDENTITY(1,1) NOT NULL,
[tcode] [varchar](20) NULL,
[tmemo] [varchar](20) NULL,
[tvalue] [numeric](18, 2) NULL
) ON [PRIMARY]
二、测试数据
insert into Table_del(tcode, tmemo, tvalue) values('1001','a',0)
insert into Table_del(tcode, tmemo, tvalue) values('1001','b',1)
insert into Table_del(tcode, tmemo, tvalue) values('1001','c',0)
insert into Table_del(tcode, tmemo, tvalue) values('1002','a',0)
insert into Table_del(tcode, tmemo, tvalue) values('1002','b',0)
insert into Table_del(tcode, tmemo, tvalue) values('1002','c',0)
三、求一个sql删除后面三行。
删除条件是tcode值相同，tvalue=0.
------解决方案--------------------
delete from Table_del where tcode in(select tcode from(select tcode,tvalue=max(tvalue) from Table_del group by tcode)tt where tvalue=0)