相同的SQL语句，且不一样的输出结果

Question

执行相同的SQL语句，结果不一样。
第一次执行SQL，会统计出所以的行数，并且row_number都为1.
第二次执行SQ L，输出正确的结果。
select * from
(
select *,(@num2 :=(if(@GROUP199=C_ISHOT,@num2+1,if(@GROUP199:=C_ISHOT,1,1)))) row_number
from city_code order by C_ISHOT ) result
where row_number<=10

相关问题：
https://segmentfault.com/q/1010000014649728

Answer 1

# 是想按 `C_ISHOT` 分组吗？

SELECT
    t2.*
FROM
(
    SELECT
      IF(t1.`C_ISHOT` = @gid, @rownum := @rownum + 1, @rownum := 1) AS tmp_rownum, @gid := `C_ISHOT` AS tmp_gid, t1.*
    FROM
        (SELECT * FROM `city_code` ORDER BY `C_ISHOT` DESC) t1,
        (SELECT @gid := NULL, @rownum := 1) tmp
) t2
WHERE
    t2.tmp_rownum <= 10;

---

P.S. 为啥要用临时变量……

建议用这种方式：https://blog.csdn.net/junzi52...