我们有一个枚举表SY_ECODE。
但是一条数据的七八个字段都是枚举选项,数据库存的是枚举编码,每次查询数据取对应的枚举值,如果left join 的话,就要七八次,就如下边的SQL。我感觉用子查询的话好看一点,写起来也方便,不然的话l要eft join好多次,到底怎么优化呢?
select
(select e.ECODETEXT from SY_ECODE e where e.ecode = o.otsstatus and e.ecodetype = 'SQM_OTS_STATUS') otsstatus,
(select e.ECODETEXT from SY_ECODE e where e.ecode = c.conclusion and e.ecodetype = 'SQM_OTS_CONCLUSION') conclusion,
(select e.ECODETEXT from SY_ECODE e where e.ecode = p.parttype and e.ecodetype = 'SQM_APQP_PART_TYPE') parttype,
(select e.ECODETEXT from SY_ECODE e where e.ecode = p.professional and e.ecodetype = 'SQM_APQP_PROFESSION') professionalText,
(select e.ECODETEXT from SY_ECODE e where e.ecode = p.developtype and e.ecodetype = 'SQM_APQP_PART_DEVTYPE') developtype,
(select e.ECODETEXT from SY_ECODE e where e.ecode = j.PROJECTLEVEL and e.ecodetype = 'SQM_APQP_PROJECT_LEVEL') PROJECTLEVEL,
(select pf.platform from GPIS_BASE_PLATFORM pf where pf.id = p.platformid) platformText,
(select u.usertext from sy_user u where u.usercode = a.DEVUSERCODE ) devUserText,
(select u.usertext from sy_user u where u.usercode = a.SEUSERCODE) seUserText,
(CASE WHEN p.isSingleVehicle = '0' THEN '否' WHEN p.isSingleVehicle = '1' THEN '是' ELSE '' END ) isSingleVehicle,
o.id,o.otscode,a.projectid,j.PROJECTNAME,a.apqpCode,ap.subapqpcode,p.contractPartName,p.PARTNO,p.PARTNAME,
ap.PLANFINISHDATE,p.vehiclenames,ap.FINISHDATE
from
SQM_APQP_OTS o
left join SQM_OTS_CONCLUSION_ISSUED c on c.otscode = o.otscode
left join SQM_APQP_INFO a on a.id = o.apqpid
left join SQM_APQP_ASSOCIATE_PARTS ap on ap.apqpcode = a.APQPCODE
left join SQM_APQP_PARTS p on p.id = ap.partid
left join SQM_APQP_PROJECT j on j.id = a.projectid