【acsess 多个left join】acsess 数据库 多个left join关联

acsess 数据库 语法 跟mysql 稍微不一样,多个left join 需要 加括号在 from 的后面

select

w1.f2,w1.f4,(w1.f16_6s+w2.f16_3+w3.f16_4+w4.f16_5) as total_score

from

((((
select

m.f2,m.f4,

iif(m.f4 in ('总经办','财务部','人事部','业务部','采购部','生产部','PMC','技术部'),m.f16_12*0.3,iif(m.f4 in ('品保部','机加课','拋光课','铸造课','仓库科'),m.f16_12*0.7,m.f16_12)) as f16_6s


from(

select

distinct a.f2,a.f4,a.f16_1*0.7+b.f16_2*0.3 as f16_12

from(



select jf.f2 as f2,jf.f4 as f4,sum(jf.f16) as f16_1 from jf  inner join aghg1  on aghg1.f2=jf.f2 
 where jf.f13 in('例行检查') and month(jf.t1)=month(date())
group by jf.f2,jf.f4
)a

left join(

select jf.f2 as f2,jf.f4 as f4,sum(jf.f16) as f16_2 from jf  inner join aghg1  on aghg1.f2=jf.f2 
 where jf.f13 in('日常检查') and month(jf.t1)=month(date())
group by jf.f2,jf.f4

)b

on a.f2=b.f2 and a.f4=b.f4

)m

)w1




left join(

select jf.f2 as f2,jf.f4 as f4,

sum(iif(jf.f4 in ('总经办','财务部','人事部','业务部','采购部','生产部','PMC','技术部'),jf.f16*0.7,iif(jf.f4 in ('品保部','机加课','拋光课','铸造课','仓库科'),jf.f16*0.3,jf.f16)))as f16_3


from jf inner join aghg1 on aghg1.f2=jf.f2

where jf.f13 in('ISO评分') and month(jf.t1)=month(date())

group by jf.f2,jf.f4

)w2


 on w1.f2=w2.f2  and w1.f4=w2.f4)



left join (

select jf.f2 as f2,jf.f4 as f4,sum(jf.f16) as f16_4 from jf  inner join aghg1  on aghg1.f2=jf.f2 
 where jf.f13 in('扣分表') and month(jf.t1)=month(date())

group by jf.f2,jf.f4


)w3

on w1.f2=w3.f2 and w1.f4=w3.f4)

left join(

select jf.f2 as f2,jf.f4 as f4,sum(jf.f16) as f16_5 from jf inner join aghg1  on aghg1.f2=jf.f2 

where jf.f13 in('日常行为') and month(jf.t1)=month(date())

group by jf.f2,jf.f4

)w4

on w1.f2=w4.f2 and w1.f4=w4.f4)



order by w1.f16_6s+w2.f16_3+w3.f16_4+w4.f16_5 desc













已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页