请高手指点我不懂的以下红色2处语法,谢谢!
CREATE function test2(@parent VARCHAR(20))
returns @temptable table(level tinyint,parent Nvarchar(20),child Nvarchar(20),unit char(10),qty numeric(9,3),MD002 char(4),
sort Nvarchar(1000)collate Latin1_General_BIN)
as
begin
declare @level int
set @level=1
insert into @temptable select 0,null,@parent,'PCS',0,0,0
insert into @temptable
select @level,MD001,MD003,MD004,MD006,MD002,MD001+MD002
from BOMMD
where MD001=@parent collate Latin1_General_BIN
while @@rowcount>0
begin
set @level=@level+1
insert @temptable
select @level,a.MD001,a.MD003,a.MD004,a.MD006,a.MD002,b.sort+a.MD002
from BOMMD a ,@temptable b
where a.MD001=b.child collate Latin1_General_BIN
and b.level=@level-1
CREATE function test2(@parent VARCHAR(20))
returns @temptable table(level tinyint,parent Nvarchar(20),child Nvarchar(20),unit char(10),qty numeric(9,3),MD002 char(4),
sort Nvarchar(1000)collate Latin1_General_BIN)
as
begin
declare @level int
set @level=1
insert into @temptable select 0,null,@parent,'PCS',0,0,0
insert into @temptable
select @level,MD001,MD003,MD004,MD006,MD002,MD001+MD002
from BOMMD
where MD001=@parent collate Latin1_General_BIN
while @@rowcount>0
begin
set @level=@level+1
insert @temptable
select @level,a.MD001,a.MD003,a.MD004,a.MD006,a.MD002,b.sort+a.MD002
from BOMMD a ,@temptable b
where a.MD001=b.child collate Latin1_General_BIN
and b.level=@level-1
第二处,你自己试试就行了,insert语法
--1、定义了sort的数据类型和排序规则
--2、往表@temptable里插入数据,数值型可以直接写,字符型要用''引起来
定义变量和变量用排序规则。
#2. SELECT '常量', '常量', '常量' 相当于 构建一个有3个字段的虚拟表,且此表中只有一行数据,并检索出来
select @level,a.MD001,a.MD003,a.MD004,a.MD006,a.MD002,b.sort+a.MD002
insert @temptable
select @level,a.MD001,a.MD003,a.MD004,a.MD006,a.MD002,b.sort+a.MD002
from BOMMD a ,@temptable b
where a.MD001=b.child collate Latin1_General_BIN
and b.level=@level-1
--@temptable里的字段sort是字符型,BOMMD里的MD002也是字符型,具体就是将这两个字段的值拼到一起
--作为@temptable的最后一个字段的值插入到表@temptable中!