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| +------+------+-------+