大量的left join 连接造成了数据重复,求大神给点建议如何防重复
select distinct d.dept_name,a.dlm_no,a.creat_date,case mid.inv_attr_code when 1 then '常备' when 2 then '专购' end inv_attr_code,bb.inv_code,mid.inv_name,
mid.inv_model,sum(bb.amount),sum(mod.amount),mom.order_code,sum(mdd.amount),mwmm.iow_no ,cast(sum(isnull(mwd.amount,0)) as nvarchar(50))+'/'+cast(sum(isnull(mwdd.amount,0))as nvarchar(50))
from mate_dept_list_main as a --科室需求
left join mate_dept_list_detail bb on a.dlm_id=bb.dlm_id
left join mate_list_pi_relation mlpr on bb.detail_id=mlpr.list_detail_id
left join mate_dept_plan_detail mdpd on mlpr.gen_iow_id=mdpd.auto_id --科室需求计划明细表
left join mate_proplan_invrelat mpi on mpi.detail_req_id= mdpd.auto_id --科室需求计划与采购计划关联表
left join mate_stock_plan_detail mspd on mpi.detail_auto_id =mspd.auto_id --采购计划明细表
left join mate_stock_plan_main mspm on mspd.peri_code=mspm.peri_code and a.comp_code=mspd.comp_code and a.copy_code=mspd.copy_code --采购计划
left join mate_order_main mom on mom.peri_code=mspm.peri_code and a.comp_code=mspm.comp_code and a.copy_code=mspm.copy_code --采购订单
left join mate_order_detail mod on mom.peri_code=mod.peri_code and mod.comp_code=mom.comp_code and mod.copy_code=mom.copy_code --采购订单明细
left join mate_delivery_main mdm on mdm.order_code=mom.order_code and a.comp_code=mom.comp_code and a.copy_code=mom.copy_code--配送单
left join mate_delivery_detail mdd on mdm.delivery_id=mdd.delivery_id and a.comp_code=mdd.comp_code and a.copy_code=mdd.copy_code
-- --配送单到订单,新建一个中间表用来之间的关联
left join mate_delivery_whr_detail mdwd on mdm.delivery_id=mdwd.delivery_id and mdd.detail_id=mdwd.detail_id --配送单与入库单中间表
left join mate_whr_detail mwd on mwd.auto_id=mdwd.auto_id --入库单明细
left join mate_whr_main mwmm on mwd.iow_id=mwmm.iow_id --入库单主表
left join mate_whr_main mwm on mwm.order_code=mwd.iow_id and mwm.bus_type_code='11' and mwm.state='100'--查询与之对应的退货单
left join mate_whr_detail mwdd on mwm.iow_id=mwdd.iow_id--退货单数量
left join mate_inv_dict mid on bb.inv_code=mid.inv_code
left join sys_user as b on a.maker = b.user_code
left join sys_user as c on a.checker = c.user_code
left join sys_dept as d on a.dept_code = d.dept_code and a.comp_code=d.comp_code
left join sys_dept as s on a.dept_code = s.dept_code and a.comp_code=s.comp_code AND @para_vlaue = 1
where (@fromdate='' or dbo.pub_getDate(a.creat_date)>=dbo.pub_getDate(convert(datetime,@fromdate))) and
(@todate='' or dbo.pub_getDate(a.creat_date)<=dbo.pub_getDate(convert(datetime,@todate))) and
a.istate =90 and
a.comp_code=@comp_code and
a.copy_code =@copy_code and
mid.inv_attr_code='1' and
(@inv_name='' or mid.inv_code like @inv_name+'%' or mid.inv_name like '%'+@inv_name+'%' or mid.spell like '%'+@inv_name+'%') and
(@dlim_no='' or a.dlm_no like @dlim_no+'%' or a.dlm_no like '%'+@dlim_no+'%') and
(@deptcode = '' or d.dept_code = @deptcode )
group by d.dept_name,a.dlm_no,a.creat_date,mid.inv_attr_code ,bb.inv_code,mid.inv_name,mid.inv_model,mom.order_code,mwmm.iow_no
order by a.dlm_no DESC