就是怎么才能使用类似 select * from f_cid in ('XXXX','XXXX','XXXX','XXXX') 这样的查询
函数如下: create function f_cid( @物料 varchar(50) )returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int) as begin declare @l int set @l=0 insert @re select 父物料,子物料,数量,手区 ,@l from tb where 父物料=@物料 while @@rowcount> 0 begin set @l=@l+1 insert @re select a.父物料,a.子物料,a.数量,a.手区,@l from tb a,@re b where a.父物料=b.物料 and b.[level]=@l-1 end return end go
函数修改如下,将要查询的[物料]串接起来(用逗号分隔),作为参数传入函数即可,create function f_cid (@物料 varchar(5000)) returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int) as begin declare @l int set @l=0declare @wl table(pn varchar(50)) declare @sql varchar(5000)select @sql='select '''+replace(@物料,',',''' union all select ''')+''' ' insert into @wl exec(@sql)insert @re select 父物料,子物料,数量,手区 ,@l from tb a inner join @wl b where a.父物料=b.pn while @@rowcount>0 begin set @l=@l+1 insert @re select a.父物料,a.子物料,a.数量,a.手区,@l from tb a,@re b where a.父物料=b.物料 and b.[level]=@l-1 endreturn end go
函数代码变更部分的说明,-- 函数输入参数 declare @物料 varchar(5000)-- 要查询的[物料]串接起来(用逗号分隔) select @物料='a,b,c,d,e'-- 建表变量 declare @wl table(pn varchar(50)) declare @sql varchar(5000)-- 分割处理 select @sql='select '''+replace(@物料,',',''' union all select ''')+''' ' insert into @wl exec(@sql)-- 查询结果 select * from @wlpn ------ a b c d e (5 row(s) affected)-- 然后用@wl联接其他表,获取所有物料的子物料.
select @sql='select '''+replace(@物料,',',''' union all select ''')+''' ' insert into @wl exec(@sql)应该上面语句有问题
for SQL2000的写法,create function f_cid (@物料 varchar(5000)) returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int) as begin declare @l int,@i int,@t varchar(50) declare @wl table(pn varchar(50))select @l=0,@物料=@物料+',',@i=1,@t=''while(@i<=len(@物料)) begin if substring(@物料,@i,1)<>',' select @t=@t+substring(@物料,@i,1) else begin insert into @wl select @t select @t='' end select @i=@i+1 endinsert @re select 父物料,子物料,数量,手区 ,@l from tb a inner join @wl b where a.父物料=b.pn while @@rowcount>0 begin set @l=@l+1 insert @re select a.父物料,a.子物料,a.数量,a.手区,@l from tb a,@re b where a.父物料=b.物料 and b.[level]=@l-1 endreturn end go
函数代码变更部分的说明,-- 函数输入参数 declare @物料 varchar(5000)-- 要查询的[物料]串接起来(用逗号分隔) select @物料='a1,b2,c3,d4,e5'-- 建表变量 declare @l int,@i int,@t varchar(50) declare @wl table(pn varchar(50)) select @l=0,@物料=@物料+',',@i=1,@t=''-- 分割处理 while(@i<=len(@物料)) begin if substring(@物料,@i,1)<>',' select @t=@t+substring(@物料,@i,1) else begin insert into @wl select @t select @t='' end select @i=@i+1 end-- 查询结果 select * from @wlpn ----- a1 b2 c3 d4 e5(5 row(s) affected)-- 然后用@wl联接其他表,获取所有物料的子物料.声明一下: 7楼的方法在SQL 2008 R2下执行正常.
没有必要切割啊,用charindex来代替切割,效力倍增DECLARE @sql VARCHAR(200) SET @sql='1,2,3,4,5,' SELECT * FROM tb where charindex((CAST(父物料 AS VARCHAR(20)))+',',@sql) > 0
用这个写法是可以的,有一个小错误,就是 inner join @wl b on a.父物料=b.pn 但是有一个问题就是这个是一级一级展开的,遇到多级就不会再寻找下一个物料循环展开了。怎么办?
--> 多級物料展開是在以下這段代碼裡處理的呀,是原先函數裡的寫法應該沒問題的,你能舉例說明問題點嗎?while @@rowcount>0 begin set @l=@l+1 insert @re select a.父物料,a.子物料,a.数量,a.手区,@l from tb a,@re b where a.父物料=b.物料 and b.[level]=@l-1 end
就是怎么才能使用类似
select * from f_cid in ('XXXX','XXXX','XXXX','XXXX') 这样的查询
create function f_cid(
@物料 varchar(50)
)returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int)
as
begin
declare @l int
set @l=0
insert @re select 父物料,子物料,数量,手区 ,@l from tb where 父物料=@物料
while @@rowcount> 0
begin
set @l=@l+1
insert @re select a.父物料,a.子物料,a.数量,a.手区,@l
from tb a,@re b
where a.父物料=b.物料 and b.[level]=@l-1
end
return
end
go
returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int)
as
begin
declare @l int
set @l=0declare @wl table(pn varchar(50))
declare @sql varchar(5000)select @sql='select '''+replace(@物料,',',''' union all select ''')+''' '
insert into @wl exec(@sql)insert @re
select 父物料,子物料,数量,手区 ,@l
from tb a
inner join @wl b
where a.父物料=b.pn while @@rowcount>0
begin
set @l=@l+1
insert @re select a.父物料,a.子物料,a.数量,a.手区,@l
from tb a,@re b
where a.父物料=b.物料 and b.[level]=@l-1
endreturn
end
go
declare @物料 varchar(5000)-- 要查询的[物料]串接起来(用逗号分隔)
select @物料='a,b,c,d,e'-- 建表变量
declare @wl table(pn varchar(50))
declare @sql varchar(5000)-- 分割处理
select @sql='select '''+replace(@物料,',',''' union all select ''')+''' '
insert into @wl exec(@sql)-- 查询结果
select * from @wlpn
------
a
b
c
d
e
(5 row(s) affected)-- 然后用@wl联接其他表,获取所有物料的子物料.
消息 197,级别 15,状态 1,过程 f_cid1,第 12 行
无法在向表插入变量时将 EXECUTE 用作源。
消息 156,级别 15,状态 1,过程 f_cid1,第 18 行
在关键字 'where' 附近有语法错误。
insert into @wl exec(@sql)应该上面语句有问题
returns @re table(父物料 varchar(50),物料 varchar(50),数量 varchar(50),手区 varchar(50),[level] int)
as
begin
declare @l int,@i int,@t varchar(50)
declare @wl table(pn varchar(50))select @l=0,@物料=@物料+',',@i=1,@t=''while(@i<=len(@物料))
begin
if substring(@物料,@i,1)<>','
select @t=@t+substring(@物料,@i,1)
else
begin
insert into @wl select @t
select @t=''
end
select @i=@i+1
endinsert @re
select 父物料,子物料,数量,手区 ,@l
from tb a
inner join @wl b
where a.父物料=b.pn while @@rowcount>0
begin
set @l=@l+1
insert @re select a.父物料,a.子物料,a.数量,a.手区,@l
from tb a,@re b
where a.父物料=b.物料 and b.[level]=@l-1
endreturn
end
go
declare @物料 varchar(5000)-- 要查询的[物料]串接起来(用逗号分隔)
select @物料='a1,b2,c3,d4,e5'-- 建表变量
declare @l int,@i int,@t varchar(50)
declare @wl table(pn varchar(50))
select @l=0,@物料=@物料+',',@i=1,@t=''-- 分割处理
while(@i<=len(@物料))
begin
if substring(@物料,@i,1)<>','
select @t=@t+substring(@物料,@i,1)
else
begin
insert into @wl select @t
select @t=''
end
select @i=@i+1
end-- 查询结果
select * from @wlpn
-----
a1
b2
c3
d4
e5(5 row(s) affected)-- 然后用@wl联接其他表,获取所有物料的子物料.声明一下: 7楼的方法在SQL 2008 R2下执行正常.
SET @sql='1,2,3,4,5,'
SELECT * FROM tb where
charindex((CAST(父物料 AS VARCHAR(20)))+',',@sql) > 0
inner join @wl b
on a.父物料=b.pn
但是有一个问题就是这个是一级一级展开的,遇到多级就不会再寻找下一个物料循环展开了。怎么办?
begin
set @l=@l+1
insert @re select a.父物料,a.子物料,a.数量,a.手区,@l
from tb a,@re b
where a.父物料=b.物料 and b.[level]=@l-1
end