根据话题热度,获得话题的热度名次
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.