sql 优化

来源：互联网发布：vscode 如何打开sln 编辑：程序博客网时间：2024/06/09 22:42

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

(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 ！谢谢支持！

阅读全文

0 0