查询结果排名——并列增加法

SELECT
    @row_index := @row_index + 1 AS row_index,
    @row_num := CASE
WHEN @row_percent = t1.percent THEN
    @row_num
WHEN @row_percent := t1.percent THEN
    @row_index
ELSE
    @row_index
END AS rank,
 t1.*
FROM
    (
        SELECT
            FORMAT(

                IF (
                    finsh_event / total_event,
                    finsh_event / total_event,
                    0
                ),
                2
            ) AS percent,
            a.town_id
        FROM
            (
                SELECT
                    t.town_id,
                    COUNT(e.id) AS total_event
                FROM
                    lyhz_river r
                LEFT JOIN lyhz_event e ON r.river_id = e.river_id
                RIGHT JOIN lyhz_town t ON t.town_id = r.town_id
                AND r.river_type IN (2, 3)
                AND e.del_flag = 0
                GROUP BY
                    t.town_id
            ) AS a
        JOIN (
            SELECT
                t.town_id,
                COUNT(e.id) AS finsh_event
            FROM
                lyhz_river r
            LEFT JOIN lyhz_event e ON r.river_id = e.river_id
            AND e. STATUS IN (4, 5, 7)
            RIGHT JOIN lyhz_town t ON t.town_id = r.town_id
            AND r.river_type IN (2, 3)
            AND e.del_flag = 0
            GROUP BY
                t.town_id
        ) AS b ON a.town_id = b.town_id
        ORDER BY
            percent DESC
    ) t1,
    (
        SELECT
            @row_num := 1 ,@row_percent IS NULL ,@row_index := 0
    ) t2

其中需要注意的是：
1.case when else end 常用语法
2.when 的比较参数值为NULL的时候，需要单独判断其XXX is null or XXX = 0
3.mysql中@XXX表示变量，:= 表示赋值，同时赋值为NULL时用 is

运行结果如下

1531808929(1).jpg