select a.id,a.FType,a.Fac,a.Fac1,''as FType3,
(case when (a.si+a.oi>0 and a.ci+a.oi>0) then round((a.si+a.oi)/(a.ci+a.oi),4)*100 else 0 end)as bfb
from
(
SELECT id,(FType+0.1)as FType,Fac,Fac1,
sum(case when FType3='IC' then cont else 0 end)as ci,
sum(case when FType3='IS' then cont else 0 end)as si,
sum(case when FType3='IO' then cont else 0 end)as oi
FROM ##dateFac
left join ##dateFacmx d on id=d.trdj and FType1=d.wawr03 and FType3=d.ildct
group by id,FType,Fac,Fac1
) a想在
round((a.si+a.oi)/(a.ci+a.oi),4)*100
這個結果後面拼接個%
因為結果是直接展現在欄位中的沒辦法在處理,
可不可以直接結果後面拼接%?
(case when (a.si+a.oi>0 and a.ci+a.oi>0) then round((a.si+a.oi)/(a.ci+a.oi),4)*100 else 0 end)as bfb
from
(
SELECT id,(FType+0.1)as FType,Fac,Fac1,
sum(case when FType3='IC' then cont else 0 end)as ci,
sum(case when FType3='IS' then cont else 0 end)as si,
sum(case when FType3='IO' then cont else 0 end)as oi
FROM ##dateFac
left join ##dateFacmx d on id=d.trdj and FType1=d.wawr03 and FType3=d.ildct
group by id,FType,Fac,Fac1
) a想在
round((a.si+a.oi)/(a.ci+a.oi),4)*100
這個結果後面拼接個%
因為結果是直接展現在欄位中的沒辦法在處理,
可不可以直接結果後面拼接%?
(case when (a.si+a.oi>0 and a.ci+a.oi>0) then round((a.si+a.oi)/(a.ci+a.oi),4)*100 else 0 end)+'%'as bfb
from
(
SELECT id,(FType+0.1)as FType,Fac,Fac1,
sum(case when FType3='IC' then cont else 0 end)as ci,
sum(case when FType3='IS' then cont else 0 end)as si,
sum(case when FType3='IO' then cont else 0 end)as oi
FROM ##dateFac
left join ##dateFacmx d on id=d.trdj and FType1=d.wawr03 and FType3=d.ildct
group by id,FType,Fac,Fac1
) a
將 varchar 值 '1.470000%' 轉換成資料類型 int 時,轉換失敗。
警告: 彙總或其他 SET 作業已刪除 Null 值。
cast((case when (a.si+a.oi>0 and a.ci+a.oi>0) then round((a.si+a.oi)/(a.ci+a.oi),4)*100 else 0 end) as varchar)+'%'as bfb
from
(
SELECT id,(FType+0.1)as FType,Fac,Fac1,
sum(case when FType3='IC' then cont else 0 end)as ci,
sum(case when FType3='IS' then cont else 0 end)as si,
sum(case when FType3='IO' then cont else 0 end)as oi
FROM ##dateFac
left join ##dateFacmx d on id=d.trdj and FType1=d.wawr03 and FType3=d.ildct
group by id,FType,Fac,Fac1
) a
(case when (a.si+a.oi>0 and a.ci+a.oi>0) then ltrim(round((a.si+a.oi)/(a.ci+a.oi),4)*100)+'%' else 0 end)as bfb
from
(
SELECT id,(FType+0.1)as FType,Fac,Fac1,
sum(case when FType3='IC' then cont else 0 end)as ci,
sum(case when FType3='IS' then cont else 0 end)as si,
sum(case when FType3='IO' then cont else 0 end)as oi
FROM ##dateFac
left join ##dateFacmx d on id=d.trdj and FType1=d.wawr03 and FType3=d.ildct
group by id,FType,Fac,Fac1
) a