新闻表news n
nID, title
回复表review r
rID, content, AddTime, nID, uID
用户表user u
uID,uName
视图：
userNewsReview (联合表news，review,user)
rID,content,AddTime,nID,Title,nID,uID,uID,uName
联合条件 r.nID=n.niD and r.uID=u.uID
需求：
取前userNewsReview 6条 要求在前6条中，无重复的新闻，回复表AddTime 降序排列
------解决方案--------------------
select top 6 rID,content,AddTime,nID,Title,nID,uID,uID,uName
from (select distinct nID from review order by Addtime) as r,news n,user u
where r.nID=n.niD and r.uID=u.uID
------解决方案--------------------
CREATE VIEW userNewsReview
AS
select TOP 6 rID,content,AddTime,nID,Title,nID,uID,uID,uName
from review as r
left join news as n on r.nID = n.nID
left join user as u on r.uID = u.uID
where not exists(select 1 from review where nID = r.nID and AddTime > AddTime)
order by AddTime DESC
------解决方案--------------------
To:楼主
最好给出具体的数据,那样可能会更加明了...
SELECT Top 6 r.rID,r.content,r.AddTime,n.nID,n.Title,u.uID,u.uName
FROM (select distinct nID from review order by Addtime desc) as r
INNER JOIN news n on n.nid=r.rid
INNER JOIN user u on u.uid=r.rid
------解决方案--------------------
楼主能不能给出你的视图啊
以下例子:nID不重复(nID重复时取addTime最大那个)，addTime降序排列
declare @t table(nid int,addtime datetime)
insert @t select 1, '2007-07-10 '
union all select 2, '2007-07-02 '
union all select 2, '2007-07-03 '
union all select 3, '2007-07-04 '
select * from @t
select * from @t a where not exists
(select 1 from @t where nid=a.nid and addtime> a.addtime) order by addtime desc
------解决方案--------------------
select n.title as '标题 ',ten10news.addtime as '最近回复时间 ',u.nName as '回复人 ',r.content as '回复内容 '
from news n,user u,review r,
( select top 10 max(addtime) as addtime ,nID from review group by nID ) ten10news
where ten10news.addtime = r.addtime and
tem10news.nID = r.nID and
r.uID = u.uID and
r.nID = n.nID
------解决方案--------------------
觉得你应该把回复表加肥一些,完全可以把新闻标题和回复人也在回复表里加一列,那样检索时,对新闻表和用户表的连接查询就不需要了.空间换时间.