• 金蝶云星空SQL 84.89 kB 2024-06-03 22:06
  • Position: 金蝶云星空SQL / 20210812 研究递归CTE多级展开BOM--按照子项最新的BOM版本进行展开.sql

    --20210812 �о��ݹ�CTEչ��BOM;--HYW
    
    /**
    --SQL��дĿ�ģ�
    1�����ݵݹ�CTE���߼���ͨ��һ��SQL���ʵ�ֲ�Ʒ��ϵ�BOM�༶չ��������Ҫʹ��ѭ�������α�.
    2��Ϊʲô������ʹ��ѭ�������α꣬����ΪSQL���Եĵײ��߼��Ǽ���˼�룬���Ч�Ĵ��������ü���˼ά�Ľ��������������ô�ͳ��̵ı�˼ά��
    
    --��д˼·��
    1��ͨ�������Ӳ�ѯ������ĵ���BOM��Ȼ��ݹ��Ӳ�ѯ��չ�������BOM���������¼�BOM��
    2��ͨ��cte2����ʶ��ɸѡ��������µ�BOM�汾��
    3������ѯ������
    
    --ע�����
    1����������ָ���IJ�ƷBOM�汾�Ϊwhere���ʱ����SQL�����ѯ����еIJ�Ʒ��BOM��
    	����Ʒ�������ϻ�����Լ���BOM������������Ƽ�����ί�������ǻ���չ������ײ���ϣ�����Ʒ�������϶����⹺�����򲻻���չ�������Ľ�����Ҳ��չ�ֳ�����BOM��
    2���и���BOM����������ϡ��ֶΣ��������ʵ��BOM�༶չ���IJ㼶����ģ����а����˸���BOM��ID����κţ���ʽ�ǡ�A-B.C-D.E-F�����֣����С�.������Dz�ͬ�IJ㼶����-�������BOM�������κţ��˴���κ�����ԭ��κ�+10000�Ľ���
    3��Ŀǰ���������һ������BOM�������ϸ���ܳ���9999���¼���������Ϻͷ������ģ������г���999���¼������Ҫ������select�Ӿ��еġ�10000���Ĵ��10��20�򶼿��ԣ������������ü��ɡ�ע�⣺where����е����������ҲҪͬ���ij�(10��+1)��(20��+1)������Ż�չ�ֳ�0�㡣
    **/
    
    ;with cte as
    (
    	--1�����㣨Anchor���Ӳ�ѯ�������ѯ����IJ�Ʒ��BOM��
    	select 
    		0 as BOM���,t1.fid as ���BOM����
    		,t1.FNUMBER as BOM�汾,fxwl.FNUMBER as ������ϴ���,fxwl_L.FNAME as ����������,t3.FSEQ as ��¼�к�
    		,t3.FREPLACEGROUP as ���,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as ������
    		,cast(CAST(t1.fid AS nvarchar)+'-'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM�����������
    		,t3.FMATERIALID as �����������,zxwl.FNUMBER as ������ϴ���,zxwl_L.FNAME as ����������
    		,case when FMATERIALTYPE = 1 then '��׼��'
    				when FMATERIALTYPE = 2 then '������' 
    				when FMATERIALTYPE = 3 then '����' 
    				else 'δ֪����' end as �������
    		,t3.FNUMERATOR as ����,t3.FDENOMINATOR as ��ĸ,t3.FFIXSCRAPQTY as �̶����,t3.FSCRAPRATE as �䶯���,t3.FBOMID,t1.FUSEORGID
    
    	from dbo.T_ENG_BOM t1
    		join T_BD_MATERIAL fxwl			--�ø�������ϱ�
    			on fxwl.FMATERIALID = t1.FMATERIALID
    				and t1.FFORBIDSTATUS = 'A'	--ֻȡδ����״̬��BOM
    		join T_BD_MATERIAL_L fxwl_L		--�ø�������϶����Ա�
    			on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
    		join T_BD_MATERIALPRODUCE fxwl_P
    			on fxwl_P.FMATERIALID = fxwl.FMATERIALID
    		join T_ENG_BOMCHILD t3
    			on t1.fid = t3.FID		
    		join T_BD_MATERIAL zxwl			--����������ϱ�
    			on zxwl.FMATERIALID = t3.FMATERIALID
    		join T_BD_MATERIAL_L zxwl_L		--����������϶����Ա�
    			on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
    	where 1=1
    		and fxwl_P.FISMAINPRD = 1		--���-���ҳǩ��'��Ϊ���Ʒ'����FISMAINPRD,����1����ζ�ſ��Խ���BOM 
    		--and t1.FNUMBER in ('1.01.001_V0.0001','1.01.001.0066_V2.22') --����������һ����ƷBOM�汾,��ֻ���ѯһ����Ʒ��BOM�༶չ��;����һ��ע�͵��,�Ϳ��Բ�ѯȫ����Ʒ��ϵĶ༶չ��;���滹��һ�����Ƶ����Ҫͬ����,һ������.
    
    	union all
    
    	--2���ݹ��Ӳ�ѯ�����ݶ����Ӳ�ѯ�IJ�ѯ�������չ���������¼���BOM
    	select  
    		p.BOM���+1 as BOM���,P.���BOM���� as ���BOM����
    		,t1.FNUMBER as BOM�汾,fxwl.FNUMBER as ������ϴ���,fxwl_L.FNAME as ����������,t3.FSEQ as ��¼�к�
    		,t3.FREPLACEGROUP as ���,cast(p.������+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as ������
    		,cast(p.BOM����������� +'.'+ ( CAST(t1.FID AS nvarchar) + '-' +CAST(10000+t3.FREPLACEGROUP AS nvarchar) ) as nvarchar(max))  as BOM�������
    		,t3.FMATERIALID as �����������,zxwl.FNUMBER as ������ϴ���,zxwl_L.FNAME as ����������
    		,case when FMATERIALTYPE = 1 then '��׼��'
    				when FMATERIALTYPE = 2 then '������' 
    				when FMATERIALTYPE = 3 then '����' 
    				else 'δ֪����' end as �������
    		,t3.FNUMERATOR as ����,t3.FDENOMINATOR as ��ĸ,t3.FFIXSCRAPQTY as �̶����,t3.FSCRAPRATE as �䶯���,t3.FBOMID,t1.FUSEORGID
    
    	from cte P		--���õݹ�CTE����
    		join dbo.T_ENG_BOM t1
    			on t1.FMATERIALID = p.�����������
    		join T_BD_MATERIAL fxwl			--��������ϱ�
    			on fxwl.FMATERIALID = t1.FMATERIALID
    				and t1.FFORBIDSTATUS = 'A'
    		join T_BD_MATERIAL_L fxwl_L		--��������϶����Ա�
    			on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
    		join T_ENG_BOMCHILD t3
    			on t1.fid = t3.FID		
    		join T_BD_MATERIAL zxwl			--��������ϱ�
    			on zxwl.FMATERIALID = t3.FMATERIALID
    		join T_BD_MATERIAL_L zxwl_L		--��������϶����Ա�
    			on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
    )
    --select * from cte		----���Ե�һ��CTE
    ,cte2_ZuiXinZiXiangBom as		--��cte2�����ȡ��0�������BOM������BOM�汾��,Ȼ���0��ĸ�����Ϣunion��һ��
    (
    	select 
    		t1.BOM��� as BOM�㼶,t1.���BOM����,t1.BOM�汾
    		,t1.������ϴ��� as ��ϴ���,t1.���������� as ������,0 as ��¼�к�,0 as ���,t1.������ as ������,BOM�����������,0 as �����������,'' as ������ϴ���,'' as ����������,'��㸸��' as �������,0 as ����,0 as ��ĸ,0 as �̶����,0 as �䶯���,0 as BOM����,t1.FUSEORGID
    		,dense_rank() over(partition by t1.���BOM����,t1.������ϴ��� order by t1.BOM�汾 desc) as BOM�汾�ŷ��
    	from cte t1
    	where 1=1 
    		and t1.BOM��� = 0 and t1.������ = '10001'		--�����ֻ��ʾ0��IJ�Ʒ
    		--and t1.BOM�汾 in ('1.01.001_V0.0001','1.01.001.0066_V2.22')	--����������һ����ƷBOM�汾,��ֻ���ѯһ����Ʒ��BOM�༶չ��;����һ��ע�͵��,�Ϳ��Բ�ѯȫ����Ʒ��ϵĶ༶չ��;���滹��һ�����Ƶ����Ҫͬ����,һ������.
    
    	union 
    	select 
    		t1.BOM���+1 as BOM�㼶,t1.���BOM����,t1.BOM�汾
    		,t1.������ϴ��� as ��ϴ���,t1.���������� as ������,t1.��¼�к� as ��¼�к�,t1.��� as ���,t1.������ as ������,BOM�����������,0 as �����������,t1.������ϴ��� as ������ϴ���,'' as ����������,t1.������� as �������,t1.���� as ����,t1.��ĸ as ��ĸ,t1.�̶���� as �̶����,t1.�䶯��� as �䶯���,t1.FBOMID as BOM����,t1.FUSEORGID
    		,dense_rank() over(partition by t1.���BOM����,t1.������ϴ��� order by t1.BOM���+1,t1.BOM�汾 desc) as BOM�汾�ŷ��	--ͨ�����ֶα�ʶ���°汾��BOM�����ո�����Ϸ��֮�󣬰�BOM�汾������У�BOM�汾�ߵ�������ž���1
    	from cte t1
    	where 1=1
    		--and t1.BOM���+1 <=2	--����ͨ��BOM����ֶ�����Ƶݹ�ѭ���Ĵ��,����ﲻ�ӿ��ƣ���ϵͳĬ�������ѭ��100��
    )
    --select * from cte2_ZuiXinZiXiangBom t2		----���Եڶ���CTE
    select t2.BOM�㼶 as BOM�㼶
    		,t2.��ϴ��� as ������ϴ���,t2.������ as ������,t2.��¼�к� as ��¼�к�,t2.��� as ���,t2.������� as �������,t2.���� as ����,t2.��ĸ as ��ĸ
    		,t2.�̶���� as �̶����,t2.�䶯��� as �䶯���
    		,t2.FUSEORGID,t2.������ as ������,t2.BOM�����������,t2.BOM���� as ����BOM�汾����,t2.BOM�汾 as ���BOM,t2.���BOM����	--��һ�еĿ���ע�͵�,ֻ��Ϊ���Ų�SQL�����õ�.
    from cte2_ZuiXinZiXiangBom t2
    where 1=1
    	and t2.BOM�汾�ŷ�� = 1		--ͨ����BOM�汾�ŷ������ʶ���°汾��BOM�����ո�����Ϸ��֮�󣬰�BOM�汾������У�BOM�汾�ߵ�ֵ����1
    	and ( (t2.BOM�㼶 = 0 and t2.������ = '10001' ) or (t2.BOM�㼶 > 0) )	--����Ϊ�˲�ѯ�����յĽ�.
    	and t2.FUSEORGID =100003	--������е���׵ġ���װ��ҵ������֯
    order by t2.BOM�����������
    
    
    

    Powered by kodbox V1.64

    Copyright © kodcloud.com.

    Files