sql中的Power函数(幂运算)

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