--建立环境
create Table #BOM(field1 varchar(10),field2 varchar(10),field3 varchar(10))
insert #bom values('A','001','B')
insert #bom values('B','002','C')
insert #bom values('C','003','D')
insert #bom values('D','004','E')
insert #bom values('E','005','F')declare @根参数 varchar(10)
set @根参数='001'
declare @结果集合 table(field1 varchar(10),field2 varchar(10),field3 varchar(10))insert @结果集合 select * from #bom where field2=@根参数while exists(select 1 from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合))
begin
insert @结果集合 select * from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合)
endselect * from #bom
create Table #BOM(field1 varchar(10),field2 varchar(10),field3 varchar(10))
insert #bom values('A','001','B')
insert #bom values('B','002','C')
insert #bom values('C','003','D')
insert #bom values('D','004','E')
insert #bom values('E','005','F')declare @根参数 varchar(10)
set @根参数='001'
declare @结果集合 table(field1 varchar(10),field2 varchar(10),field3 varchar(10))insert @结果集合 select * from #bom where field2=@根参数while exists(select 1 from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合))
begin
insert @结果集合 select * from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合)
endselect * from #bom
--建立环境
create Table #BOM(field1 varchar(10),field2 varchar(10),field3 varchar(10))
insert #bom values('A','001','B')
insert #bom values('B','002','C')
insert #bom values('C','003','D')
insert #bom values('D','004','E')
insert #bom values('E','005','F')declare @根参数 varchar(10),@结果字符串 varchar(8000)
select @根参数='001',@结果字符串=''
declare @结果集合 table(field1 varchar(10),field2 varchar(10),field3 varchar(10))insert @结果集合 select * from #bom where field2=@根参数while exists(select 1 from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合))
insert @结果集合 select * from #bom where field1 in (select field3 from @结果集合) and field1 not in (select field1 from @结果集合)select @结果字符串=@结果字符串+','+field2 from #bomselect right(@结果字符串,len(@结果字符串)-1)
go
drop table #bom
set field2=right( ('000'+cast(id as varchar)),3)alter table 表 drop column id
DECLARE @min1 char(1)
DECLARE @min3 char(1)
DECLARE @max1 char(1)
DECLARE @max3 char(1)
DECLARE @count intCREATE TABLE #Temp
(
field1 char(1),
field2 char(3),
field3 char(1)
)SELECT @max1=MAX(field1) FROM table1 WHERE field3>field1
SELECT @max3=MAX(field3) FROM table1 WHERE field3>field1 AND field1=@max1
SELECT @min1=MIN(field1) FROM table1 WHERE field3>field1
SELECT @min3=MIN(field3) FROM table1 WHERE field1=@min1 AND field3>field1
SET @count=1WHILE (1=1)
BEGIN
IF (@min3 IS NULL) BREAK INSERT INTO #Temp
SELECT field1,RIGHT('00'+LTRIM(CAST(@count AS varchar(10))),3) AS field2,field3 FROM table1 WHERE field1=@min1 AND field3=@min3 IF (@min1=@max1 AND @min3=@max3) BREAK SET @min1=@min3
SELECT @min3=MIN(field3) FROM table1 WHERE field1=@min1 AND field3>field1
SET @count=@count+1ENDSELECT * FROM #Temp