请教sql查询怎么忽略重复行

[liushihua]

做了个小bbs
要在bbs回帖表（bbs_post） 中查询10个最新的ThreadId(主帖表的外键)
select top 10 id,threadId,pubdate from bbs_post order by pubdate
查询结果如下：
id threadId pubdate
17 34 2008-12-15
18 20 2008-12-15
19 20 2008-12-15
。。。。。
。。。。
请教各位，怎么忽略或跳过重复的记录而找到最新的10条呢？
18 20
19 20

[敏捷William]

这个问题我也遇到过，不知道该怎么解决？
希望你的有人解决，我也好看看。

[koukoujiayi]

id不要显示,可这样:
select distinct top 10 threadId,pubdate from bbs_post order by pubdate

[jiang_jiajia10]

用 distinct

[dd__dd]

[Quote=引用 2 楼 koukoujiayi 的回复:]
id不要显示,可这样:
select distinct top 10 threadId,pubdate from bbs_post order by pubdate
[/Quote]
正解，因为id是唯一的所以要用distinct就必须不显示id

[zetee]

distinct

[liushihua]

select distinct top 10 threadId,pubdate from bbs_post order by pubdate desc
结果：
20 2008-12-15 23:17:06.000
20 2008-12-15 22:17:07.623
20 2008-12-15 22:17:07.610
20 2008-12-15 22:17:07.593
20 2008-12-15 22:17:07.577
20 2008-12-15 22:17:07.560
20 2008-12-15 22:17:07.547
20 2008-12-15 22:17:07.530
20 2008-12-15 22:17:07.513
20 2008-12-15 22:17:07.500

[敏捷William]

select distinct top 10 threadId,pubdate from bbs_post order by pubdate

这样查询出时间并不是最新的啊?

如果是用desc倒叙后，编号就不对了??

[HolyPlace]

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
参考一下。

[liushihua]

我想要这样的结果：
ThreadID pudate
65 2008-12-15 22:17:07.750
25 2008-12-15 22:17:06.750
39 2008-12-15 22:17:05.750
27 2008-12-15 22:17:04.750
29 2008-12-15 22:16:06.750
22 2008-12-15 22:13:06.750
43 2008-12-15 22:11:06.750
24 2008-12-15 22:05:06.750
26 2008-12-15 22:13:06.750
33 2008-12-15 22:11:06.750
pudate是desc排序的

[zjybushiren88888]

distinct关键字 去除重复

[yagebu1983]

distinct!!!
关键你是怎样用！！

[liushihua]

我是这样用的
select distinct top 10 threadId,pubdate from bbs_post order by pubdate desc

结果：
id pubdate
20 2008-12-15 23:17:06.000
20 2008-12-15 22:17:07.623
20 2008-12-15 22:17:07.610
20 2008-12-15 22:17:07.593
20 2008-12-15 22:17:07.577
20 2008-12-15 22:17:07.560
20 2008-12-15 22:17:07.547
20 2008-12-15 22:17:07.530
20 2008-12-15 22:17:07.513
20 2008-12-15 22:17:07.500
id 全是重复的

[criedshy]

按照ThreadID 分组,取最大时间就不得了

[criedshy]

select top 10 t.threadId,t.pubdate 
 from (select threadId,max(pubdate) as pubdate from bbs_post) t order by pubdate 

[criedshy]

select top 10 t.threadId,t.pubdate 
 from (select threadId,max(pubdate) as pubdate from bbs_post) t order by pubdate 

[criedshy]

希望有对你帮助

[liushihua]

criedshy 说的行我又加了group by threadId 分组 不然报错
select top 10 t.threadId,t.pubdate
from (select threadId,max(pubdate)
as pubdate from bbs_post group by threadId) t order by pubdate desc
结果OK:
threadId pubdate
20 2008-12-15 23:17:06.000
76 2008-12-15 22:17:06.763
89 2008-12-15 22:17:06.763
45 2008-12-15 22:17:06.763
56 2008-12-15 22:17:06.763
34 2008-12-15 22:17:06.763
23 2008-12-15 22:17:06.750
22 2008-12-15 22:17:06.750
65 2008-12-15 22:17:06.750
24 2008-12-15 22:17:06.750

[敏捷William]

我的问题也OK了！！

[CluoLeo]

[Quote=引用 14 楼 criedshy 的回复:]
SQL codeselect top 10 t.threadId,t.pubdate
from (select threadId,max(pubdate) as pubdate from bbs_post) t order by pubdate
[/Quote]
这个方法我也认同

[criedshy]

[Quote=引用 17 楼 liushihua 的回复:]
criedshy 说的行我又加了group by threadId 分组 不然报错
select top 10 t.threadId,t.pubdate
from (select threadId,max(pubdate)
as pubdate from bbs_post group by threadId) t order by pubdate desc
结果OK:
threadId pubdate
20 2008-12-15 23:17:06.000
76 2008-12-15 22:17:06.763
89 2008-12-15 22:17:06.763
45 2008-12-15 22:17:06.763
56 2008-12-15 22:17:06.763
34 2008-12-15 …
[/Quote]

是啊,刚才忘了加group by,哈哈