Select a.title as 主题,b.userid as 最后回应人,
IsNULL(b.createtime,a.createtime) as 最后回应时间
from tb_forum as a left Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(Select * from tb_forum_reply where id=a.id and
Createtime>b.Createtime)
order by 最后回应时间 desc
IsNULL(b.createtime,a.createtime) as 最后回应时间
from tb_forum as a left Join tb_forum_reply b on a.id=b.tb_forum_id
where not exists(Select * from tb_forum_reply where id=a.id and
Createtime>b.Createtime)
order by 最后回应时间 desc
解决方案 »
- 关于从手机模拟器访问pc端sql server数据库的问题
- 问一个简单的问题,哪位大哥能帮一下
- 怎么写?:-(
- 跪求面试的一道题解法
- 有关插入某列数值的问题
- 帮忙一个用户权限问题。我只有41分了。都给你好了。~~~~~:(
- 大家好,请教一下,这样的SQL语句怎么写呢?
- 怎么用存储进程修改Truncate log on checkpoint属性?
- 关于表触发器中两个触发器执行的先后顺序!
- 类型转换问题,急急急
- 大对象问题(text,ntext,image)高分呼唤高手
- 在过程中日期2006-05-21 17:15:43 如何只要日期2006-05-21,不用Convert(varchar(20),mydate,102)
id,
title,
userid,
case (Select max(createtime) from tb_fornum_reply where id=tb_forum_id)
when is null then createtime
else (Select max(createtime) from tb_fornum_reply where id=tb_forum_id)
end as 最后时间
from tb_fornum
INSERT TB_FORUM SELECT 1,'AA',1,'2005-6-9'
UNION ALL SELECT 2,'BB',2,'2005-9-8'
UNION ALL SELECT 3,'CCC',2,'2005-9-8' --该贴无人回
GO
CREATE TABLE tb_forum_reply(ID INT,tb_forum_id INT,title VARCHAR(10),userid INT,CREATETIME DATETIME)
INSERT tb_forum_reply SELECT 1,1,'ABC',3,'2005-6-10'
UNION ALL SELECT 2,2,'BBD',4,'2005-9-9'SELECT
'主题'=A.TITLE ,
'最后时间'= ISNULL((SELECT TOP 1 CREATETIME FROM tb_forum_reply B WHERE TB_FORUM_ID=A.ID ORDER BY 1 DESC),A.CREATETIME)
FROM TB_FORUM A