set @rownum = 0;
select (@rownum := @rownum + 1) as rownum, name, scores from user order by scores;
利用一个变量作为计数器,就把这个rownum给模拟出来了。再对这个查询结果进行搜索就可以得到想要的了:
select rownum, name, scores from (select (@rownum := @rownum + 1) as rownum, name, scores from user order by scores) where name = ‘张三’;
这样貌似可以了。但是我的问题还没完:我们项目里的数据库类无法实现同时执行两条SQL语句。开始我试着改那个类,后来我换了个想法:能不能把这两条SQL语句给整合成一条呢?
在overflow上找到了这个问题的解答:
select rownum, name, scores from (select (@rownum := @rownum + 1) as rownum, name, scores from user u, (select @rownum := 0) r order by scores) where name = ‘张三’;