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| +------+------+-------+
热门推荐
10 八一幼儿祝福语大全简短
11 公司乔迁食堂祝福语简短
12 婚礼结束聚餐祝福语简短
13 儿媳买车妈妈祝福语简短
14 毕业送礼老师祝福语简短
15 同事辞职正常祝福语简短
16 恭贺新婚文案祝福语简短
17 金店立秋祝福语简短英文
18 婆婆高寿祝福语大全简短