去百度上 查过了  是字段的类型转换的时候 出错了 然后百度上的案例没看懂 有大佬指点一下嘛

SQL server 数据库 执行sql 如下：

--库存数据的处理 以及与基础信息关联
--库存基础数据表取字段

with AA AS (select
            distinct designkcode,YEAR AS YEARS,ExtStr1,BM AS CODE,PP,MC_FL1 AS TYPE,BIG_TYPE,Cost
   from STATIC_stateSTATE_TABLE_I
   WHERE BM not in (select BM from STATIC_stateSTATE_TABLE_I where PP = '芭欧' and BM like 'A%')
      ),

--过滤库存数据

     AB AS (SELECT * from kucun where ck_name in ('芭欧成品天猫仓','芭欧outlets仓','芭欧成品唯品会仓','芭欧成品云集仓','芭欧成品好衣库仓','芭欧成品爱库存仓','芭欧拼多多仓',
                                               '芭欧代代购','芭欧成品环球捕手仓','芭欧成品尼克公主仓','薇薇卡成品天猫仓','薇薇卡成品唯品会仓',
              '薇薇卡成品云集仓','薇薇卡成品好衣库仓','薇薇卡成品爱库存仓','薇薇卡成品outlets仓',
              'COCO唯品会成品共享仓','COCO成品云集仓','小侨成品共享仓','薇娅成品共享仓','安安成品仓_共享仓')
        ),
  
--调整库存数据字段内容

     AC AS (SELECT *,(case CK_Name when '芭欧成品天猫仓' then 'B天猫'
                                   when '芭欧outlets仓' then 'B奥莱'
                    when '芭欧成品唯品会仓' then 'B唯品'
                    when '芭欧成品云集仓' then 'B云集'
                    when '芭欧成品好衣库仓' then 'B好衣库'
                       when '芭欧成品爱库存仓' then 'B爱库存'
                    when '芭欧拼多多仓' then 'B拼多多'
                    when '芭欧代代购' then 'B代代购'
                    when '芭欧成品环球捕手仓' then 'B环球捕手'
                    when '芭欧成品尼克公主仓' then 'B尼克公主'
                    when '薇薇卡成品天猫仓' then 'V天猫'
                    when '薇薇卡成品唯品会仓' then 'V唯品'
                    when '薇薇卡成品云集仓' then 'V云集'
                    when '薇薇卡成品好衣库仓' then 'V好衣库'
                    when '薇薇卡成品爱库存仓' then 'V爱库存'
                    when '薇薇卡成品outlets仓' then 'V奥莱'
                    when 'COCO唯品会成品共享仓' then 'C唯品'
                    when 'COCO成品云集仓' then 'C云集'
                    when '小侨成品共享仓' then '小侨'
                    when '薇娅成品共享仓' then '薇娅'
                    when '安安成品仓_共享仓' then '安安'
                    else '其他仓' end     
           ) as 'CK_Name_II' from AB),

--调整库存数据字段内容

  AD AS (SELECT *,(case CK_Name_II when 'B天猫' then 'B仓'
                                   when 'B奥莱' then 'B仓'
                    when 'B唯品' then 'B仓'
                    when 'B云集' then 'B仓'
                    when 'B好衣库' then 'B仓'
                       when 'B爱库存' then 'B仓'
                    when 'B拼多多' then 'B仓'
                    when 'B代代购' then 'B仓'
                    when 'B环球捕手' then 'B仓'
                    when 'B尼克公主' then 'B仓'
                    when 'V天猫' then 'V仓'
                    when 'V唯品' then 'V仓'
                    when 'V云集' then 'V仓'
                    when 'V好衣库' then 'V仓'
                    when 'V爱库存' then 'V仓'
                    when 'V奥莱' then 'V仓'
                    when 'C唯品' then 'C仓'
                    when 'C云集' then 'C仓'
           when '小侨' then 'XQ仓'
                    when '薇娅' then 'VY仓'
                    when '安安' then 'ANAN仓'
                    else 'E仓' end     
           ) as 'CK_Name_III' from AC
     ),

--过滤掉特殊商品编码

  AE AS (SELECT * FROM AD WHERE CODE NOT LIKE 'WJ%' AND CODE NOT LIKE 'YD%' AND CODE NOT LIKE 'FCD%' AND CODE NOT LIKE 'KF%' AND CODE NOT LIKE 'ST%' AND CODE NOT LIKE 'TZ%' ),

--将库存数据与基础数据 放在一起

  AF AS (select distinct
            A.designkcode,A.YEARS,A.extStr1,A.code,E.SKUcode,A.PP,E.color,E.size,A.cost,A.TYPE,A.BIG_TYPE,
            E.CK_Name_II,E.DiaoBoZhanYong,E.KeXiao,E.ZaiTu,E.CK_Name_III
            from
   AE E LEFT JOIN AA A
            ON E.CODE = A.extStr1),

--顶层码数据为空时 加入唯品货号得数据

     AG AS (SELECT *,(CASE WHEN DESIGNKCODE IS NULL THEN ExtStr1 ELSE DESIGNKCODE END) AS DESIGNKCODE_I FROM AF),

--大类数据为空时 加入细类得数据

     AH AS (SELECT *,(CASE WHEN BIG_TYPE IS NULL THEN TYPE ELSE BIG_TYPE END) AS BIG_TYPE_I FROM AG),

--合并单元格数据，做唯一值

     AL AS (select DESIGNKCODE_I AS DESIGNKCODE,
                YEARS,EXTSTR1,CODE,SKUCODE,PP,COLOR,SIZE,TYPE,
       BIG_TYPE_I AS BIG_TYPE,CK_NAME_II,DIAOBOZHANYONG,KEXIAO,ZAITU,CK_NAME_III,
       concat(designkcode_I,color,size) as 'WeiYiZhi'from AH),

--销量数据的处理 以及与基础信息的关联
--销量对基础信息 取数

     CA AS (select
            distinct designkcode,YEAR AS YEARS,ExtStr1,BM AS CODE,PP,MC_FL1 AS TYPE,BIG_TYPE,Cost
   from STATIC_stateSTATE_TABLE_I
      ),

--对销量数据表的店铺内容进行过滤

     CC as (select * from JinQiXiaoLiang_I where DianPu in ('芭欧服饰旗舰店','芭欧outlets店','薇薇卡旗舰店','芭欧唯品会','薇薇卡唯品会','COCO唯品会','芭欧云集POP店','芭欧云集B2B平台',
                                                   '薇薇卡云集POP店','COCO云集POP店','芭欧爱库存店','薇薇卡渠道店','芭欧渠道店','芭欧甩宝店','薇薇卡渠道2店','薇薇卡甩宝店',
                                       '芭欧拼多多店','芭欧拼多多个人店','芭欧芙蓉拼多多店','薇薇卡拼多多店','芭欧环球捕手店','海宁英蔓时装贸易有限公司',
               '海宁市慕名服饰有限公司','bool芭欧芙蓉专卖店','bool芭欧聚上聚专卖店', '芭欧淘小铺','尼可公主','薇娅高端定制皮草店','薇娅皮草店',
               '薇娅直播C店','芭欧薇娅店','小侨jofay','安安妖踪淘宝店')
            ),

--将销量数据得两个表合并

     CD as (select RiQi,DianPu,ExtStr1,skucode,XiaoLiang,Color,Size from CC
            union
            select RiQi,DianPu,ExtStr1,skucode,XiaoLiang,substring(GUIGEMINGCHENG,1,charindex(' ',GUIGEMINGCHENG)-1) AS Color,substring(GUIGEMINGCHENG,charindex(' ',GUIGEMINGCHENG),4) AS Size
   from JinQiXiaoLiang_II
   where DianPu IN ('芭欧服饰旗舰店','芭欧outlets店','薇薇卡旗舰店','芭欧唯品会','薇薇卡唯品会','COCO唯品会','芭欧云集POP店',
                    '芭欧云集B2B平台','薇薇卡云集POP店','COCO云集POP店','芭欧爱库存店','薇薇卡渠道店','芭欧渠道店',
        '芭欧甩宝店','薇薇卡渠道2店','薇薇卡甩宝店','芭欧拼多多店','芭欧拼多多个人店','芭欧芙蓉拼多多店',
        '薇薇卡拼多多店','芭欧环球捕手店','海宁英蔓时装贸易有限公司','海宁市慕名服饰有限公司',
        'bool芭欧芙蓉专卖店','bool芭欧聚上聚专卖店','芭欧淘小铺','尼可公主','薇娅高端定制皮草店','薇娅皮草店',
        '薇娅直播C店','芭欧薇娅店','小侨jofay','安安妖踪淘宝店')
             ),
 
--定义销量数据店铺名字

     CE as (SELECT *,(case DianPu when '芭欧服饰旗舰店' then 'B天猫'
                                  when '芭欧outlets店' then 'outlets'
                   when '薇薇卡旗舰店' then 'V天猫'
                   when '芭欧唯品会' then 'B唯品会'
                   when 'COCO唯品会' then 'C唯品会'
                   when '芭欧云集POP店' then 'B云集'
                   when '芭欧云集B2B平台' then 'B云集'
                   when '薇薇卡云集POP店 ' then 'V云集'
                   when 'COCO云集POP店' then 'C云集'
                   when '芭欧爱库存店' then 'B爱库存'
                   when '薇薇卡渠道店' then 'V爱库存'
                   when '芭欧渠道店' then 'B好衣库'
                   when '芭欧甩宝店' then 'B好衣库'
                   when '薇薇卡渠道2店' then 'V好衣库'
                   when '薇薇卡甩宝店' then 'V好衣库'
                   when '芭欧拼多多店' then 'B拼多多'
                   when '芭欧拼多多个人店' then 'B拼多多'
                   when '芭欧芙蓉拼多多店' then 'B芙蓉拼多多'
                   when '薇薇卡拼多多店' then 'V拼多多'
                   when '芭欧环球捕手店' then 'B环球捕手'
                   when '海宁英蔓时装贸易有限公司' then 'B英蔓慕名'
                   when '海宁市慕名服饰有限公司' then 'B英蔓慕名'
                   when 'bool芭欧芙蓉专卖店' then 'B芙蓉专卖店'
                   when 'bool芭欧聚上聚专卖店' then 'B聚上聚专卖店'
                   when '芭欧淘小铺' then 'B淘小铺'
                   when '尼可公主' then '尼可公主'
                   when '薇娅高端定制皮草店' then '薇娅'
                   when '薇娅皮草店' then '薇娅'
                   when '薇娅直播C店' then '薇娅'
                   when '芭欧薇娅店' then '薇娅'
                   when '小侨jofay' then '小侨'
                   when '安安妖踪淘宝店' then '安安'
                   else '其他店铺' end     
                                  ) as 'DianPu_II' from CD
      ),

--过滤掉一些唯品编码

     CF AS (SELECT * FROM CE WHERE ExtStr1 NOT LIKE 'WJ%' AND ExtStr1 NOT LIKE 'YD%' AND ExtStr1 NOT LIKE 'FCD%' AND ExtStr1 NOT LIKE 'KF%' AND ExtStr1 NOT LIKE 'ST%' AND ExtStr1 NOT LIKE 'TZ%'),
       
--将 基础信息表与销量信息表 关联

  CG AS (SELECT A.DESIGNKCODE,A.ExtStr1,A.CODE,F.SKUCODE,A.PP,A.TYPE,A.BIG_TYPE,A.COST,F.Color,F.Size,F.RIQI,F.XIAOLIANG,F.DIANPU_II
         FROM CF F LEFT JOIN CA A
   ON F.ExtStr1 = A.ExtStr1
      ),

--找出 没有关联上库存的基本信息的销量的数据

     CH AS (select * FROM CF WHERE EXTSTR1 NOT IN (SELECT EXTSTR1 FROM CA)),

--给 CH 的数据 关联基本信息

     CI AS (select
         D.DESIGNKCODE,D.EXTSTR1,D.BM AS CODE,H.SKUCODE,D.PP,D.MC_FL1 AS TYPE,CAST(ISNULL(D.DJ_PF1,0) AS DECIMAL(18,0)) AS Cost,H.COLOR,H.SIZE,H.RIQI,H.XIAOLIANG,H.DIANPU_II
         FROM
         CH H LEFT JOIN DW_BoolProductsFile D
         ON H.EXTSTR1 = D.EXTSTR1),

--关联大类

     CJ AS (select
         I.DESIGNKCODE,I.EXTSTR1,I.CODE,I.SKUCODE,I.PP,I.TYPE,T.BIG_TYPE,I.Cost,I.COLOR,I.SIZE,I.RIQI,I.XIAOLIANG,I.DIANPU_II
         FROM
         CI I LEFT JOIN TYPE_II T
         ON I.TYPE = T. TYPE),

--与销量的主表数据放到一起

     CK AS (select * from CG
            UNION
            SELECT * FROM CJ),

--顶层码数据为空时 加入唯品货号得数据

     CL AS (SELECT *,(CASE WHEN DESIGNKCODE IS NULL THEN ExtStr1 ELSE DESIGNKCODE END) AS DESIGNKCODE_I FROM CK),

--大类数据为空时 加入细类得数据

     CM AS (SELECT *,(CASE WHEN BIG_TYPE IS NULL THEN TYPE ELSE BIG_TYPE END) AS BIG_TYPE_I FROM CL),

--过滤掉一些商品编码

     CN AS (SELECT * FROM CM WHERE CODE not in (select CODE from CI where PP = '芭欧' and CODE like 'A%')),

--做唯一值

     CO AS (select TOP 100 PERCENT  * , concat(designkcode_I,Color,size) as 'WeiYiZhi'from CN WHERE XIAOLIANG IS NOT NULL ORDER BY RIQI DESC),

--求出近七天销量和

  CP AS (select DISTINCT WEIYIZHI,SKUCODE,EXTSTR1,DIANPU_II,sum(xiaoliang)AS XIAOLIANG7 from CO where RIQI > GETDATE() - 7 group by WEIYIZHI,SKUCODE,EXTSTR1,DIANPU_II),

--将近七天销量和得数据 与主表数据关联

     CQ AS (SELECT DISTINCT
         O.DESIGNKCODE_I,O.EXTSTR1,O.CODE,O.SKUCODE,O.PP,O.TYPE,O.BIG_TYPE_I,O.COST,O.Color,O.Size,O.WEIYIZHI,
         P.DIANPU_II,P.XIAOLIANG7
      FROM CO O LEFT JOIN CP P
   ON O.WEIYIZHI = P.WEIYIZHI AND O.SKUCODE = P.SKUCODE AND O.EXTSTR1 = P.EXTSTR1),

--求出近三十天销量和

     CR AS (select DISTINCT WEIYIZHI,SKUCODE,EXTSTR1,DIANPU_II,sum(xiaoliang)AS XIAOLIANG30 from CO where RIQI > GETDATE() - 30 group by WEIYIZHI,SKUCODE,EXTSTR1,DIANPU_II),

--将近三十天销量和得数据 与主表数据关联

     CS AS (SELECT DISTINCT
         Q.DESIGNKCODE_I AS DESIGNKCODE,Q.EXTSTR1,Q.CODE,Q.SKUCODE,Q.PP,Q.TYPE,Q.BIG_TYPE_I AS BIG_TYPE,Q.COST,Q.Color,Q.Size,Q.WEIYIZHI,Q.DIANPU_II,Q.XIAOLIANG7,
         R.DIANPU_II AS DIANPU_III,R.XIAOLIANG30
   FROM CQ Q LEFT JOIN CR R
      ON Q.WEIYIZHI = R.WEIYIZHI AND Q.SKUCODE = R.SKUCODE AND Q.EXTSTR1 = R.EXTSTR1),

--将销量数据里有 但是库存数据里没有的数据 放入库存数据表中；将库存数据里有 但是销量数据表里没有的数据 放入销量数据表中
--在库存表里 不在销量表里的唯品货号数 1100

     EA AS (SELECT * FROM AL WHERE EXTSTR1 NOT IN (select EXTSTR1 FROM CS WHERE EXTSTR1 IS NOT NULL)),

--在销量表里 不在库存表的唯品货号数   297

     EB AS (SELECT * FROM CS WHERE EXTSTR1 NOT IN (select DISTINCT EXTSTR1 FROM AL WHERE EXTSTR1 IS NOT NULL)),

--将库存数据里有 但是销量数据表里没有的数据 放入销量数据表中 9455 4826

  EC AS (SELECT DESIGNKCODE,EXTSTR1,CODE,SKUCODE,PP,TYPE,BIG_TYPE,COST,COLOR,SIZE,WEIYIZHI,DIANPU_II,XIAOLIANG7,DIANPU_III,XIAOLIANG30 FROM CS
         union ALL
            SELECT DESIGNKCODE,EXTSTR1,CODE,SKUCODE,PP,TYPE,BIG_TYPE,'',COLOR,SIZE,WEIYIZHI,'','','','' FROM EA)

--将销量数据里有 但是库存数据里没有的数据 放入库存数据表中 13870 623

SELECT DESIGNKCODE,YEARS,EXTSTR1,CODE,SKUCODE,PP,TYPE,BIG_TYPE,COLOR,SIZE,CK_NAME_II,DIAOBOZHANYONG,KEXIAO,ZAITU,CK_NAME_III,WEIYIZHI FROM AL
UNION ALL
SELECT DESIGNKCODE,'',EXTSTR1,CODE,SKUCODE,PP,TYPE,BIG_TYPE,COST,COLOR,SIZE,WEIYIZHI,'','','','' FROM EB