Mysql一种模拟row_number() over方式及理解

前言：Oracle中row_number() over分析函数，有时使用来解决问题非常方便，但最近项目用mysql处理数据需用到类似功能，找了下在mysql中没有类似的函数，此处记录使用mysql变量来模拟类似效果。
SQL语句如下：
SELECT * FROM
(SELECT
F.PARTY_ID AS PARTY_ID,
F.ALL_COUNT AS ALL_COUNT,
F.SU AS SU,
CASE
WHEN @PARTY_ID != PARTY_ID THEN
@ROWNUM := 1
ELSE
@ROWNUM :[email protected] + 1
END AS ROWNUM,
@PARTY_ID := PARTY_ID AS PARTY_ID12
FROM
(SELECT @F.PARTY_ID := “”) c,
(SELECT @ROWNUM := 0) r,
(
SELECT
E.PARTY_ID AS PARTY_ID,
E.SU AS SU,
E.ALL_COUNT AS ALL_COUNT
FROM
(
SELECT
c.PARTY_ID AS PARTY_ID,
COUNT(*) AS SU,
d.AMT AS ALL_COUNT
FROM
dpf_ckaccno_dtl d
JOIN dpf_ckaccno c ON d.ACC_NO = c.DM01ACCT
WHERE
d.CRDFLG = ‘D’
GROUP BY
c.PARTY_ID,
d.AMT
) E
ORDER BY
E.PARTY_ID,
E.SU DESC,
E.ALL_COUNT DESC
) F ) G WHERE G.ROWNUM<=3
说明：1、（SELECT @ROWNUM := 0）r 用于定义变量并初始化，后面是命别名，此处不能省略，不然会报错。
2、 @PARTY_ID := PARTY_ID AS PARTY_ID12 此处是把当前行值赋值给PARTY_ID 变量，前面(SELECT @F.PARTY_ID := “”) c，已经将其初始化为空串
3、CASE
WHEN @PARTY_ID != PARTY_ID THEN
@ROWNUM := 1
ELSE
@ROWNUM :[email protected] + 1
END AS ROWNUM, – 此处通过case when 条件句来判断变量值与当前PARTY_ID是否相等来，不等说明是新的PARTY_ID，ROWNUM 就为1，否则就+1
4、通过这个sql，我们可以发现mysql在sql语句中使用变量时，初始化的东西放到from语句的后面，使变量的变化语句要放到查询结果里面，这里的原因可能是跟数据库管理系统解析一个sql的顺序有关系吧（猜的！！！）
结果如下：

参考：mysql中实现row_number() over(http://www.zhimengzhe.com/shujuku/MySQL/117054.html)