今天看唐成书看到一段语句不明白什么意思
select row_number() over (partition by id),ctid from t
以前依稀记得 见过 over( partiontion by xxx) 类似的语法,但是工作中还未发现有什么sql会写不出来,以为自己用不到,居然没太关注。
不过书上别人这么写了,要是看不懂还是显得略微丢人了。
所以细细分析如下。
执行脚本
create table t1(
c1 int4
);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);
insert into t1 values(6);
insert into t1 values(7);
insert into t1 values(8);
insert into t1 values(9);
insert into t1 values(10);
insert into t1 values(1);
insert into t1 values(1);
insert into t1 values(1);
看到row_number()第一反应就是ORACLE中rownum,遂执行查询报错
那只能加上over执行
可见row_number()是一个分区后的排序,那么如何用到当成ORACLE的rownum使用呢?
可以这样?
select * from (select row_number() over (partition by 1) as rownum from t1 ) t1 where rownum =1
后来查询文档,发现一句说明 “在只有一个包含了所有行的分区情况下,也可以省略PARTITION BY。”
前半句我没看懂,但是他说可以省略PARTITION BY,原来不可以省略over但是可以省略PARTITION BY
select * from (select row_number() over () as rownum from t1 ) t1 where rownum =1
一样是没问题的,虽然写法不一样,但是对比了执行计划是一样的。
后面又测试了一些rank之类的函数。不一一分析了,测试语句贴一下好了。可以注意看执行计划;
select row_number() over (partition by t1), ctid,c1 from t1
select rank() over (),c1 from t1
select rank() over (partition by t1),c1 from t1
select row_number() over (partition by c1),rank() over (partition by 1 order by c1),c1 from t1
select row_number() over (partition by c1),rank() over (order by c1),c1 from t1 order by c1 asc