oracle开窗函数

1.-- 使用ROW_NUMBER分页，查找第1-10条数据


SELECT T.custid,T.companyname,T.address,T.city FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY custid) AS rownum,custid,companyname,address,city FROM Sales.Customers
) AS TWHERE T.rownum BETWEEN 1 AND 10

2.默认值应用

UPDATE T_ACTIVITY SET MINGROUPCOUNT =(SELECT max(id)+1 FROM T_ACTIVITY) WHERE ID=1


直接取最大值：——也可以排名第一的就是默认值
select id, CASE WHEN Id= (max(id) over()) then '默认' ELSE '非默认' END AS sdsdf from T_BRAND


ORDER BY ID DESC


分组取默认值：——本质是分组排序


select t.pid,t.id,t.name,t.rk,CASE WHEN t.rk=1 then '默认' ELSE '非默认' END AS paixu from 


(select rank() over(partition BY pid order by id desc) rk,a.ID,a.PID,a.NAME from  T_PRODUCT a) t
　　where t.rk<=5;


where可以去掉，但是默认值不能合并，合并了代码很长。




select t.*, t.rowid,rank() over (partition by t.classid order by t.score,t.classid) from tablename t

文章来源: blog.csdn.net，作者：网奇，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/jacke121/article/details/54429515