--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�����������