POWER
返回给定表达式乘指定次方的值。
语法:POWER ( numeric_expression , y )
参数:numeric_expression:是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。
y:numeric_expression 的次方。y 可以是精确数字或近似数字数据类型类别的表达式(bit 数据类型除外)。
返回类型:与 numeric_expression 相同。示例本示例显示返回结果 0.0 的浮点下溢。SELECT POWER(2.0, -100.0)
with sr5 as(
select
movieName=a.cnname,
movieid=a.id,
p.cnName,
p.id,
(select case mrp2.sortid
when 10 then b2.amount/100*1
when 9 then b2.amount/100*9/10
when 8 then b2.amount/100*6/10
when 7 then b2.amount/100*4/10
when 6 then b2.amount/100*2/10
else 0
end
from ent_movie_relate_person mrp2,ent_personnel p2,ent_boxoffice b2
where mrp2.personid=p2.id and mrp2.professionid=2 and b2.movieid=mrp2.movieid and sortid>=6 and sortid<=10
and mrp2.movieid=mrp.movieid and mrp2.personid=mrp.personid) amount,
p.sex,
YEAR(GETDATE())-p.bornYear age
from ent_movie a
left join ent_boxoffice b on b.movieid = a.id
inner join ent_movie_relate_person mrp on a.id=mrp.movieid
inner join ent_personnel p on mrp.personid=p.id
where a.cnname<>'' and (copies>=0 and copies<=1500 or copies is null) and mrp.sortid>=6 and mrp.sortid<=10 and amount>0 and p.cnName is not null and p.cnName!=''
and releaseTime>='2007-10-1' and releaseTime<'2012-10-1' and b.typeid in(1,2,8,9)
)
select
id
,cnName
,COUNT(1) allNum5
,sum(amount)/100 amount5
,MAX(amount)/100 max5
,[dbo].[GetMinAmount](sr5.id)/10000 min5
,dbo.[Merger]('2007-10-1','2012-10-1',id) person5
,cast(sum(amount)/100 as float)/cast(COUNT(1) as float) 'mean'
,POWER(POWER(cast(sum(amount)/100 as float),1.2)*(cast(MAX(amount)/100.0 as float))*(cast([dbo].[GetMinAmount](sr5.id) as float)/10000.0), 0.125)*POWER(CAST(COUNT(1) as float),0.32) '指数'
,POWER(cast(cast(sum(amount)/100 as float)/cast(COUNT(1) as float)/10000.0 as float),0.2) '均值比例'
,POWER(POWER(cast(sum(amount)/100 as float),1.2)*(cast(MAX(amount)/100.0 as float))*(cast([dbo].[GetMinAmount](sr5.id) as float)/10000.0), 0.125)*POWER(CAST(COUNT(1) as float),0.32)*POWER(cast(cast(sum(amount)/100 as float)/cast(COUNT(1) as float)/10000.0 as float),0.2) '指数3'
from sr5
--where 1=1
--and sr5.cnName='赵薇'
--and sr5.sex=1
--and sr5.age<20
group by cnName,id
order by sum(amount) desc