Question

请教：怎样删除asp表格重复数据

表格vtime有字段id,title，time都不是唯一的，现在我想把id一样time不一样的删除掉，只留下time最大值的一个，请问我该怎么用sql语句呢？请高手指点一下。我的语句不知道会不会错，请大家看看：
<!--#include file=conn.asp-->
<%
Application.Lock()
sql="Delete from vtime Where Time Not In (Select Max(Time) From vtime Group By ID)"
set rs=conn.execute(sql)
response.write "删除完毕！<br>"
rs.close
Application.Unlock()
%>
delete from vtime where cast(id as varchar(10))+' | '+convert(varchar(25),time,120) not in (select cast(id as varchar(10))+' | '+convert(varchar(25),maxtime,120) from (select id,max(time) as maxtime from vtime group by id) a)

执行到中间就挺了。还是不行。我用的是access数据库。

Answer 1

最好先备份一下数据表，以免sql有误，造成误删除。

SqlServer的sql语句：

delete from vtime where cast(id as varchar(10))+' | '+convert(varchar(25),time,120) not in (select cast(id as varchar(10))+' | '+convert(varchar(25),maxtime,120) from (select id,max(time) as maxtime from vtime group by id) a)

看看是否能达到你的要求。
-------------------
请楼主下次提问题时先说明数据库。

access的对应语句如下：

delete from vtime where cstr(id)+' | '+cstr(time) not in (select cstr(id)+' | '+cstr(maxtime) from (select id,max(time) as maxtime from vtime group by id) a)