一杯星空一个世界 2019-02-20 19:07 采纳率: 0%
浏览 1836

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

我们有一个枚举表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条回答 默认 最新

  • hmbW670 2019-02-20 19:08
    关注

    带子查询 和 left join都可以

    评论

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波
  • ¥15 针对曲面部件的制孔路径规划,大家有什么思路吗