mysql获取排名后的名次(根据话题热度,获得话题的热度名次)

根据话题热度,获得话题的热度名次

        SELECT
        b.ranking
        FROM
        (
        SELECT
        a.id,
        a.hot,
        ( @ranknum := @ranknum + 1 ) AS ranking
        FROM
        (
        SELECT
        ct.id,
        cts.hot
        FROM
        CTS cts,
        CT ct
        WHERE
         ct.id = cts.topic_id
        ORDER BY
        cts.hot DESC
        ) AS a,
        ( SELECT ( @ranknum := 0 ) ) rank_b
        ) AS b
        WHERE
        b.id = 33

我们把它拆分成三段

1.以hot 降序排列

        SELECT
        ct.id,
        cts.hot
        FROM
        CTS cts,
        CT ct
        WHERE
         ct.id = cts.topic_id
        ORDER BY
        cts.hot DESC

结果

2.给hot 排名

        SELECT
        a.id,
        a.hot,
        ( @ranknum := @ranknum + 1 ) AS ranking
        FROM
        (
        SELECT
        ct.id,
        cts.hot
        FROM
        CTS cts,
        CT ct
        WHERE
         ct.id = cts.topic_id
        ORDER BY
        cts.hot DESC
        ) AS a,
        ( SELECT ( @ranknum := 0 ) ) rank_b

结果

3.ranking 名词已经出来了,我们就把上面结果作为一张临时表,根据id查ranking。看第一张图的代码,查询的id = 33.结果

对比2的结果图,我们已经拿到正确的id=33 ,的hot 排名为3。

其中  ( @ranknum := @ranknum + 1 ) AS ranking 就是排名时,每次递增 + 1,如果是 ( @ranknum := @ranknum + 100 ),那就是100 200 300 这样排名。

 ( SELECT ( @ranknum := 0 ) ) rank_b  意思是排名时,从0开始计数。如果是 ( SELECT ( @ranknum := 50 ) ),那就是从50开始计数,结合 ( @ranknum := @ranknum + 1 )  就是 51 52 53 54 , 是 ( @ranknum := @ranknum + 100 ) 就是  150 250  350.

 

©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页