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| +------+------+-------+
热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下