用户y7163732(提问者) 回复 axingif not object_id(\'Tempdb..#rk\') is null drop table #rk
select gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid,sum(b.nums) 入库数,c.s_name
into #rk
from dbo.红人总表2020 a
--left join d_Indepotsub b on a.styleid=b.styleid and a.color_id=b.colorid
left join b_style c on a.styleid = c.styleid
left join u_indepotstyle d on c.id = d.style_id
left join u_Indepotsub b on d.id = b.style_code
--left join u_indepot f on f.id = b.code
left join b_clothing e on b.clothing_id = e.id and a.color_id = e.colorid
where colorid is not null and b.nums is not null
and a.st_bd <> \'追单\'
group by gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid,c.s_name
if not object_id(\'Tempdb..#ss\') is null drop table #ss
select gys,a.styleid,a.st_bd,xl,st_name,color_name,a.color_id,min(f.sure_date) 上市日期
into #ss
from dbo.红人总表2020 a
--left join d_Sellsub b on a.styleid=b.styleid and a.color_id=b.colorid
--left join d_sell c on b.sellid=c.sellid
left join b_style c on a.styleid = c.styleid
left join u_sellstyle d on c.id = d.style_id
left join u_sellsub b on d.id = b.style_code
left join b_clothing e on b.clothing_id = e.id and a.color_id = e.colorid
left join u_sell f on b.code=f.id
where f.sure=\'1\' and f.states = \'2\'
--and a.st_bd <> \'追单\'
group by gys,a.styleid,a.st_bd,xl,st_name,color_name,a.color_id
if not object_id(\'Tempdb..#zy\') is null drop table #zy
select gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid,sum(b.nums) 自营
into #zy
from dbo.红人总表2020 a
left join b_style c on a.styleid = c.styleid
left join u_retailstyle d on c.id = d.style_id
left join u_retailsub b on d.id = b.style_code
left join b_clothing e on b.clothing_id = e.id and a.color_id = e.colorid
left join u_retail f on b.code=f.id
left join b_depot g on f.depot_id = g.id
where f.sure=\'1\' and f.states = \'2\'
--and c.setdate>=\'2015-01-01\'
--and c.setdate-1<\'2019-05-22\'
and substring(g.depotid,2,1) in (\'1\',\'3\')
and c.st_type<>\'买单\' and c.st_type<>\'饰品\'
and a.st_bd <> \'追单\'
group by gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid
if not object_id(\'Tempdb..#kh\') is null drop table #kh
select gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid,sum(b.nums) 客户
into #kh
from dbo.红人总表2020 a
left join b_style c on a.styleid = c.styleid
left join u_retailstyle d on c.id = d.style_id
left join u_retailsub b on d.id = b.style_code
left join b_clothing e on b.clothing_id = e.id and a.color_id = e.colorid
left join u_retail f on b.code=f.id
left join b_depot g on f.depot_id = g.id
where f.sure=\'1\' and f.states = \'2\'
--and c.setdate>=\'2015-01-01\'
--and c.setdate-1<\'2019-05-22\'
and substring(g.depotid,2,1) =\'2\'
and c.st_type<>\'买单\' and c.st_type<>\'饰品\'
and a.st_bd <> \'追单\'
group by gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid
if not object_id(\'Tempdb..#sd\') is null drop table #sd
select gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid,sum(b.nums) 山东
into #sd
from dbo.红人总表2020 a
left join b_style c on a.styleid = c.styleid
left join u_retailstyle d on c.id = d.style_id
left join u_retailsub b on d.id = b.style_code
left join b_clothing e on b.clothing_id = e.id and a.color_id = e.colorid
left join u_retail f on b.code=f.id
left join b_depot g on f.depot_id = g.id
where f.sure=\'1\' and f.states = \'2\'
--and c.setdate>=\'2015-01-01\'
--and c.setdate-1<\'2019-05-22\'
and g.depotid like \'25371%\'
and c.st_type<>\'买单\' and c.st_type<>\'饰品\'
and a.st_bd <> \'追单\'
group by gys,a.styleid,a.st_bd,xl,st_name,color_name,e.colorid
select distinct a.gys,a.styleid,a.st_bd,a.xl,a.s_name,co.colorname,co.colorid,a.入库数,f.上市日期,
b.自营,c.客户,d.山东
from #rk a
left join #zy b on a.styleid=b.styleid and a.colorid=b.colorid
left join #kh c on a.styleid=c.styleid and a.colorid=c.colorid
left join #sd d on a.styleid=d.styleid and a.colorid=d.colorid
--left join [f117picture].[dbo].[j_stylecol] e on a.styleid=e.styleid and a.color_id=e.colorid
left join #ss f on a.styleid=f.styleid and a.colorid=f.color_id
left join b_style g on a.styleid=g.styleid
left join b_color co on a.colorid=co.colorid
WHERE
--g.st_year=\'${year}\'
g.st_year=\'2020\'
--and g.st_month in (\'${month}\')
and g.st_month in (\'冬季\')
1min目标场景问卷
立即参与
