Select...From TableName Union Select '..','..',Sum(...)From TableName
select PartNo,PartName,ReasonDesc,Count(ReasonDesc) as ReasonDescNum,sun(cast(ReasonDesc)as int) from MO,Reason where MONo in(select MONo from MO where PartNo='50540Y20V4003A1A1') and ReasonCode in (select ReasonCode from Repair where RepairStatus='1') group by PartNo,PartName,ReasonDesc 来帮我看一下这个SQL语句,sun(cast(ReasonDesc)as int)是对ReasonDesc求和,因为ReasonDesc是字符型的,所以转的时候老出错,各位有什么好的办法? 因为这帖子的问题先得解决这个SQL,所以解决好这个后就结帖!
Sum(ReasonDesc)你的ReasonDesc是什麽類型?
sun(cast(ReasonDesc as int)) 這樣才對 不是sun(cast(ReasonDesc)as int)
可是可以通过,能成功,但我看了一下显示的数据不符合.这个是我的 select ReasonDesc,Count(ReasonDesc) as ReasonDescNum,cast(Count(ReasonDesc)as float)/cast((select Count(ReasonDesc) from Reason where not (ReasonDesc='' or ReasonDesc is NULL))as float ) as '百分比' from MO,Reason where MONo in(select MONo from MO ) and ReasonCode in (select ReasonCode from Repair where RepairStatus='1' )group by PartNo,PartName,ReasonDesc有正确的结果,只是百分比里是以小数实现的.现在要变为%的形式.望高手解决一下
Create Function toPercent(@floNum Float) Returns Varchar(10) As Begin Declare @varPer Varchar(10) Select @varPer=@floNum*100 Select @varPer=@varPer+'%' Return @varPer End 用函数好了 select ReasonDesc,Count(ReasonDesc) as ReasonDescNum,dbo.toPercent(cast(Count(ReasonDesc)as float)/cast((select Count(ReasonDesc)) from Reason where not (ReasonDesc='' or ReasonDesc is NULL))as float ) as '百分比' from MO,Reason where MONo in(select MONo from MO ) and ReasonCode in (select ReasonCode from Repair where RepairStatus='1' )group by PartNo,PartName,ReasonDesc
Union
Select '..','..',Sum(...)From TableName
来帮我看一下这个SQL语句,sun(cast(ReasonDesc)as int)是对ReasonDesc求和,因为ReasonDesc是字符型的,所以转的时候老出错,各位有什么好的办法?
因为这帖子的问题先得解决这个SQL,所以解决好这个后就结帖!
這樣才對 不是sun(cast(ReasonDesc)as int)
select ReasonDesc,Count(ReasonDesc) as ReasonDescNum,cast(Count(ReasonDesc)as float)/cast((select Count(ReasonDesc) from Reason where not (ReasonDesc='' or ReasonDesc is NULL))as float ) as '百分比' from MO,Reason where MONo in(select MONo from MO ) and ReasonCode in (select ReasonCode from Repair where RepairStatus='1' )group by PartNo,PartName,ReasonDesc有正确的结果,只是百分比里是以小数实现的.现在要变为%的形式.望高手解决一下
Returns Varchar(10)
As
Begin
Declare @varPer Varchar(10)
Select @varPer=@floNum*100
Select @varPer=@varPer+'%'
Return @varPer
End
用函数好了
select ReasonDesc,Count(ReasonDesc) as ReasonDescNum,dbo.toPercent(cast(Count(ReasonDesc)as float)/cast((select Count(ReasonDesc)) from Reason where not (ReasonDesc='' or ReasonDesc is NULL))as float ) as '百分比' from MO,Reason where MONo in(select MONo from MO ) and ReasonCode in (select ReasonCode from Repair where RepairStatus='1' )group by PartNo,PartName,ReasonDesc