这里有新鲜出炉的 Mysql 教程,程序狗速度看过来!
MySQL 是一个开放源码的小型关联式数据库管理系统,开发者为瑞典 MySQL AB 公司。MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。
这篇文章主要介绍了 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 |
- +------+------+-------+
来源: http://www.phperz.com/article/17/0530/335553.html