update pw_threads a inner join (select *
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.postdate<postdate))
b set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1这段语句并没有语法错误,但是没有达到只更新子查询中限定的范围的作用,把整个pw_theads的所有记录全部都更新了
请问应该怎么改一下。
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.postdate<postdate))
b set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1这段语句并没有语法错误,但是没有达到只更新子查询中限定的范围的作用,把整个pw_theads的所有记录全部都更新了
请问应该怎么改一下。
(select * from pw_threads A where 5>(select count(*) from pw_threads where A.fid=fid and A.postdate<postdate)) b
set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1
where .....
用这个时间+1就是比最新的帖子还新。把最老的帖子的最后回复时间设置成比最新的帖子还新。我先取出每个板块最老的5个帖子,进行更新。pw_threads是phpwind论坛的主贴表,里面有id是自增字段,fid是版块id,lastpost是最后回复时间,
postdate是发帖时间,似乎我个语句里面不应该有postdate,和我要实现的目的无关
select *
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.lastpost>lastpost)
order by fid 修正一下update pw_threads a inner join (select *
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.lastpost<lastpost))
b set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1但是还是更新所有表中的记录,不是只更新每个板块最老的5条
update pw_threads a inner join (select *
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.lastpost>lastpost))
b set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1 on a.tid=b.tid
这样改语法错误
from pw_threads A
where 5>(select count(*) from pw_threads where A.fid=fid and A.lastpost>lastpost))
b on a.tid=b.tid set a.lastpost=(select lastpost from (select lastpost from pw_threads order by lastpost desc limit 0,1) a)+1
这样可以了,似乎行了,请高手看看有什么可以优化一下的地方吗,
语句是不是太长了点