這篇文章主要介紹了mysql使用mysql實現(xiàn)各種排序,需要的朋友可以參考下
核心代碼
--下面我演示下MySQL中的排序列的實現(xiàn) --測試數(shù)據(jù) CREATE?TABLE?tb ( score?INT ); INSERT?tb?select? 5?UNION?ALL?SELECT? 4?UNION?ALL?SELECT? 4?UNION?ALL?SELECT? 4?UNION?ALL?SELECT? 3?UNION?ALL?SELECT? 2?UNION?ALL?SELECT 1; --1.row_number式的排序 SET?@row_number?=0; SELECT?@row_number?:=?@row_number+1?AS?row_number,score? FROM?tb? ORDER?BY?score?DESC?; +------------+-------+ |?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)?AS?decnse_rank, ??@prev_score?:=?score?AS?score? FROM?tb? ORDER?BY?score?DESC?;? +-------------+-------+ |?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+1?AS?ROW, ????@rank:=IF(@prev_score=score,@rank,@row)?AS?rank, ????@prev_score:=score?AS?score FROM?tb? ORDER?BY?score?DESC; +------+------+-------+ |?ROW?|?rank?|?score?| +------+------+-------+ |??1?|??1?|???5?| |??2?|??2?|???4?| |??3?|??2?|???4?| |??4?|??2?|???4?| |??5?|??5?|???3?| |??6?|??6?|???2?| |??7?|??7?|???1?| +------+------+-------+
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
