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

巴特西