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

Question

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

Answer 1

没看懂，是这样吗？
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)

Answer 2

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)

Answer 3

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)

Answer 4

--应该是这个吧?--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)

Answer 5

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)

Answer 6

Forums_Topics和Forums_Replies的MemberID是什么关系？

Answer 7

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))

Answer 8

试试吧,我也不知道可以不!
(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

Answer 9

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)