mysql的虚拟表编号技巧

2014年更新,成功实现用自增值实现临时表

由于mysql不像oracle这样支持dual虚拟表connect by,在需要用到虚拟表时纠结了好些时间,我在这里记录一下最后的解决办法,由于我之前是完全不了解mysql的编程的,所以以下方法也是自己笨拙的探索而已,如有更好的方法,望指点。

//自增序号:(tablez需要一个存在且量大的表来帮助建立临时表)
select @x:=if((@x:=ifnull(@x,0))+1>24,1,@x+1) as thehour from tablez limit 0,24;

返回一个值从0到24的表

Oracle:

select level from dual where level between 0 and 24 connect by level<=24;

舍弃的笨笨MySQL:

select 0 hour union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 union all select 15 union all select 16 union all select 17 union all select 18 union all select 19 union all select 20 union all select 21 union all select 22 union all select 23 union all select 24
若您觉得我的博文对您有帮助,欢迎点击下方按钮对我打赏
打赏