还是查询问题

[xiangr25]

上面的代码没有得到想要的结果
比如
select top 4 * from
(select top 3 smid,sum(clickall) sm
from New_ClickAll
group by smid
order by sm
) t1
order by sm desc
----------------
这段代码要得到的是 排名为从3到4的记录 显示出来的应该是一条记录,可是却显示了3条
为了让对功能的需求有个经清析点的了解,我说一下我的代码的用处,用处就是:根据sum(clickall) 查询出排名,用户可以 输入两个数据,即排名的开始到排名的结束 比如 我想查出
排名在50位到140位的数据,那么最后 列了来的就是90条数据呈现给用户

[leohuang]

不对吧,哥们
从3到4应该是两个啊,咋成top 1了呢,应该是top 2 吧

[点点星灯]

--这样才是排名为从3到4的记录
select top 1 * from
(select top 4 smid,sum(clickall) sm
from New_ClickAll
group by smid
order by sm
) t1
order by sm desc

[点点星灯]

declare @BeginNum int
declare @EndNum int
set @BeginNum=3
set @EndNum=4

--如果要加参数，用动态SQL语句，注意理解逻辑关系
select top 1 * from --(@EndNum-@BeginNum)
(select top 4 smid,sum(clickall) sm --@EndNum
from New_ClickAll
group by smid
order by sm
) t1
order by sm desc

[xiangr25]

查询还要加一个条件 即IsYes 代表着这条记录是否有效, 0无效 1有效,现在查询只针对有效记录,该如何改

[paoluo]

select top 1 * from
(select top 4 smid,sum(clickall) sm
from New_ClickAll
Where IsYes =1
group by smid
order by sm
) t1
order by sm desc

[robert_ypeng]

select top 1 * from
(select top 4 smid,sum(clickall) sm
from New_ClickAll
Where IsYes =1
group by smid
order by sm
) t1
order by sm desc

请问第一句select top 1 * from 在top 1 * 的后面为什么要加星号*?? 谢谢
还有结果集3到4是按sm降序排列的，如何改为升序？？ 谢谢？？

[liangpei2008]

1.所有内容
2.楼上兄弟的最后一行改为
Order By Sm即可

[MoDingHua]

按照楼主的需求应该是这样吧：
declare @BeginNum int
declare @EndNum int
declare @sql varchar(2000)

set @sql = 'select top '+str(@EndNum)+' * from
(select smid,sum(clickall) sm
from New_ClickAll
group by smid
order by sm
) t1 where smid not in( select top '+str(@BeginNum)+' smid from
(select smid,sum(clickall) sm
from New_ClickAll
group by smid
order by sm
) t2)
order by sm desc'
exec (@sql)