liyaowen505
一杯星空一个世界
2019-02-20 19:07

对于取多个枚举字段,select 中带子查询 和 全部用left join 哪个好呢?

  • oracle
  • sql

我们有一个枚举表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
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

3条回答

为你推荐

换一换