select * from (
select tablename.*,rank() over(partition by userid order by id desc) rk from
tablename) where rk=1
select tablename.*,rank() over(partition by userid order by id desc) rk from
tablename) where rk=1
select * from xxx where id in (select max(id) from xxx group by userid)
多谢,不过我测试了一下,上面的语句执行效率要高一些
rank() over(子句) 就是对子句进行排序,
partition by userid 表示根据userid进行重新排序,也可以多个字段.
order by id desc 对id字段进行降序排列.
rk 表示rank() over(partition by userid order by id desc) 的简写.
不知道你明白了吗?
你的查询会导致两次全表扫描,效率不太好吧. 附上三个查询计划,ID列建有唯一索引.
Execution Plan 2:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=3 Bytes=78)
1 0 NESTED LOOPS (Cost=11 Card=3 Bytes=78)
2 1 VIEW OF 'VW_NSO_1' (Cost=8 Card=3 Bytes=21)
3 2 SORT (UNIQUE) (Cost=8 Card=3 Bytes=36)
4 3 SORT (GROUP BY) (Cost=8 Card=3 Bytes=36)
5 4 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=6
Bytes=72) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1' (Cost=1 Card=6
Bytes=114) 7 6 INDEX (UNIQUE SCAN) OF 'PK_TEST1' (UNIQUE)
Execution Plan 3:
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=19)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=1 Bytes=19)
3 1 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'TEST1' (Cost=1 Card=2 Bytes=24)