求删资料 sql我有一个订单文件
主键是 ordid 和 version现有资料﹕Ordid version
Aa 0
Aa 1
Bb 0
Bb 1
Cc 0
Cc 1
Cc 2我现在只要版本最大的﹐删掉之前的版本最后剩﹕
Ordid version
Aa 1
Bb 1
Cc 2不過要注意 還有訂單子檔也要刪
主键是 ordid 和 version现有资料﹕Ordid version
Aa 0
Aa 1
Bb 0
Bb 1
Cc 0
Cc 1
Cc 2我现在只要版本最大的﹐删掉之前的版本最后剩﹕
Ordid version
Aa 1
Bb 1
Cc 2不過要注意 還有訂單子檔也要刪
from tablename
group by Ordid
where rowid not in(
select rowid from(
select Ordid,max(version)
from tablename
group by Ordid))
t1.Ordid=t2.Ordid and t1.version<t2.version)
select Ordid,max(version)
from tablename
group by Ordid
会有 rowid??
delete from tablename
where Ordid||version not in(
select Ordid||max(version) from tablename
group by Ordid)
DELETE FROM "訂單表"
where (ordid,version) IN (
select ordid,version from (
select ordid,version,row_number() over (partition by ordid order by version DESC) as row_Num from "訂單表") where row_Num<> 1
);
以上語句即可達到樓主要求,但一定要保証主键是ordid 和version,即同ordid值的行中,不能再有相同的version值的行,否則不會保留到唯一行,而且還可能出現錯誤,樓主可以測試一下。
delete from "訂單表" where (ordid,version) NOT IN (select ordid,max(version) from "訂單表" group by ordid);