我写了一个sql最后的数据集 执行出来 需要 很长时间 三四分钟 其实 就只有 8000多条数据 请大佬看一下 这个 sql 可不可以优化一下 提高一下 查询效率--静态表处理 多列变成一列 抛出大类WITH GA AS (SELECT DISTINCT DESIGNKCODE,BEXTSTR1 AS EXTSTR1,YEAR,BIG_TYPE,mc_fl1,COST FROM DW_product020 WHERE BEXTSTR1 IS NOT NULL UNION SELECT DISTINCT DESIGNKCODE,VEXTSTR1,YEAR,BIG_TYPE,mc_fl1,COST FROM DW_product020 WHERE VEXTSTR1 IS NOT NULL UNION SELECT DISTINCT DESIGNKCODE,CEXTSTR1,YEAR,BIG_TYPE,mc_fl1,COST FROM DW_product020 WHERE CEXTSTR1 IS NOT NULL ),--九光档案表处理 GB AS ( SELECT DISTINCT designkcode,ExtStr1,BM ,klengthnm as 'YEAR',CAST(ISNULL(DJ_PF1,0) AS DECIMAL(18,2)) AS Kcost,PP,MC_FL1 FROM DW_BoolProductsFile WHERE MC_SPFL2 <> '未通过' AND PP IN ('芭欧','ANAN','MIX GEORA','VY','薇薇卡','小侨（jofay）') AND MC_FL1 <> '样衣' AND MC_FL1 NOT LIKE '%男装%' AND MC_FL1 <> '礼品' AND MC_FL1 <> '其他' AND BM <> 'V16XA1062' ),--找出 档案表中 顶层码一样 成本不一样的数据 GC AS (SELECT T1.DESIGNKCODE, T1.EXTSTR1, T1.BM, T1.YEAR, T1.KCOST, T1.PP, T1.MC_FL1 FROM GB T1 JOIN GB T2 ON T1.DESIGNKCODE = T2.DESIGNKCODE AND T1.KCOST <> T2.KCOST ),--在总表过滤掉 顶层码一样 成本不一样的数据 GD AS (SELECT * FROM GB WHERE DESIGNKCODE NOT IN (SELECT DISTINCT DESIGNKCODE FROM GC)),--将 顶层码一样 成本不一样的这部分 数据 不一样的成本 取最小值 GE AS (select a.DESIGNKCODE ,a.EXTSTR1,a.bm,a.YEAR,b.Mcost as kcost,a.pp,a.MC_FL1 from GC a left join (select distinct DESIGNKcode AS DESIGNKCODE_I , min(Kcost) Mcost from GC group by DESIGNKcode) b on a.DESIGNKCODE=b.DESIGNKcode_I ),--将以过滤的数据 与 源数据放到一起 GF AS (SELECT * FROM GD UNION SELECT * FROM GE UNION SELECT * FROM GB WHERE DESIGNKCODE IS NULL),--静态表 与 档案表 表关联 GG AS (SELECT GF.designkcode AS DESIGNKCODE_I,GF.ExtStr1 AS EXTSTR1_I,GF.YEAR AS YEAR_I,GF.MC_FL1 AS MC_FL1_I,GF.Kcost,GA.designkcode,GA.ExtStr1,GA.YEAR,GA.MC_FL1,GA.COST FROM GF LEFT JOIN GA ON GF.DESIGNKCODE =GA.DESIGNKCODE AND GF.ExtStr1 =GA.ExtStr1 ),--过滤掉静态表designkcode为空的数据 GH AS (SELECT * FROM GG WHERE DESIGNKCODE IS NOT NULL),--静态表DESIGNKCODE为空的所有档案表的数据 第一部分的模块 GI AS (SELECT DESIGNKCODE_I AS DESIGNKCODE,EXTSTR1_I AS EXTSTR1,YEAR_I AS YEAR,MC_FL1_I MC_FL1,KCOST AS COST FROM GG WHERE DESIGNKCODE IS NULL),--静态表 没有关联上 档案表的数据(档案表所有的数据已经关联进去了) 第二部分的模块 GJ AS (SELECT DESIGNKCODE,EXTSTR1,YEAR,MC_FL1,COST FROM GA WHERE NOT EXISTS ( SELECT DISTINCT DESIGNKCODE FROM GG WHERE EXISTS (SELECT DISTINCT DESIGNKCODE FROM GA))),--顶层码一致 但是 成本不一致的数据 GK AS ( SELECT T1.DESIGNKCODE_I, T1.EXTSTR1_I, T1.YEAR_I, T1.MC_FL1_I, T1.KCOST, T1.DESIGNKCODE, T1.EXTSTR1, T1.YEAR, T1.MC_FL1, T1.COST FROM GH T1 JOIN GH T2 ON T1.DESIGNKCODE_I = T2.DESIGNKCODE AND T1.KCOST <> T2.COST ),--将成本不同的数据 以 GB的成本 为标准同化GA的成本数据 GL AS (SELECT *,(CASE WHEN KCOST <> COST THEN KCOST ELSE COST END) AS COST_I FROM GK),--取出以GB成本为标准的数据 第三部分的模块 GM AS (SELECT DESIGNKCODE_I AS DESIGNKCODE,EXTSTR1_I AS EXTSTR1,YEAR_I AS YEAR,MC_FL1_I AS MC_FL1,KCOST AS COST FROM GL),--取出 非 顶层码一致 但是 成本不一致的数据 第四部分的模块 GP AS (select DESIGNKCODE_I AS DESIGNKCODE,EXTSTR1_I AS EXTSTR1,YEAR_I AS YEAR,MC_FL1_I AS MC_FL1,KCOST AS COST FROM GH WHERE DESIGNKCODE_I NOT IN (SELECT DISTINCT DESIGNKCODE_I FROM GK)),--将前边的几个模块组合 GQ AS (SELECT * FROM GI UNION SELECT * FROM GJ UNION SELECT * FROM GM UNION SELECT * FROM GP) SELECT * FROM GQ
