|
楼主 |
发表于 2019-4-18 10:54
|
显示全部楼层
Sql = "select t2.重庆仓库库存,t3.重庆门店库存,t4.重庆门店寄存,t5.重庆本月销量,t6.重庆上月销量,t7.南充仓库库存,t8.南充门店库存,t9.南充门店寄存,t10.南充本月销量,t11.南充上月销量,t12.达州仓库库存,t13.达州门店库存,t14.达州门店寄存,t15.达州本月销量,t16.达州上月销量 from (((((((((((((((select 商品条码 from [数据分析表$])t1 left join " & _
"(select 条码,总库存数 as 重庆仓库库存 from [库存汇总$] where 仓库 = 'B重庆仓库CQ')t2 on t1.商品条码= t2.条码) left join " & _
"(select 条码,sum(总库存数) as 重庆门店库存 from [库存汇总$] where 仓库 like '%CQ%' and 仓库<>'B重庆仓库CQ' group by 条码)t3 on t1.商品条码 = t3.条码) left join " & _
"(select 条码,sum(寄存数) as 重庆门店寄存 from [库存汇总$] where 仓库 like '%CQ%' group by 条码)t4 on t1.商品条码=t4.条码) left join " & _
"(select 条码,sum(实际数量) as 重庆本月销量 from [本月销量$] where 门店名称 like '%CQ%' and 门店名称<>'B重庆仓库CQ' group by 条码)t5 on t1.商品条码=t5.条码) left join " & _
"(select 条码,sum(实际数量) as 重庆上月销量 from [上月销量$] where 门店名称 like '%CQ%' and 门店名称<>'B重庆仓库CQ' group by 条码)t6 on t1.商品条码=t6.条码) left join " & _
"(select 条码,总库存数 as 南充仓库库存 from [库存汇总$] where 仓库 = 'B南充仓库NC')t7 on t1.商品条码= t7.条码) left join " & _
"(select 条码,sum(总库存数) as 南充门店库存 from [库存汇总$] where 仓库 like '%NC%' and 仓库<>'B南充仓库NC' group by 条码)t8 on t1.商品条码 = t8.条码) left join " & _
"(select 条码,sum(寄存数) as 南充门店寄存 from [库存汇总$] where 仓库 like '%NC%'and 仓库<>'B南充仓库NC' group by 条码)t9 on t1.商品条码=t9.条码) left join " & _
"(select 条码,sum(实际数量) as 南充本月销量 from [本月销量$] where 门店名称 like '%NC%' and 门店名称<>'B南充仓库NC' group by 条码)t10 on t1.商品条码=t10.条码) left join " & _
"(select 条码,sum(实际数量) as 南充上月销量 from [上月销量$] where 门店名称 like '%NC%' and 门店名称<>'B南充仓库NC' group by 条码)t11 on t1.商品条码=t11.条码) left join " & _
"(select 条码,总库存数 as 达州仓库库存 from [库存汇总$] where 仓库 = 'A达州总仓库DZ')t12 on t1.商品条码= t12.条码) left join " & _
"(select 条码,sum(总库存数) as 达州门店库存 from [库存汇总$] where 仓库 like '%DZ%' and 仓库<>'A达州总仓库DZ' group by 条码)t13 on t1.商品条码 = t13.条码) left join " & _
"(select 条码,sum(寄存数) as 达州门店寄存 from [库存汇总$] where 仓库 like '%DZ%'and 仓库<>'A达州总仓库DZ' group by 条码)t14 on t1.商品条码=t14.条码) left join " & _
"(select 条码,sum(实际数量) as 达州本月销量 from [本月销量$] where 门店名称 like '%DZ%' and 门店名称<>'A达州总仓库DZ' group by 条码)t15 on t1.商品条码=t15.条码) left join " & _
"(select 条码,sum(实际数量) as 达州上月销量 from [上月销量$] where 门店名称 like '%DZ%' and 门店名称<>'A达州总仓库DZ' group by 条码)t16 on t1.商品条码=t16.条码"
Sheets("数据分析表").Select
'Debug.Print Sql
[I2].CopyFromRecordset cnn.Execute(Sql)
cnn.Close
Set cnn = Nothing
|
|