大量的left join 连接造成了数据重复，求大神给点建议如何防重复

TD-keys 2017-09-18 03:29:54

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

...全文

8260 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

bigcrazy 2017-09-20

打赏
举报

建议你先吃透各表的关系。然后考虑用图的方式，把需要查询内容用图形表示出来。把逻辑弄清楚后，写SQL语句就方便多了。

凡人皆侑一死 2017-09-20

打赏
举报

从重复的数据中找两组数据对比不同地方就是出现连接重复的字段。应该是左连接出现一个字段对应多个了

学海无涯-回头是岸 2017-09-20

打赏
举报

造成重复不是 left join的锅而是你join 前后的表不是一对一的关系。哪个地方不是一对一只能你自己去找

繁花尽流年 2017-09-19

打赏
举报

心好大，一个系统的出入库单子，一句话出来。不是不能这样做，前提是你是不是把各个单据表与表之间的关联关系完全吃透了？个人建议，新手上路最好听5#的建议，先一个个结果集慢慢来。

acen_chen 2017-09-19

打赏
举报

慢慢调，是哪个left join造成的，然后看是表数据问题还是连接问题

文盲老顾 2017-09-19

打赏
举报

重复数据基本上是在join时的关联数据一对多或多对多造成的比如

create table table1(id int identity,name varchar(max))
create table table2(id int identity,aid int,name varchar(max))
insert into table1(name) values('aaa'),('bbb')
insert into table2(aid,name) values(1,'aaa第一次'),(1,'aaa第二次'),(2,'bbb第一次')


select a.id,a.name,b.name from table1 a left join table2 b on a.id=b.aid

所以，在具有已对多或者多对多的时候，使用cross apply比join更灵活一些

顺势而为1 2017-09-18

打赏
举报

采购,订单,入库,出库..... 这些段段写, 主表与明细表先 Join , 再全部连起来.

OwenZeng_DBA 2017-09-18

打赏
举报

引用楼主 luomoxiaoxigua 的回复:

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

这个语句join的表太多了，建议拆解下。1，可以让逻辑更清晰点，2.有利于生成合理的执行计划。然后重复的数据最好把重复的数据截图来看看。是哪些数据你认为重复了。