sql查询结果输出为百分比

请教：
SQL的查询语句如下：
=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, (zys+cys)/(0.334*37*ylc) as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )

想将红色字体的结果输出为百分比。如：50.00%

参考过这一句：cast(cast(分子*1.0*100/分母 as decimal(10,2)) as varchar(50)) +'%'
但将这一句加进去的时候会提示：语句中含有不能识别的短语或关键字。

8 个解决方案

#1

补充说明：VFP版本是VFP9.0，SP2；
SQL版本是SQL2000。

#2

用这个你就熟悉了：

select ..., str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl, ...
from ...

#3

=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )

是这样子吗？这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”

另外，用SQLEXEC(insert into...values...) 时，如果字段名太长或者太多，也会出现这样的提示。
上次试过values13个字段后就错误提示，后来不得不改短字段了。

#4

你的字符串长度超过了 255,所以报错，分成2个字符串即可，改为：

=SQLExec(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl, str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj "+"from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )

#5

如果你要传递的 SQL 语句太长, 检查它是否超过了 Visual FoxPro 串长的最大限度 255 字符。太长的串会产生 "命令中含有不能识别的短语或关键字。" 错误。但是, 如果你把它们分割到多个相连的串中, 你可以传递长的 SQL 语句。例如:

lnRetVal = SQLEXEC(lnHandle, "SELECT <long list of fields> " + ;
"FROM <several tables> " + ;
"WHERE <complex filter expression>")

#6

引用 3 楼 xinwen609 的回复:

=SQLEXEC(lnHandle,"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj, (zys+cys)/0.334 as zzl,str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' as szl,(zys+cys)/rs as pj from schz where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)","schz_temp" )

是这样子吗？这样我也试过了。也会提示“语句中含有不能识别的短语或关键字”

另外，用SQLEXEC(insert into...values...) 时，如果字段名太长或者太多，也会出现这样的提示。
上次试过values13个字段后就错误提示，后来不得不改短字段了。

这种情况，要用拼接字符串,如下，可能在类型上拼接上要再改
local cIn
cIn=''
cIn=cIn+"select id,rqi,yxxl,zlc,ylc,bc,rs,zys,cys,kjj,kjy,ryl,ryf,qf,zys+cys as hj,"
cIn=cIn+str((zys+cys)/0.334,3)+' as zzl, '
cIn=cIn+str(round((zys+cys)*100/(0.334*37*ylc),2),6,2)+'%' +' as szl,'
cIn=cIn+str(zys+cys)/rs +'as pj from schz '
cIn=cIn+'where (substring(rqi,6,2) = ?mo) and (substring(rqi,1,4) =?ye ) and (LTRIM(yxxl)=?yxx)'

#7

原来超过256个字符串也会报错！
楼上几位，回复的效率太高了！
谢谢！！

#8

稍后结贴。

#1