菜鸟求教

Question

ShiftID        System           TotalJob
A.SLITTER-CR Cimpack System             2
A.SLITTER-WS Cimpack System            16
ATY 1         FloorTrack System          10
BOBST A          Cimpack System            8
BOBST H   Cimpack System       6
BOBST H         FloorTrack System    5
C PACK    Cimpack System           55
F3C FAZILLAH FloorTrack System   14
F3C ZAINUDIN FloorTrack System   14
FFG KAMAL Cimpack System           2
FFG KAMAL FloorTrack System   5
 
根据以上的表格， 打个比方， BOBST H 的cimpack 用了6 个， floortrack 用了5，中共用了11 个
请问各位高手，如何用sql 来实现 % ， 比如floortrack 占了多少%， cimpack 占了多少%

Answer 1

格式亂，給你寫個例子
select ShiftID,
sum(case when System = 'cimpack' then TotalJob else 0 end)*1.0/sum(TotalJob) as cimpack ,
sum(case when System = 'floortrack' then TotalJob else 0 end)*1.0/sum(TotalJob) as floortrack
from table1
where ShiftID='BOBST H'
group by ShiftID

Answer 2

--举个例子
select ltrim(5*100.0/(5+6+11))+'%'