一杯星空一个世界 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都可以

    评论

报告相同问题?

悬赏问题

  • ¥15 flink cdc无法实时同步mysql数据
  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名