Question

asp删除数据库里的数据保留最新的50条，怎么写

conn.execute ("delete from accmdbtab where...... )

Answer 1

主键为int或者bigint:
DELETE 表名 WHERE 主键<(SELECT MIN(主键) FROM (SELECT TOP 50 主键) FROM 表名 ORDER BY 主键) ORDER BY 主键
主键为datetime或者smalldatetime:
DELETE 表名 WHERE 时间<(SELECT MIN(时间) FROM (SELECT TOP 50 时间 ORDER BY 时间) FROM 表名 ORDER BY 时间)

not in的效率太低了.

Answer 2

我认为按时间会好点。
如果你中间删掉别的可能就造成ID的数不是前50个
delete from accmdbtab where (addtime) not in ("select top 50 [addtime] from accmdbtab order by addtime desc")
你自己改改这个

Answer 3

请问有时间或者ID做关键字段吗?

DELETE FROM accmdbtab WHERE [ID] NOT IN(SELECT TOP 50 [ID] FROM accmdbtab ORDER BY [ID] DESC)

你自己改改吧.

这个应该可以的.