防止记录条数过多 返回值错误
先来看下面的sql语句
select * from (
select
(select Name from T_Base_IMSIUserSet where ID=a.usersetid) as[IMSI用户组],a.usersetid
,
sum(a.TCHtakeNum) as [TCH占用次数],
sum(case when 1=1 then a.DropNum else 0 end) as [掉话次数],
round((case when sum(a.TCHtakeNum) = 0 then 0 e————lse cast(sum(casewhen 1=1 then a.DropNum else 0 end) as float )/sum(a.TCHtakeNum)end),4,1) as [掉话率] from T_Drop a where
a.RequestTime>='2010/1/9 16:00:00' anda.RequestTime<='2010/1/25 16:59:00'
group by
a.usersetid order by [掉话率] DESC ) t
----------------------错误提示:
消息 1033,级别 15,状态 1,第 11 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
这时就需要用到 top 100 percent 了,原因是:针对一个表的SELECT其实并不是返回一个表,而是一个游标。
改正方法就是:
select * from (
select top 100percent
(select Name from T_Base_IMSIUserSet where ID=a.usersetid) as[IMSI用户组],a.usersetid
,
sum(a.TCHtakeNum) as [TCH占用次数],
sum(case when 1=1 then a.DropNum else 0 end) as [掉话次数],
round((case when sum(a.TCHtakeNum) = 0 then 0 else cast(sum(casewhen 1=1 then a.DropNum else 0 end) as float )/sum(a.TCHtakeNum)end),4,1) as [掉话率] from T_Drop a where
a.RequestTime>='2010/1/9 16:00:00' anda.RequestTime<='2010/1/25 16:59:00'
group by
a.usersetid order by [掉话率] DESC ) t
注释:
top 100percent :就是前100%行,就是所有行的数据, top10percent ,就是取前10%的数据