Mysql查询显示行号,实现类似Oracle数据库的
ROWNUM()
在mysql数据库中没有ROWNUM()这个函数
,只能通过变量的方式来实现.
需求:查询部门是技术开发,并按工资由低到高排序取第6条数据
create table employee(
name varchar(20),
dept varchar(20),
salary int
)
insert into employee values('李白','技术开发部',8000);
insert into employee values('赵云','技术开发部',9000);
insert into employee values('曹操','技术开发部',11000);
insert into employee values('关羽','技术开发部',13000);
insert into employee values('刘备','技术开发部',20000);
insert into employee values('小乔','技术开发部',25000);
insert into employee values('孙策','策划开发部',8500);
insert into employee values('马超','技术开发部',9600);
insert into employee values('张飞','需求开发部',12000);
insert into employee values('大乔','技术开发部',14000);
insert into employee values('典韦','需求开发部',21000);
insert into employee values('吕布','测试开发部',22000);
实现
-- @rownum 为自定义会话变量
-- @rownum := 0 确定初始值
-- @rownum := @rownum +1 步长
select *from
(select @rownum:=@rownum+1 as rownum,e.*
from employee e,(select @rownum:=0)t
where e.dept='技术开发部'
order by salary asc)c
where c.rownum=6
先排序
然后取出第六条