Oracle 和 SQL Server 按条件分组排序取最后一条 及其他 OVER使用

1.排序列为唯一标识

select * from bu_upload where 
times in (select max(times) from bu_upload group by gs_no)

2.排序列非唯一标识 （server sql 2005以上也适用）

按gs_no 分组，按times 反向排序，生成行号rid ,取第一条(rid=1的)

SELECT * FROM

(

SELECT z.* ,ROW_NUMBER() OVER(PARTITION BY z.gs_no ORDER BY z.times desc) AS rid

FROM bu_upload z

)

WHERE rid =1;

 

3.更新最后一条记录

update bu_sale_detail_make set money_duty = money_duty+(v_total-v_money) where ep_code = v_ep_code and list_no = v_list_no
          and exists(select 1 from 
              (SELECT z.id ,ROW_NUMBER() OVER(ORDER BY z.times desc) AS rid FROM bu_sale_detail_make z where ep_code = v_ep_code and list_no = v_list_no)t
              where t.rid=1 and t.id=bu_sale_detail_make.id
          );

 

OVER(PARTITION BY） 其他功能

-- select * from cte_child order by gro,level
-- select c.*,row_number() OVER(PARTITION BY gro ORDER BY level desc) AS rid from cte_child c --加行号
-- select c.*,DENSE_RANK() OVER(PARTITION BY gro ORDER BY level desc) AS rid from cte_child c --排名 并列不占名次位
 select c.*,RANK() OVER(PARTITION BY gro ORDER BY level asc) AS rid from cte_child c --排名 并列占名次位
-- select c.*,NTILE(4) OVER(PARTITION BY gro ORDER BY level asc) AS rid from cte_child c --排名 将数据分成指定组数，并为每一组生成一个序号
-- SELECT c.*,COUNT(1) OVER(PARTITION BY gro) AS rid from cte_child c --分组，获取组内数据行数
-- SELECT c.*,COUNT(1) OVER() AS rid from cte_child c --不分组，获取所有数据行数 --可和OVER(PARTITION BY gro)同时使用
-- SUM,AVG(平均),MAX,MIN 同上