查无重复数据的前N条记录，求高效的sql语句

新闻表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 降序排列

16 个解决方案

#1

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

#2

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

#3

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

#4

各位理解错了，视图userNewsReview 我已经建好
我求的是在视图中取前六条要求：nID不重复，addTime降序排列

#5

TO:hellowork(一两清风) ( ) 信誉：100 Blog 加为好友
为什么要用LEFT JOIN 呢?

#6

select top 6 r.rID,r.content,r.AddTime,t.nID,r.Title,n.nID,r.uID,u.uID,u.uName
from (select distinct nID from review order by Addtime) as t,review r,news n,user u
where r.nID = t.nID and r.nID=n.niD and r.uID=u.uID

#7

楼主能不能给出你的视图啊
以下例子: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

#8

----方法1:
select DISTINCT TOP 6 * from userNewsReview as a
where not exists(select 1 from userNewsReview where nID = a.nID and AddTime > a.AddTime)

----方法2:
select DISTINCT TOP 6 a.* from userNewsReview as a
inner join (select nID,max(AddTime) as AddTime from userNewsReview group by nID) as b
on a.nID = b.nID and a.AddTime = b.AddTime

#9

抱歉,更正一下,上面的回复忘了ORDER BY AddTime DESC:

----方法1:
select DISTINCT TOP 6 * from userNewsReview as a
where not exists(select 1 from userNewsReview where nID = a.nID and AddTime > a.AddTime)
ORDER BY AddTime DESC

----方法2:
select DISTINCT TOP 6 a.* from userNewsReview as a
inner join (select nID,max(AddTime) as AddTime from userNewsReview group by nID) as b
on a.nID = b.nID and a.AddTime = b.AddTime
ORDER BY AddTime DESC

#10

这是我的视图
CREATE VIEW dbo.userNewsReview
AS
SELECT dbo.news.nID, dbo.news.title,
      dbo.user.uID, dbo.user.uName,
      dbo.review.rID, dbo.review.content, dbo.review.AddTime
FROM dbo.review INNER JOIN
      dbo.user ON dbo.review.uID = dbo.user.ID INNER JOIN
      dbo.news ON dbo.review.nID = dbo.news.nID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

#11

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

#12

hellowork(一两清风) 高手.

刚刚看时还只有一人回复,等我写完,已经一堆人回复了,下手慢了.

#13

觉得你应该把回复表加肥一些,完全可以把新闻标题和回复人也在回复表里加一列,那样检索时,对新闻表和用户表的连接查询就不需要了.空间换时间.

#14

-----这样试试
CREATE VIEW dbo.userNewsReview
AS
SELECT TOP 6 r.rID,r.content,r.AddTime,n.nID,n.Title,u.uID,u.uName
from review as r
inner join news as n on n.nID = r.rID
inner join user as u on u.uID = r.rID
where not exists(select 1 from review where nID = r.nID and AddTime > r.AddTime)
order by r.AddTime DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

#15

跟学！！！

#16

感谢大家，问题已经解决，现在就结帖

#1