前面两个帖子
http://community.csdn.net/Expert/topic/4939/4939763.xml?temp=3.864688E-02
http://community.csdn.net/Expert/topic/4941/4941041.xml?temp=.4557459表: tb_forum(id,title,userid,createtime)
tb_forum_reply(id,tb_forum_id,title,userid,createtime)
现在要列出
1. 主题(title), 最后回应人,最后回应时间
按发表时间倒排序
解答: Select a.title as 主题,b.userid as 最后回应人,b.createtime as 最后回应时间 ,
(Select count(1) from tb_forum_reply where tb_forum_id=a.id) as 回复数
from tb_forum as a inner Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(select 1 from tb_forum_reply where tb_forum_id=a.id and createtime>b.createtime) 2. 主题(title), 最后时间这个最后时间是指如果该主题没有回复则取主题创建的日期(tb_forum.createtime),如果有回复,则取回复的最近时间(tb_forum_reply.createtime),
结果按这个最后时间倒排???
解答:
Select a.title 主题,
IsNULL(b.createtime,a.createtime) 最后回应时间
from tb_forum a left Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(Select 1from tb_forum_reply where id=a.id and
Createtime>b.Createtime)
order by 最后回应时间 desc按照前面帖子给出的解答在本机可以,可是到服务器上数据库不支持子查询,怎么改写呀?急!!!
http://community.csdn.net/Expert/topic/4939/4939763.xml?temp=3.864688E-02
http://community.csdn.net/Expert/topic/4941/4941041.xml?temp=.4557459表: tb_forum(id,title,userid,createtime)
tb_forum_reply(id,tb_forum_id,title,userid,createtime)
现在要列出
1. 主题(title), 最后回应人,最后回应时间
按发表时间倒排序
解答: Select a.title as 主题,b.userid as 最后回应人,b.createtime as 最后回应时间 ,
(Select count(1) from tb_forum_reply where tb_forum_id=a.id) as 回复数
from tb_forum as a inner Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(select 1 from tb_forum_reply where tb_forum_id=a.id and createtime>b.createtime) 2. 主题(title), 最后时间这个最后时间是指如果该主题没有回复则取主题创建的日期(tb_forum.createtime),如果有回复,则取回复的最近时间(tb_forum_reply.createtime),
结果按这个最后时间倒排???
解答:
Select a.title 主题,
IsNULL(b.createtime,a.createtime) 最后回应时间
from tb_forum a left Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(Select 1from tb_forum_reply where id=a.id and
Createtime>b.Createtime)
order by 最后回应时间 desc按照前面帖子给出的解答在本机可以,可是到服务器上数据库不支持子查询,怎么改写呀?急!!!
tb_forum a left join tb_forum_reply b on a.id=b.tb_forum_id
order by a.id asc
tb_forum a inner join
(
select M.* from tb_forum_reply M inner join
(select tb_forum_id,title,max(createtime) as CreateTime from tb_forum_reply group by tb_forum_id,title)N on m.tb_forum_id=n.tb_forum_id and m.title=n.tilte and
M.createtime=N.createTIme)
b on a.id=b.tb_forum_id and
order by a.id asc
select a.title,b.userid,b.createtime,status=case when b.id is null then '未有回复' else '最后回复信息' end from
tb_forum a left join
(
select M.* from tb_forum_reply M inner join
(select tb_forum_id,title,max(createtime) as CreateTime from tb_forum_reply group by tb_forum_id,title)N on m.tb_forum_id=n.tb_forum_id and m.title=n.tilte and
M.createtime=N.createTIme)
b on a.id=b.tb_forum_id
顶上去