链接两个table

select 
a.shiftid,a.[count],
sum(case when b.system like 'cimpack%' then job else 0 end) as cimpack,
sum(case when b.system like 'floortrack%' then job else 0 end) as floortrack
from t1 inner joint t2 on t1.shiftid=b.shiftid
group by a.shiftid,a.[count]

select 
a.shiftid,a.[count],
sum(case when b.system like 'cimpack%' then job else 0 end) as cimpack,
sum(case when b.system like 'floortrack%' then job else 0 end) as floortrack
from t1 as a inner joint t2 as b on t1.shiftid=b.shiftid
group by a.shiftid,a.[count]

select 
  a.shiftid,a.[count],
  sum(case when b.system like 'cimpack%' then job else 0 end) as cimpack,
  sum(case when b.system like 'floortrack%' then job else 0 end) as floortrack
from
 a ,b 
where
 a.shiftid=b.shiftid
group by
 a.shiftid,a.[count]