SQL 组内排序

SELECT
    t_time,
    code,
    name,
    CL,
    row_number () OVER (partition BY t_time ORDER BY cl) AS 组内排名1,
--T_time组内，cl排名
    row_number () OVER (ORDER BY cl) AS 排名1_1,
--所有cl的排名
    rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名2,
--T_time组内，cl排名
    rank () OVER (ORDER BY cl) AS 排名2_1,
--所有cl的排名
    dense_rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名3,
--T_time组内，cl排名
    dense_rank () OVER (ORDER BY cl) AS 排名3_1
--所有cl的排名

FROM
    zsh_0220
ORDER BY
    t_time,code;
解释：按t_time分组（示例将t_time分为2005,2006二个组）， cl排序（默认：升序）。降序可设置ORDER BY cl desc
函数ROW_NUMBER() OVER ()  ,RANK() OVER ()RANK(),DENSE_RANK() OVER ()的区别在这就不详细介绍了，可参考：开窗函数-排名
row_number() over(partition by … order by …) 
rank() over(partition by … order by …) 
dense_rank() over(partition by … order by …) 
count() over(partition by … order by …) 
max() over(partition by … order by …) 
min() over(partition by … order by …) 
sum() over(partition by … order by …) 
avg() over(partition by … order by …) 
first_value() over(partition by … order by …) 
last_value() over(partition by … order by …) 
lag() over(partition by … order by …) 
lead() over(partition by … order by …) 
等等…有兴趣的朋友可以试试，这里就不详解了！
原文地址:http://help.finereport.com/doc-view-1763.html