三表:表A(用户),列ID和UserName
表B(主题),列ID,UserID(A表ID的外键)和Time(发表时间)
表C(回复),列ID,TopicalID(B表ID的外键),UserID(A表ID的外键)和Time(发表时间)
现需求最后发表(不分主题和回复,只要最新,即可能是主题也可能是回复),包含发表用户名(UserName)和发表时间(Time)
表B(主题),列ID,UserID(A表ID的外键)和Time(发表时间)
表C(回复),列ID,TopicalID(B表ID的外键),UserID(A表ID的外键)和Time(发表时间)
现需求最后发表(不分主题和回复,只要最新,即可能是主题也可能是回复),包含发表用户名(UserName)和发表时间(Time)
select a.UserName,case when max(b.Time) > max(c.Time) then max(b.Time) else max(c.Time)end 发表时间
from 表A a
join 表B b on a.ID=b.UserID
join 表C c on b.UserID=c.TopicalID and a.ID=c.UserID
用户表UserInfo
UserID UserName
1 yuhao
2 lvjingjing
3 wuyancheng主题表Topic
TopicID UserID TopicTime
1 1 2008-3-6 0:00:00
2 2 2008-3-15 0:00:00
3 3 2008-3-8 0:00:00回帖表Reply
ReplyID TopicID UserID ReplyTime
1 1 2 2008-3-1 0:00:00
2 1 3 2008-4-12 0:00:00
3 1 1 2008-3-23 0:00:00
4 2 1 2008-4-6 0:00:00SELECT top 1 TB.time as Time
,case when TB.tTime>TB.rTime then TB.TopicID end as tID
,case when TB.tTime<TB.rTime then TB.ReplyID end as rID
,case when TB.tTime>TB.rTime then TB.tUID end as tUID
,case when TB.tTime<TB.rTime then TB.rUID end as rUID
INTO #temp_table FROM
(SELECT t.TopicID
,t.UserID as tUID
,t.TopicTime as tTime
,r.UserID as rUID
,r.ReplyID
,r.ReplyTime as rTime
,case when t.TopicTime>r.ReplyTime then t.TopicTime else r.ReplyTime end as time
FROM [Topic] t left join Reply r on t.TopicID=r.TopicID) TB
order by TB.time descdeclare @TID int
declare @RID int
select @TID=tID from #temp_table
select @RID=rID from #temp_tableIF @TID is not null
BEGIN
SELECT t.tID,u.UserName, t.[Time] FROM #temp_table t INNER JOIN UserInfo u on t.tUID=u.UserID
ENDIF @RID is not null
BEGIN
SELECT t.rID,u.UserName, t.[Time] FROM #temp_table t INNER JOIN UserInfo u on t.rUID=u.UserID
END
测试结果rID UserName Time
2 wuyancheng 2008-04-12 00:00:00.000