求替代料处理语句?
表结构: BOM表
BOM_NO nvarchar(40), --BOM号
PRD_NO nvarchar(40), --料号
PRD_NO_REP nvarchar(250) --替代料
BOM数据:
PC00001, 100-2300013, 100-2300013A;100-2300013B
PC00001, 100-2300014, 100-2300014B;100-2300014D
PC00002, 100-2300013, 100-2300013B;100-2300013D
PC00002, 100-2300014A, 100-2300014;100-2300014B;100-2300014D
--说明: 100-2300013A;100-2300013B为100-2300013的替代料; 替代料是用分号";"分隔的。
现在要新加一个替代料表: PRDT_REP 结构:
PRD_NO nvarchar(40), --料号
ITEM integer, --序号
PRD_NO_REP nvarchar(40) --替代料
并将BOM中数据写入到替代料表中;
100-2300013, 1, 100-2300013A
100-2300013, 2, 100-2300013B
100-2300013, 3, 100-2300013D
100-2300014, 1, 100-2300014B
100-2300014, 2, 100-2300014D
100-2300014A, 1, 100-2300014
100-2300014A, 2, 100-2300014B
100-2300014A, 3, 100-2300014D
最后再根据替代料表更新BOM表成为:
PC00001, 100-2300013, 1;2
PC00001, 100-2300014, 1;2
PC00002, 100-2300013, 2;3
PC00002, 100-2300014A, 1;2;3
--简单说: 原来BOM中存的是替代料号，现在要求存是的替代料表中的ITEM号; 不知表达得明白？
[解决办法]
--创建表create table BOM( BOM_NO nvarchar(40), --BOM号 PRD_NO nvarchar(40), --料号 PRD_NO_REP nvarchar(250) --替代料)gocreate table PRDT_REP(PRD_NO nvarchar(40), --料号ITEM integer, --序号PRD_NO_REP nvarchar(40) --替代料)go--新增测试数据insert BOMselect 'PC00001', '100-2300013', '100-2300013A;100-2300013B' union allselect 'PC00001', '100-2300014', '100-2300014B;100-2300014D' union allselect 'PC00002', '100-2300013', '100-2300013B;100-2300013D' union allselect 'PC00002', '100-2300014A', '100-2300014;100-2300014B;100-2300014D'--并将BOM中数据写入到替代料表中declare @sql varchar(max)set @sql = 'select 'select @sql=@sql+''''+prd_no+''' a,'''+replace(PRD_NO_REP,';',''' b union all select '''+prd_no+''',''')+''' union all select ' from BOMset @sql = 'insert PRDT_REP select a,row_number()over(partition by a order by b) id,b from ('+left(@sql,len(@sql) - 17)+')tbl group by a,b'exec (@sql)go--创建字符串聚合函数create function FN_GetStr( @PRD_NO nvarchar(40), --料号 @PRD_NO_REP nvarchar(500) --替代料)returns nvarchar(250)asbegin declare @result nvarchar(500) set @result = '' select @result = @result + ltrim(item) + ',' from PRDT_REP where PRD_NO = @PRD_NO and charindex(';'+prd_no_rep+';',';'+@PRD_NO_REP+';') > 0 return left(@result,len(@result)-1)endgo--更新update BOM set prd_no_rep = tbl.resultfrom( select *,dbo.FN_GetStr(PRD_NO,PRD_NO_REP) result from BOM)tblwhere BOM.bom_no = tbl.bom_no andBOM.PRD_NO = tbl.PRD_NO andBOM.PRD_NO_REP = tbl.PRD_NO_REP--返回最终数据select * from BOM
[解决办法]
create table BOM( BOM_NO nvarchar(40), PRD_NO nvarchar(40), PRD_NO_REP nvarchar(250))create table PRDT_REP(PRD_NO nvarchar(40),ITEM integer,PRD_NO_REP nvarchar(40))insert BOMselect 'PC00001', '100-2300013', '100-2300013A;100-2300013B' union allselect 'PC00001', '100-2300014', '100-2300014B;100-2300014D' union allselect 'PC00002', '100-2300013', '100-2300013B;100-2300013D' union allselect 'PC00002', '100-2300014A', '100-2300014;100-2300014B;100-2300014D'insert into PRDT_REP select t.PRD_NO,row_number() over (partition by PRD_NO order by PRD_NO_REP) 'ITEM',t.PRD_NO_REPfrom (select distinct a.PRD_NO,substring(a.PRD_NO_REP,b.number,charindex(';',a.PRD_NO_REP+';',b.number)-b.number) 'PRD_NO_REP'from BOM a, master.dbo.spt_values bwhere b.type='P' and b.number between 1 and len(a.PRD_NO_REP)and substring(';'+a.PRD_NO_REP,b.number,1)=';') tgroup by PRD_NO,PRD_NO_REPselect * from PRDT_REPPRD_NO ITEM PRD_NO_REP----------------- ---------- --------------100-2300013 1 100-2300013A100-2300013 2 100-2300013B100-2300013 3 100-2300013D100-2300014 1 100-2300014B100-2300014 2 100-2300014D100-2300014A 1 100-2300014100-2300014A 2 100-2300014B100-2300014A 3 100-2300014Dupdate t1 set t1.PRD_NO_REP=left(t2.PRD_NO_REP2,len(t2.PRD_NO_REP2)-1)from BOM t1inner join (select a.PRD_NO,a.PRD_NO_REP,cast((select case when charindex(b.PRD_NO_REP,a.PRD_NO_REP,1)>0 then cast(b.ITEM as varchar)+';' else '' end from PRDT_REP b where b.PRD_NO=a.PRD_NO for xml path('')) as varchar) 'PRD_NO_REP2'from BOM a) t2on t1.PRD_NO=t2.PRD_NO and t1.PRD_NO_REP=t2.PRD_NO_REPselect * from BOMBOM_NO PRD_NO PRD_NO_REP------------ -------------------- ---------------PC00001 100-2300013 1;2PC00001 100-2300014 1;2PC00002 100-2300013 2;3PC00002 100-2300014A 1;2;3