Mysql模拟row_number

Mysql 模拟row_number

• 对所有结果数据标注行号

    SELECT 
        (@row_number:=@row_number + 1) AS num, firstName, lastName
    FROM
        employees,(SELECT @row_number:=0) AS t
    LIMIT 5;

注意：衍生表必须有别名

• 对分组结果数据标注行号

    SELECT 
        @row_number:=CASE
            WHEN @customer_no = customerNumber THEN @row_number + 1
            ELSE 1
        END AS num,
        @customer_no:=customerNumber as CustomerNumber,
        paymentDate,
        amount
    FROM
        payments
    ORDER BY customerNumber;

文章原址