Mysql动态row_number生成问题

Question

我尝试根据web上的引用为MySQL创建行号。

查询如下:

存在审计id不相同的情况，需要将row_number变量加1。否则，row_number相同[逐行递增]。
然而，由于在having; condition{[row_number>3]}中有一个引用，row_number再次被增加。

所以，我得到奇数作为row_number的结果。请帮助。
这意味着下面查询的结果有5,7,9,等，作为row_number而不是4,5,6,等，

注意:此处AUDIT_ID列为大整数。

SELECT  
 @row_number:=CASE WHEN @audit_id = AUDIT_ID then @row_number
    else
@row_number+1 
end AS row_number,
@audit_id:=AUDIT_ID as AUDIT_ID from 
(
SELECT AUDIT_ID from AUDIT where <somecondition>
UNION ALL
SELECT AUDIT_ID from AUDIT where <somecondition>
)
TABLE ,(SELECT @row_number:=0, @audit_id:=0) AS temp 
   having row_number >3

Answer 1

首先需要解决AUDIT_ID列的顺序必须下令为了正确row_number我添加了ORDER BY AUDIT_ID联盟这部分你就使用别名row_number having条款将重新运行row_number/评估定义的表达式(即当…)别名因此row_number不一样你期望的过滤结果小于3你需要包装你整个查询另一个接头选择像select * from (your query) t1 where some conditon

SELECT *
FROM (
    SELECT  
        @row_number:=CASE WHEN @audit_id = AUDIT_ID
        THEN @row_number
        ELSE @row_number+1 
        END AS row_number,
        @audit_id:=AUDIT_ID AS AUDIT_ID
     FROM (
        SELECT AUDIT_ID FROM AUDIT WHERE <somecondition>
        UNION ALL
        SELECT AUDIT_ID FROM AUDIT WHERE <somecondition>
        ORDER BY AUDIT_ID
        ) t,
       (SELECT @row_number:=0, @audit_id:=0) AS temp 
) t1
WHERE row_number >3