来自 金沙澳门官网网址 2019-12-19 15:01 的文章
当前位置: 金沙澳门官网网址 > 金沙澳门官网网址 > 正文

SQL对字符串数组的拍卖,一遍性张开多少个多阶

以下语句能够兑现单个BOM的拓宽,但风度翩翩旦想二次性张开四个以上的制品BOM就乱套了,请教各位老师有如何好的情势、思路能够消除此难点?---创制一时表,插入测验数据--createtable#bom(母件varchar(50卡塔尔,子件项目和次数varchar(100卡塔尔(英语:State of Qatar),子件varchar(50卡塔尔国,用量numeric(16,4卡塔尔卡塔尔insertinto#bomselect'10210100030','1','20012100008',1unionallselect'10210100030','2','20012110008',0.5unionallselect'10210100030','3','30300701001',1unionallselect'20012100008','1','2001210P161',1unionallselect'20012100008','2','30400201100',2unionallselect'20012110008','1','2001211S104',1unionallselect'20012110008','2','30400203000',0.5unionallselect'10210100026','1','20012100005',3unionallselect'10210100026','2','20012190012',4unionallselect'10210100026','3','30300201001',1unionallselect'20012100005','1','2001210P162',1unionallselect'20012100005','2','30400201100',0.5unionallselect'20012190012','1','2001219Z001',2unionallselect'2001219Z002','1','30400201111',2unionallselect'20012190012','2','2001219Z002',2--droptable#bom--select* from#bom---创造可多选函数---createfunctionSplitIn(@cvarchar(2003卡塔尔(英语:State of Qatar),@splitvarchar(2卡塔尔卡塔尔(英语:State of Qatar)returns@ttable(colvarchar(32卡塔尔国卡塔尔(قطر‎asbeginwhile(charindex(@split,@c卡塔尔(قطر‎0卡塔尔(英语:State of Qatar)begininsert@t(col卡塔尔values(substring(@c,1,charindex(@split,@c卡塔尔(قطر‎-1卡塔尔卡塔尔国set@c=stuff(@c,1,charindex(@split,@c卡塔尔国,''卡塔尔(قطر‎endinsert@t(col卡塔尔values(@c卡塔尔(英语:State of Qatar)returnend----制造展BOM存款和储蓄进程---createproc[dbo].[p_bom]@mmastervarchar(50卡塔尔(قطر‎asbegin-----变量表--------declare@BOMtable(序号varchar(100卡塔尔(قطر‎,阶次varchar(10卡塔尔(英语:State of Qatar),层级varchar(100卡塔尔(英语:State of Qatar),母件varchar(50卡塔尔国,子件varchar(50卡塔尔(英语:State of Qatar),用量numeric(16,4卡塔尔(قطر‎,实际用量numeric(16,4卡塔尔(英语:State of Qatar)卡塔尔国;----递规运算BOM构造,结果插入表变量@BOM-----withtAs(selectle=convert(varchar(10卡塔尔(英语:State of Qatar),1卡塔尔,convert(varchar(100卡塔尔(英语:State of Qatar),子件项目和次数卡塔尔(英语:State of Qatar)as层级,*,实际用量=cast(用量asnumeric(16,4卡塔尔(英语:State of Qatar)卡塔尔(قطر‎from#bomWhere母件in(selectcolas母件fromsplitIn(@mmaster,','卡塔尔卡塔尔国unionallselectle=convert(varchar(10卡塔尔,le+1卡塔尔国,convert(Varchar(100卡塔尔(قطر‎,层级+','+convert(Varchar(100卡塔尔,B.子件项目和次数卡塔尔(英语:State of Qatar)卡塔尔国As层级,---BOM子件项目和次数归并,显示层级,用于排序显示BOM布局B.*,cast(t.实际用量*b.用量asnumeric(16,4卡塔尔国卡塔尔AS实际用量fromTinnerjoin#bomBonT.子件=B.母件)insertinto@BOMselectROW_NUMBER()over(orderby层级)序号,*from(SelectREPLICATE('.',le卡塔尔+LTLacrosseIM(le卡塔尔as阶次,---树状层级,母件,子件,用量,实际用量Fromtunionallselectdistinct阶次='0','0'as层级,''as母件,母件as子件,用量=0,实际用量=0---顶层母件from#bomasawherea.母件in(selectcolAs母件fromsplitIn(@mmaster,',')))corderbyc.层级select*from@bomendGOexecp_bom'10210100030,10210100026'

生龙活虎,用有时表作为数组 

复制代码代码如下:

create function f_split(@c varchar(2000),@split varchar(2)) 
returns @t table(col varchar(20)) 
as 
begin 

while(charindex(@split,@c)<>0) 
begin 
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) 
set @c = stuff(@c,1,charindex(@split,@c),'') 
end 
insert @t(col) values (@c) 
return 
end 
go 

select * from dbo.f_split('dfkd,dfdkdf,dfdkf,dffjk',',') 

drop function f_split 
col 
-------------------- 
dfkd 
dfdkdf 
dfdkf 
dffjk 

(所影响的行数为 4 行) 

二、按钦点符号分割字符串,重临分割后的因素个数,方法很简短,正是看字符串中留存多少个分隔符号,然后再加风度翩翩,正是供给的结果。 

复制代码代码如下:

CREATE function Get_StrArrayLength 

@str varchar(1024卡塔尔(قطر‎, --要分开的字符串 
@split varchar(10卡塔尔 --分隔符号 

returns int 
as 
begin 
declare @location int 
declare @start int 
declare @length int 

set @str=ltrim(rtrim(@str)) 
set @location=charindex(@split,@str) 
set @length=1 
while @location<>0 
begin 
set @start=@location+1 
set @location=charindex(@split,@str,@start) 
set @length=@length+1 
end 
return @length 
end 

调用示例:select dbo.Get_StrArrayLength('78,1,2,3',',') 
返回值:4 

本文由金沙澳门官网网址发布于金沙澳门官网网址,转载请注明出处:SQL对字符串数组的拍卖,一遍性张开多少个多阶

关键词: