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 送给学长毕业祝福语简短