[GS知识]发运单生成重复数据查询

--发运单生成重复的单子
declare @t table (SHIPORDERITEMS_LYCode varchar(20),SHIPORDERITEMS_LYFLID varchar(10),num int)
insert into @t
select a.SHIPORDERITEMS_LYCode,a.SHIPORDERITEMS_LYFLID,sum(num) as num
from (
	  select SHIPORDERITEMS_LYCode,SHIPORDERITEMS_LYFLID,1 as num
	  from SHIPORDERITEMS 
     ) as a
group by a.SHIPORDERITEMS_LYCode,a.SHIPORDERITEMS_LYFLID 
having sum(num) > 1


--select * from SHIPORDERITEMS where SHIPORDERITEMS_LYCode='BOL202003160010'


select a.SHIPORDERS_LSBH,a.SHIPORDERS_DJBH,a.SHIPORDERS_zdrq,a.SHIPORDERS_ZDR,b.SHIPORDERITEMS_LYID,b.SHIPORDERITEMS_LYFLID,b.SHIPORDERITEMS_LYCode,b.SHIPORDERITEMS_WL,b.SHIPORDERITEMS_WLPH
from SHIPORDERS as a
     inner join SHIPORDERITEMS as b on a.SHIPORDERS_LSBH = b.SHIPORDERITEMS_LSBH
     inner join @t as c on b.SHIPORDERITEMS_LYCode = c.SHIPORDERITEMS_LYCode and b.SHIPORDERITEMS_LYFLID = c.SHIPORDERITEMS_LYFLID
order by b.SHIPORDERITEMS_LYCode,a.SHIPORDERS_DJBH