sql 优化

最新推荐文章于 2024-09-14 18:42:05 发布

lxqnl1314

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量174

点赞数

分类专栏： sql 文章标签： sql 优化

本文链接：https://blog.csdn.net/lxqnl1314/article/details/72876773

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这是取两个数据的并集，通常我们这么写，看着太臃肿了，心烦，试着优化了一下，请看优化后的

(SELECT  M.G_ID,
               M.G_RSCNAME,
               M.G_RSCDISPLAYNAME,
               M.G_RSCCODE,
               M.G_RSCVERSION,
               M.G_RSCURL,
               M.G_RSCTYPE,
               M.G_ISAUTH,
               M.G_SORT,
               M.G_DEVELOPER,
               M.G_APPLYSTATE,
               E.G_EMPLOYEENAME AS G_CREATUSER,
               M.G_CREATTIME,
               M.G_UPDATEUSER,
               M.G_REMARK,
               M.G_UPDATETIME,
               M.G_ISQUICK,
               M.G_CREATUSER CREATEUSERID
 FROM JCS.T_RESOURCE M LEFT JOIN JCS.T_EMPLOYEE E ON M.G_CREATUSER = E.G_ID
 WHERE M.G_DELSTATE = '0' AND m.G_RSCTYPE = '1')
UNION
(SELECT  M.G_ID,
               M.G_RSCNAME,
               M.G_RSCDISPLAYNAME,
               M.G_RSCCODE,
               M.G_RSCVERSION,
               M.G_RSCURL,
               M.G_RSCTYPE,
               M.G_ISAUTH,
               M.G_SORT,
               M.G_DEVELOPER,
               M.G_APPLYSTATE,
               E.G_EMPLOYEENAME AS G_CREATUSER,
               M.G_CREATTIME,
               M.G_UPDATEUSER,
               M.G_REMARK,
               M.G_UPDATETIME,
               M.G_ISQUICK,
               M.G_CREATUSER CREATEUSERID
 FROM JCS.T_RESOURCE M
      LEFT JOIN JCS.T_EMPLOYEERES er ON er.G_RESID = M.G_ID
      LEFT JOIN JCS.T_EMPLOYEE E ON er.G_EMPLOYEEID = E.G_ID
 WHERE M.G_DELSTATE = '0' AND M.G_RSCTYPE = '1' AND er.G_EMPLOYEEID = 'JCT012016050609403600000001')

优化后的结果

SELECT   M.G_ID,
               M.G_RSCNAME,
               M.G_RSCDISPLAYNAME,
               M.G_RSCCODE,
               M.G_RSCVERSION,
               M.G_RSCURL,
               M.G_RSCTYPE,
               M.G_ISAUTH,
               M.G_SORT,
               M.G_DEVELOPER,
               M.G_APPLYSTATE,
               E.G_EMPLOYEENAME AS G_CREATUSER,
               M.G_CREATTIME,
               M.G_UPDATEUSER,
               M.G_REMARK,
               M.G_UPDATETIME,
               M.G_ISQUICK,
               M.G_CREATUSER CREATEUSERID
 FROM JCS.T_RESOURCE M LEFT JOIN JCS.T_EMPLOYEE E ON M.G_CREATUSER = E.G_ID
  LEFT JOIN JCS.T_EMPLOYEERES er ON er.G_EMPLOYEEID='JCT012016050609403600000001' and  M.G_ID = er.G_RESID 
      
 WHERE M.G_DELSTATE = '0' AND m.G_RSCTYPE = '1' or M.G_ID = er.G_RESID

是不是爽多了，easy is beautiful ！谢谢支持！