MS sqlserver 查询问题，请教高手！

[waydalee]

SELECT distinct(a.bm_id),COUNT(DISTINCT(user_id)) as usercount,bm_count,
((COUNT(DISTINCT(user_id)))*10000/bm_count) as per
FROM DoInfo AS p INNER JOIN BmInfo AS a ON a.bm_id=p.bm_id group by
a.bm_id ,a.bm_temp ,bm_count,user_id having a.bm_temp= 1 and user_id>0 order by per desc
查询语句是这样子的，也就是查询参与人数的百分比！这样查询出来的是所有的，我想知道怎么能够查询到前3条呢？
前面加top 3 会和 distinct有冲突！至于这个usr_id>0是因为我在表里面插入了一组空数据（为了页面显示）！

[人生难得一只鸡]

distinct在这里貌似没有任何的作用，既然如此，还不如去掉

[zhangcanqun]

select top 3 * from
(
SELECT distinct(a.bm_id),COUNT(DISTINCT(user_id)) as usercount,bm_count,
((COUNT(DISTINCT(user_id)))*10000/bm_count) as per
FROM DoInfo AS p INNER JOIN BmInfo AS a ON a.bm_id=p.bm_id group by
a.bm_id ,a.bm_temp ,bm_count,user_id having a.bm_temp= 1 and user_id>0
) as t order by t.per desc;
这样呢

[EnForGrass]

[Quote=引用楼主 的回复:]
SELECT distinct(a.bm_id),COUNT(DISTINCT(user_id)) as usercount,bm_count,
((COUNT(DISTINCT(user_id)))*10000/bm_count) as per
FROM DoInfo AS p INNER JOIN BmInfo AS a ON a.bm_id=p.bm_id group by
……
[/Quote]
可以用Row_Number函数先编号
再用between。。。and 。。。。。

[bdmh]

嵌套
select top 3 field1,field2 from (select......)

[anzhiqiang_touzi]

select top 3 * from
(
	SELECT distinct(a.bm_id),COUNT(DISTINCT(user_id)) as usercount,bm_count,
	 ((COUNT(DISTINCT(user_id)))*10000/bm_count) as per
	 FROM DoInfo AS p INNER JOIN BmInfo AS a ON a.bm_id=p.bm_id group by   
 a.bm_id ,a.bm_temp ,bm_count,user_id having a.bm_temp= 1 and user_id>0
) as t order by t.per desc;

[PitTroll]

写个嵌套查询。