select a.indentid,a.indent,a.merchantid,a.depotid,a.get_depotid,a.parentid,a.selltype,
a.backrate,a.cr_name,a.chk_name,a.sure_name,a.comment,
CONVERT(char(10), a.sure_date, 126) as sure_date,
CONVERT(char(10), a.getdate, 126) as getdate,
CONVERT(char(10), a.backdate, 126) as backdate,
SUBSTRING(CONVERT(char(10),
a.sure_date, 126), 1, 4) as sureyear,
SUBSTRING(CONVERT(char(10), a.sure_date, 126), 6, 2) as suremonth,
SUBSTRING(CONVERT(char(10), a.sure_date, 126), 9, 2) as sureday,
b.clothingid,b.styleid,b.colorid,b.sizeid,b.j_cost,b.j_price,b.x_price,b.discount,b.s_price,b.nums as nums,
b.comment as commentsub, b.nums*b.j_cost as jcostsums,b.nums*b.j_price as jpricesums,b.nums*b.x_price as xpricesums,
b.nums*b.s_price as spricesums,b.phnums,b.fhnums,e.shipei,e.shipeisums,
convert(char(10),a.cr_date,126) as cr_date
--into F22_1_f22_tmp.dbo.T1001#tmp201211051308086911001
from d_indent a
inner join d_indentsub b on a.indentid=b.indentid
left join (select a.indentid, b.clothingid,sum(case when a.ov_sure=0 then Isnull(b.nums,0) else Isnull(c.sellnum,0) end ) as shipei,
sum(case when a.ov_sure=0 then Isnull(b.nums*b.s_price,0) else Isnull(c.sellsums,0) end ) as shipeisums
from d_quota a inner join d_quotasub b on a.quotaid=b.quotaid
left join (select a.quotaid,b.clothingid,sum(b.set_nums)as sellnum,sum(b.set_nums*b.s_price)as sellsums
from d_sell a inner join d_sellsub b on a.sellid=b.sellid
where a.set_sure>0 Group by a.quotaid,b.clothingid )c on a.quotaid=c.quotaid and b.clothingid=c.clothingid
where a.sure>0
and a.get_depotid in (select a.depotid from F22_1_f22_power.dbo.power_dpt1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
and a.set_depotid in (select a.depotid from F22_1_f22_power.dbo.power_setdpt1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
and b.styleid in (select a.styleid from F22_1_f22_power.dbo.power_sty1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
Group by a.indentid,b.clothingid ) e
on e.indentid=a.indentid and e.clothingid=b.clothingid
where d_type=2 and a.sure>0
and convert(char(10),a.sure_date,126)
between '2012-03-01' and '2012-11-05'
and a.depotid in (select a.depotid from F22_1_f22_power.dbo.power_setdpt1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
and a.get_depotid in (select a.depotid from F22_1_f22_power.dbo.power_dpt1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
and b.styleid in (select a.styleid from F22_1_f22_power.dbo.power_sty1001 a where isnull(a.access,0)=1 and isnull(a.selection,0)=1 )
补货单综合查询表错误关联格式 inner 改left
