一个小视图，不知怎么写，请教！

[uuuuu]

我有两个表Forums_Topics和Forums_Replies都有时间和MemberID字段，要求取出最后回复人的ID怎么写？
取出最后回复时间的视图如下：
(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
WHERE dates.TopicID = Forums_Topics.TopicID) AS LastPostDate

如果还要根据取出MemberID在Member表中取MemberName要怎么写？我想了好久，而且一定要在视图中实现！！头痛！

[liuyun2003]

关注

[aierong]

没有看懂你表结构

[uuuuu]

相关表的字段：
Forums_Topics:AddedDate,MemberID,TopicID
Forums_Replies:AddedDate,MemberID,TopicID,ReplyID
Member:MemberID,MemberName

[txlicenhe]

没看懂，是这样吗？
Select MemberName from Member where addedDate =
(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
WHERE dates.TopicID = Forums_Topics.TopicID)

[friendliu]

create view as
select b.Memberid,b.MerberName,(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
WHERE dates.TopicID = Forums_Topics.TopicID) AS LastPostDate
from Forums_Topics a,Forums_Replies b
where Forums_Topics.TopicID=Forums_Replies.TopicID
and (LastPostDate=a.AddedDate or LastPostDate=b.AddedDate)

[friendliu]

create view as
select b.Memberid,b.MerberName,(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
WHERE dates.TopicID = Forums_Topics.TopicID) AS LastPostDate
from Member c,Forums_Topics a,Forums_Replies b
where Forums_Topics.TopicID=Forums_Replies.TopicID
and (LastPostDate=a.AddedDate or LastPostDate=b.AddedDate)
and (c.MemberID=a.MemberID or c.MemberID=b.MemberID)

[zjcxc]

--应该是这个吧?

--create view test
--as
select MemberName from Member
where MemberID in(select top 1 MemberID from
(SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
order by AddedDate desc)

[happydreamer]

Select MemberName,AddDate from Member where AddDate=
(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates
WHERE dates.TopicID = Forums_Topics.TopicID and dates.MemberID=Member.MemberID)

[Wally_wu]

Forums_Topics和Forums_Replies的MemberID是什么关系？

[pengdali]

select MemberID,MemberName from Member where MemberID in (
select MemberID from (
SELECT TopicID,AddedDate,MemberID FROM Forums_Topics
union all
select TopicID,AddedDate,MemberID FROM Forums_Replies
) aaa where addeddate=(
select max(AddedDate) from (
SELECT TopicID,AddedDate FROM Forums_Topics
union all
select TopicID,AddedDate FROM Forums_Replies
) tem where topicid=aaa.topicid))

[windywalk]

试试吧,我也不知道可以不!
(SELECT top 1 Dates_AddedDate,Member_MemberName as MemberName
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies) AS dates,
Member
WHERE dates.TopicID = Forums_Topics.TopicID and Dates_MemberId=Member_MemberId Order by Dates_Addeddate Desc) AS LastPostDate

[uuuuu]

多谢了，结帐

[hqsee]

Select MemberID from Member where addedDate IN
(SELECT MAX(AddedDate)
FROM (SELECT TopicID, AddedDate,MemberID
FROM Forums_Topics
UNION ALL
SELECT TopicID, AddedDate,MemberID
FROM Forums_Replies)
WHERE dates.TopicID = Forums_Topics.TopicID)