欢迎使用CSDN-markdown编辑器

mysql 没有像sqlServer 和 oracle 的 类似 rownum 函数，那么我们如何获得行号呢，以下
三种方式可实现类似sqlServer 和 oracle行号，具体如下

– 方法1
SET @x = 0;
SELECT @x:=@x+1 rownum , st2.* from student2 st2 ORDER BY st2.name;

– 方法2
SELECT @x:=@x+1 rownum, st2.* from student2 st2 ,(SELECT @x:=0) ;

– 方法3
select st2.*,(@x:=@x+1) as rownum from student2 st2,(select (@x:=0))b ;

准备工作如下：
/*
Navicat MySQL Data Transfer

Source Server : mytest
Source Server Version : 50614
Source Host : localhost:3306
Source Database : test

Target Server Type : MYSQL
Target Server Version : 50614
File Encoding : 65001

Date: 2015-09-22 10:10:43
*/

SET FOREIGN_KEY_CHECKS=0;

---

– Table structure for student2

---

DROP TABLE IF EXISTS student2;
CREATE TABLE student2 (
id int(10) NOT NULL,
name varchar(20) NOT NULL,
age int(4) DEFAULT NULL,
class int(4) DEFAULT NULL,

PRIMARY KEY (id),
UNIQUE KEY id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

– Records of student

---

INSERT INTO student2 VALUES (‘1’, ‘张1’, ‘15’, ‘1’);
INSERT INTO student2 VALUES (‘2’, ‘张2’, ‘15’, ‘1’);
INSERT INTO student2 VALUES (‘3’, ‘张3’, ‘15’, ‘1’);
INSERT INTO student2 VALUES (‘4’, ‘李4’, ‘15’, ‘2’);
INSERT INTO student2 VALUES (‘5’, ‘王五’, ‘15’, ‘2’);
INSERT INTO student2 VALUES (‘6’, ‘王六’, ‘15’, ‘2’);
INSERT INTO student2 VALUES (‘7’, ‘张7’, ‘15’, ‘3’);
INSERT INTO student2 VALUES (‘8’, ‘张8’, ‘15’, ‘3’);
INSERT INTO student2 VALUES (‘9’, ‘张9’, ‘15’, ‘3’);
INSERT INTO student2 VALUES (‘10’, ‘李10’, ‘15’, ‘5’);
INSERT INTO student2 VALUES (‘11’, ‘王11’, ‘15’, ‘5’);