delete from t1 where (c1,c4) not in select c1,max(c4) from t1 group by c1;
good.delete from t1 where (c1,c4) not in (select c1,max(c4) from t1 group by c1);
select * from t1 a where a.c4=(select max(c4) from t1 where c1=a.c1)
delete from t1 where (c1,c4) not in select c1,max(c4) from t1 group by c1;
比如我有表t1 c1 c2 c4 ------------------- aa bb 9 aa bb 9 aa bc 3 bb bb 5 bb bc 3 我想得到c1列不重复的记录(举例:c1列为aa的记录有两条,我想取一条,并且c4列的值最大的一条) 查讯结果如下 c1 c2 c4 ------------------- aa bb 9 bb bb 5 像這種情況下,當有兩條相等的情況下,我如何隨機取一條呢? 如果按照以上各位的方法,會出現重復的情況.
delete from t1 where rowid in (select rid from (select rowid as rid, row_number()over(partition by c1 order by c4 desc) as rn from t1 ) where rid >=2)
方法比较多 楼上的需要改动一下: delete from t1 where rowid in (select rid from (select rowid as rid, row_number()over(partition by c1 order by c4 desc) as rn from t1 ) where rn >=2) ================================================= select * from (select rowid as rid, row_number()over(partition by c1 order by c4 desc),t.* as rn from t1 t) where rn=1;
to: xiaoxiao1984(笨猫儿^_^) 笨猫对‘over(partition by ..’情有独钟啊! 但写差了一点: where rid >=2 --->where rn >=2
where a.c4=(select max(c4) from t1 where c1=a.c1)
c1 c2 c4
-------------------
aa bb 9
aa bb 9
aa bc 3
bb bb 5
bb bc 3
我想得到c1列不重复的记录(举例:c1列为aa的记录有两条,我想取一条,并且c4列的值最大的一条)
查讯结果如下
c1 c2 c4
-------------------
aa bb 9
bb bb 5
像這種情況下,當有兩條相等的情況下,我如何隨機取一條呢?
如果按照以上各位的方法,會出現重復的情況.
(select rid from
(select rowid as rid, row_number()over(partition by c1 order by c4 desc) as rn from t1 )
where rid >=2)
楼上的需要改动一下:
delete from t1 where rowid in
(select rid from
(select rowid as rid, row_number()over(partition by c1 order by c4 desc) as rn from t1 )
where rn >=2)
=================================================
select * from
(select rowid as rid, row_number()over(partition by c1 order by c4 desc),t.* as rn from t1 t)
where rn=1;
xiaoxiao1984(笨猫儿^_^) 笨猫对‘over(partition by ..’情有独钟啊!
但写差了一点:
where rid >=2
--->where rn >=2
另外wiler (52)的语句,当出现重复c1,c4的记录时并不能消除。