在mysql/ target=_blank class=infotextkey>mysql数据库查询结果中显示行号row_number。
1，创建表employee
复制代码 代码示例:
mysql> create table employee (id smallint(5), name varchar(15),salary smallint(5));
2，插入测试数据
复制代码 代码示例:
mysql> Insert into employee values (1,’jon’,1000); mysql> Insert into employee values (2,’darvin’,3500);
mysql> Insert into employee values (3,’krik’,500);
mysql> Insert into employee values (4,’rook’,1500);
mysql> Insert into employee values (5,’alex’,100);
3，显示表中数据
复制代码 代码示例:
mysql> select * from employee;
+------+--------+--------+
| id | name | salary |
+------+--------+--------+
| 1 | jon | 1000 |
| 2 | darvin | 3500 |
| 3 | krik | 500 |
| 4 | rook | 1500 |
| 5 | alex | 100 |
+------+--------+--------+
5 rows in set (0.00 sec)
4、按薪水排序后显示结果
复制代码 代码示例:
mysql> select * from employee order by salary;
+------+--------+--------+
| id | name | salary |
+------+--------+--------+
| 5 | alex | 100 |
| 3 | krik | 500 |
| 1 | jon | 1000 |
| 4 | rook | 1500 |
| 2 | darvin | 3500 |
+------+--------+--------+
5 rows in set (0.00 sec)
5、别名显示row_number
复制代码 代码示例:
mysql> set @row_num = 0; SELECT @row_num := @row_num + 1 as row_number,id,name,salary FROM employee
ORDER BY salary;
+------------+------+--------+--------+
| row_number | id | name | salary |
+------------+------+--------+--------+
| 1 | 5 | alex | 100 |
| 2 | 3 | krik | 500 |
| 3 | 1 | jon | 1000 |
| 4 | 4 | rook | 1500 |
| 5 | 2 | darvin | 3500 |
+------------+------+--------+--------+
5 rows in set (0.00 sec)
从以上结果可以看出row_number列显示在结果中了。
来看下获得行号的操作步骤：
复制代码 代码示例:
set @row_num = 0;
使用@row_num用户自定义变量，保存查询结果，默认值设置为0.
复制代码 代码示例:
SELECT @row_num := @row_num + 1 as row_number
然后， 每一次查杀时row_num 加1，计算完成后 @row_num 保存了所有员工薪水求和，然后用row_number用作别名来使用。
