id carno jgsj 1 京AAAA 2008-01-01 22:23:01
2 京EEDE 2008-01-21 22:54:21
3 京AAAA 2008-01-01 22:23:01
4 京TREE 2008-01-07 11:43:44我要得到的结果是 id carno jgsj 1 京AAAA 2008-01-01 22:23:01
2 京EEDE 2008-01-21 22:54:21
4 京TREE 2008-01-07 11:43:44我要删除表中所有carno项和jgsj项一样的数据,只留一条
请问要怎么写???????谢谢 急!!!!!!!!!!
2 京EEDE 2008-01-21 22:54:21
3 京AAAA 2008-01-01 22:23:01
4 京TREE 2008-01-07 11:43:44我要得到的结果是 id carno jgsj 1 京AAAA 2008-01-01 22:23:01
2 京EEDE 2008-01-21 22:54:21
4 京TREE 2008-01-07 11:43:44我要删除表中所有carno项和jgsj项一样的数据,只留一条
请问要怎么写???????谢谢 急!!!!!!!!!!
select min(id),carno,jgsj from table group by carno,jgsj;
delete from t where id < all (select max(id) from t group by carno, jgsj);没必要导出数据再弄。注意,确保id是表的主键。
那个all是什么意思????
where id < (select max(id) from table_name b
where b.carno = a.carno and b.jgsj = a.jgsj);
where rowid !=(
select max(rowid)
from table t
where table.id = t.id
)oracle中 rowid
from Table a
where not exists(select * from Table b where
b.carno=a.carno and b.jgsj>a.jgsj)
你那数据库的记录怎么可能要删除掉?
执行上面的就可以了~
明显显示的数据是一个交集~
where id <
(select max(id) from t group by carno, jgsj);
delete from TableName where id not in (select min(id) from TableName group by carno,jgsj)
按楼主的例子,id 1,3是重复的,id<3,id>1,id not in (1),诸如此类都会删掉其它记录。
在mysql中这样做 它一直抱 “you can't not specify target for update 't' from clause”
怎么办
如果重複的紀錄不多的話一條條刪得了 delete * from table where id=3
最後建議建立一個組合主鍵(carno,jgsj)這樣以後就不會有重複的數據出現了
如果重複的紀錄不多的話一條條刪得了 delete * from table where id=3
最後建議建立一個組合主鍵(carno,jgsj)這樣以後就不會有重複的數據出現了
where rowid in (select max(rowid) from tablename table2
where table1.carno=table2.carnoand
table1.jgsj=e2.jgsj );
delete from t where id not in all (select max(id) from t group by carno, jgsj);
整理一下思路:以id值作为查询条件,凡是carno值相同的id,则把大的id删除
delete from tablename t1
where rowid in (select t2.rowid from tablename t2
where t1.carno=t2.carno and t1.jgsj=t2.jgsj and t2.rowid>t1.rowid)