爱华网防止记录条数过多 返回值错误
先来看下面的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%的数据
