select
USERID,successCount,errorCount,NotBackCount,SuccessCM,SuccessCU,SuccessCT,
CONVERT(varchar(50),
cast(cast(17000 as float(20))/POWER(1024,cast(p.Pr as float(20))) as decimal(18,2)))
+ p.suffix SendCMTraffic
from
(select USERID,
SUM(case when REPORT_STAT='DELIVRD' then 1 else 0 end) successCount,
SUM(case when REPORT_STAT<>'DELIVRD' then 1 else 0 end) errorCount,
SUM(case when REPORT_STAT IS NULL then 1 else 0 end) NotBackCount,
SMS_CONTENT,
SUM(case when MOBILE_NO_TYPE=1 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCM,
SUM(case when MOBILE_NO_TYPE=2 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCT,
SUM(case when MOBILE_NO_TYPE=3 AND REPORT_STAT='DELIVRD' THEN 1 ELSE 0 END) SuccessCU
from SMS_MT where
Convert(varchar(8),INTO_TIME,112)=Convert(varchar(8),'20140401',112)
group by USERID,SMS_CONTENT having USERID='xdx') b
,SMS_Suffix p
;
出现这个错误.
消息 232,级别 16,状态 3,第 1 行
类型 int 发生算术溢出错误,值 = 1099511627776.000000。
可是我如何把
CONVERT(varchar(50),
cast(cast(17000 as float(20))/POWER(1024,cast(p.Pr as float(20))) as decimal(18,2)))
p.pr改成0就不出错..很纠结
我本身的数据库pr这个字段就是int类型.请大神明示
------解决方案--------------------
肯定是超限,但提示1099511627776.000000这个数,这是1024的4次方。
所以出错的地方是power函数。所以power函数里要转型。
POWER(1024,.....)写成,POWER(cast(1024 as bigint),.....)