MySQL使用变量实现各种排序
核心代码
--下面我演示下MySQL中的排序列的实现 --测试数据 CREATETABLEtb ( scoreINT ); INSERTtbSELECT 5UNIONALLSELECT 4UNIONALLSELECT 4UNIONALLSELECT 4UNIONALLSELECT 3UNIONALLSELECT 2UNIONALLSELECT 1; --1.row_number式的排序 SET@row_number=0; SELECT@row_number:=@row_number+1ASrow_number,score FROMtb ORDERBYscoreDESC; +------------+-------+ |row_number|score| +------------+-------+ |1|5| |2|4| |3|4| |4|4| |5|3| |6|2| |7|1| +------------+-------+ --2.dense_rank式的排序 SET@dense_rank=0,@prev_score=NULL; SELECT@dense_rank:=IF(@prev_score=score,@dense_rank,@dense_rank+1)ASdecnse_rank, @prev_score:=scoreASscore FROMtb ORDERBYscoreDESC; +-------------+-------+ |decnse_rank|score| +-------------+-------+ |1|5| |2|4| |2|4| |2|4| |3|3| |4|2| |5|1| +-------------+-------+ --3.rank式的排序 SET@row=0,@rank=0,@prev_score=NULL; SELECT@row:=@row+1ASROW, @rank:=IF(@prev_score=score,@rank,@row)ASrank, @prev_score:=scoreASscore FROMtb ORDERBYscoreDESC; +------+------+-------+ |ROW|rank|score| +------+------+-------+ |1|1|5| |2|2|4| |3|2|4| |4|2|4| |5|5|3| |6|6|2| |7|7|1| +------+------+-------+
热门推荐
3 祝福语简短的微信
10 幼儿祝福语大全简短贺卡
11 感谢教官文案祝福语简短
12 鼠年同事祝福语简短创意
13 元旦祝福语 简短独特群发
14 妈咪跟我生日祝福语简短
15 给儿子新婚祝福语简短
16 伴娘祝福语长文案简短
17 恭喜高考完祝福语简短
18 生日祝福语送老公简短