轉(zhuǎn)載自：http://qincidong.github.io/blog/2015/02/06/mysql-get-rownum.html
MYSQL目前不支持行號功能，如果想按某字段進行排序，然后得到排序號，很麻煩，要想實現(xiàn)這種功能，網(wǎng)上的答案五花八門，經(jīng)過幾次實驗，得出如下一條SQL文就能簡單實現(xiàn)此功能，現(xiàn)共享一下。
表 a：
UID Money
2 444
1 222
3 555
4 6666
想要以Money排序取得排行號：SQL文如下：
SelectUID,(@rowNum:=@rowNum+1)asrowNo
Froma,
(Select(@rowNum:=0))b
Orderbya.MoneyDesc
輸入結(jié)果如下：
UID rowNo
4 1
3 2
2 3
1 4
轉(zhuǎn)載：http://www.cnblogs.com/xinlei/archive/2011/12/16/2290349.html
hibernate下獲取mysql表中的rownum所遇bug
在項目中，想要獲取mysql的行號，好不容易進行查找進行轉(zhuǎn)換可以得到行號，語句類似于
SELECT
(@rownum:=@rownum+1)AS rownum,
t.*
FROM
t_gls_familypic_record t,
(SELECT(@rownum:=0))AS s;
在mysql(5.X版本)中的sql編輯器中可以運行通過，但是在java程序中卻拋出異常:org.hibernate.QueryException: Space is not allowed after parameter prefix ':' ....
在網(wǎng)上 查找了資料，卻發(fā)現(xiàn)這是hibernate3.X包之下的一個bug,(參照 id=41741)在hibernate4.X中已經(jīng)修復(fù)。但是項目中不可能使用hibernate4.0，最后不能不使用原生jdbc進行解決...
轉(zhuǎn)載：http://www.educity.cn/wenda/404196.html
解決方法
1)在sql中將:=的:改成其他符號，比如|。然后在hibernate攔截器中將|替換成:。原文如下：
you can implement this is a slightly different way.. you need to replace the : operator with something else (say '|' char ) and in your interceptor replace the '|' with the : .
this way hibernate will not try to think the : is a param but will ignore it
For the interceptor logic you can refer to the hibernate manual
This has worked for me using MySQL 5.
remember, this replacing of : must be only done to ':=' and other MySQL specific requirments.. don't try to replace the : for the param-placeholders. (hibernate will not be able to identify the params then)
2)在:=前后加上/'/，因為加上后，hibernate會認(rèn)為它是一個字符串，就不會解析了。解釋的不好，看原文：
Another solution for those of us who can't make the jump to Hibernate 4.1.3. Simply use /'/:=/'/ inside the query. Hibernate code treats everything between ' as a string (ignores it). MySQL on the other hand will ignore everything inside a blockquote and will evaluate the whole expression to an assignement operator. I know it's quick and dirty, but it get's the job done without stored procedures, interceptors etc.
我目前使用的是第2種方式。 我的需求是計算排名，所以需要使用到行號，但是MySQL又沒有提供rownum這樣的東西。所以通過下面的方式實現(xiàn)：
SELECT
(@rownum:=@rownum+1)AS rownum,
t.*
FROM
t_gls_familypic_record t,
(SELECT(@rownum:=0))AS s;
所以我最終的sql是這樣的：
Stringsql="select * from ("
+"select (@rowNum/*'*/:=/*'*/@rowNum+1) as rowNo,t.* from ("
+"select a.NICKNAME,a.PHOTOURL,a.MEMBERID,b.voteCount from t_member a,("
+"select memberid, count(*) voteCount from t_gls_familypic_record where glshbactivityid =:activityid group by memberid) b "
+"where a.MEMBERID = b.memberid order by voteCount desc) t,(Select (@rowNum/*'*/:=/*'*/0) ) f) s where s.memberid!=:memberid";
SQLQueryquery=sessionFactory.getCurrentSession().createSQLQuery(sql);
query.setParameter("activityid",activityid);
query.setParameter("memberid",memberid);
returnquery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).setFirstResult(currentIndex).setMaxResults(maxResult).list();
