MySQL使用變量實現各種排序詳解

--下面我演示下MySQL中的排序列的實現  --測試數據  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?|  +------+------+-------+