MySQL实现rownum

MySQL实现rownum

CREATE TABLE T_TEST_AREA(
	ID INT,
	PID INT,
	NAME VARCHAR(20),
	DELETED INT,
	CHECKED INT
);

DELETE FROM T_TEST_AREA WHERE 1=1;
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(1, -1, '长沙市', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(2, -1, '深圳市', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(3, 1, '开福区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(4, 1, '雨花区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(5, 1, '岳麓区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(6, 2, '宝安区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(7, 2, '南山区', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(8, 2, '福田区', 0, 0);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(9, 4, '黎托街道', 0, 1);
INSERT INTO T_TEST_AREA(ID, PID, NAME, DELETED, CHECKED) VALUES(10, 4, '东山街道', 0, 1);

方法一：自定义变量

-- @rownum 为自定义会话变量
-- @rownum := 0 确定初始值
-- @rownum := @rownum +1 步长
SELECT @rownum := @rownum +1 rownum, a.* 
FROM T_TEST_AREA a, (select @rownum := 0) t 
ORDER BY id desc;
-- 注意：在hibernate中：有代表占位符的意思，故应在：=两端加上/*'*/，否则会报错

方法二：自增函数

-- 普通查询用以上方式是可以满足的，但如果以此作为视图则会报错
-- SQL 错误 [1351] [HY000]: View's SELECT contains a variable or parameter
-- 在视图需要序号时，则需创建函数来实现rownum
delimiter $$
drop function if exists func_row_num $$
create function func_row_num(reset bit) returns int(11)
begin
  	if reset then
    	set @row_num := 0;
    else
    	set @row_num := ifnull(@row_num, 0) + 1;
    end if;
    return  @row_num;
end $$
delimiter ;

-- 原理类似方法一，func_row_num(1)重置
select func_row_num(0) rownum, a.*
FROM T_TEST_AREA a, (select func_row_num(1)) t 
ORDER BY id desc;