mysql中如何多次使用from子查询形成的临时表

Question

select * from (select news.*,@num:=@num+1 as num from news,(select @num:=0) as t order by updatetime desc) as tmp where num=(select num from tmp where id=5)-1;

结果报错，tmp表不存在
谢谢诸位了

附：
创建及插入数据的sql

create table news(
    id int unsigned primary key auto_increment,
    title varchar(50) not null default '',
    inputtime int unsigned not null default 0,
    updatetime int unsigned not null default 0
)engine myisam charset utf8;

insert into news
values
(null,'第一篇文章',1240902890,1240902890),
(null,'第二篇文章',1241422082,1241422082),
(null,'第三篇文章',1241422402,1241422402),
(null,'第四篇文章',1241422518,1241422518),
(null,'第五篇文章',1241422573,1241422573),
(null,'第六篇文章',1241422785,1241422785),
(null,'第七篇文章',1241425512,1241425512),
(null,'第八篇文章',1241511871,1241511871),
(null,'第九篇文章',1241966951,1241966951);

Answer 1

后面的子查询 select num from tmp where id=5 ，tmp不存在。两个属于平级子查询，后面的的子查询不能用之前的tmp。可以把 后面的子查询条件 写在前面的子查询里。

Answer 2

可以在ng-repeat中加入过滤器，把第一个下啦作为第二个过滤条件写法上就不对，逻辑不清晰，可以把字查询和主表以id建立关联

Answer 3

mysql并不支持with as 的写法，但是可以使用子查询替代：

select tmp.*
from
(
select news.*,@num:=@num+1 as num
from news,(select @num:=0)
as t order by updatetime desc
) as tmp
where tmp.num=
-- 这句和你的原语句的where一致
(select num-1 from
(select news.*,@num_t:=@num_t+1 as num
from news,(select @num_t:=0)
as t order by updatetime desc)t where id=5)