-- 按金额降序,RANK排序
SELECT
T.SALES_OFFICE_CODE
,T.A_TNOR
,T.A_RANK
,@ROWNUM := @ROWNUM + 1 AS ROWNUM -- 行号初始化0,查询第一条语句的时候循环+1
,@rank := (case when @PA = T.A_TNOR then @rank else @ROWNUM end) -- 判断当前数据和上一条数据是否一致,如果一致给予上条记录排名,给予行号
,@PA := T.A_TNOR
FROM
(SELECT
SALES_OFFICE_CODE
,A_TNOR
,A_RANK
FROM
BA_MEMBER_SO_DAY_F
WHERE
P_DAY = 20161116 -- and A_TNOR = 0
ORDER BY A_TNOR DESC) T
,(SELECT
@RANK := 0
,@ROWNUM := 0
,@PA := NULL) TT -- 定义变量
;