delete 符合条件的记录中的前几条或者重复记录

今天写代码,遇到了这个问题,只能删除符合条件的记录中的某几条.

在网上查了一下,结合自己的经验. 

做法如下:

1. 取出符合条件的records;
2. 排序;
3. delete 前面n条 或者 指定的第n条.

sql server:

--删除前3行
select top(3) * from dbo.t1
delete top(3) from dbo.t1

--删除重复记录
declare @count int
select @count =count(*) from dbo.t1 where name = 'guoqiang'
delete top(@count-1) from  dbo.t1 where name = 'guoqiang'

 

MySQL:
DELETE语句包括一个ORDER BY子句，则各行按照子句中指定的顺序进行删除。此子句只在与LIMIT联用是才起作用。下面的子句用于查找与WHERE子句对应的行，使用timestamp_column进行分类，并删除第一（最旧的）行。

 

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp_column
LIMIT 1;

 

新增 mysq删除某些重复值的记录。

delete b from mem_world_building as b, (select *,min(sbid) as tbid from mem_world_building group by wid,bid having count(sbid) >1) as t
where  b.wid = t.wid and b.sbid = t.tbid and b.bid = t.bid

 

其他：

Delete From Table Where ID in ( Select Top N ID From Table Order By ID)