to xspf ,说得没错,不drop掉ReTable,ReTable数据不是越添越多,由于我是在测试过程,所以每次执行存储过程前,我都会在查询分析器中手工执行删除的东作。我也认为是表的问题,可是为何同一个表,我每执行第一次都是正确的。
--如果改用临时表呢? CREATE PROCEDURE EXPBOM (@PR char(20)) AS begin SET NOCOUNT ONCreate Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3)) DECLARE @lvl int, @line char(20), @qy1 numeric(11,3), @qy2 numeric(11,3) CREATE TABLE #stack (lvl char(15),item char(20), MD006 numeric(11,3), MD007 numeric(11,3)) INSERT INTO #stack VALUES ('1',@PR,1,1) Set @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2)))) BEGIN SELECT @PR = item,@QY1=MD006,@QY2=MD007 FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) Order By item DESC SELECT @line =Replicate('0',@lvl-1)+Ltrim(Cast(@lvl as char(15)))+Space(10) PRINT @line+@PR --测试用 Insert into #ReTable(Lvl,item,MD006,MD007) values(@line,@PR,@QY1,@QY2) DELETE FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) AND item = @PR INSERT #stack SELECT Ltrim(cast(@lvl+1 as char(2))),MD003,MD006,MD007 FROM BOM WHERE MD001 = @PR Order BY MD001 IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END Select Lvl,item,MD006,MD007 from #ReTable --left join INVMB ON item = MB001 SET NOCOUNT OFF end
CREATE TABLE BOM(MD001 CHAR(20),MD003 CHAR(20),MD006 numeric(11,3),MD007 numeric(11,3)) INSERT INTO BOM VALUES('1210-00307000','3250-01307000',1,1) INSERT INTO BOM VALUES('1210-00307000','1250-01307001',1,1) INSERT INTO BOM VALUES('1210-00307000','3250-01307002',1,1) INSERT INTO BOM VALUES('1210-00307000','LA-CHL-50001',1,1) INSERT INTO BOM VALUES('1250-01307001','4250-01307000',1,1) INSERT INTO BOM VALUES('1250-01307001','4250-01307001',1,1) INSERT INTO BOM VALUES('1250-01307001','4250-01307002',1,1) INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001000',1,1) INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001001',1,1) INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001002',1,1) INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001003',1,1) go CREATE PROCEDURE EXPBOM (@PR char(20)) AS begin SET NOCOUNT ONCreate Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3)) DECLARE @lvl int, @line char(20), @qy1 numeric(11,3), @qy2 numeric(11,3) CREATE TABLE #stack (lvl char(15),item char(20), MD006 numeric(11,3), MD007 numeric(11,3)) INSERT INTO #stack VALUES ('1',@PR,1,1) Set @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2)))) BEGIN SELECT @PR = item,@QY1=MD006,@QY2=MD007 FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) Order By item DESC SELECT @line =Replicate('0',@lvl-1)+Ltrim(Cast(@lvl as char(15)))+Space(10) PRINT @line+@PR --测试用 Insert into #ReTable(Lvl,item,MD006,MD007) values(@line,@PR,@QY1,@QY2) DELETE FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) AND item = @PR INSERT #stack SELECT Ltrim(cast(@lvl+1 as char(2))),MD003,MD006,MD007 FROM BOM WHERE MD001 = @PR Order BY MD001 IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END Select Lvl,item,MD006,MD007 from #ReTable --left join INVMB ON item = MB001 SET NOCOUNT OFF end goEXEC EXPBOM '0401-95155010' EXEC EXPBOM '0401-95155010' go drop proc EXPBOM drop table bom/*--结果 1 0401-95155010 Lvl item MD006 MD007 --------------- -------------------- ------------- ------------- 1 0401-95155010 1.000 1.0001 0401-95155010 Lvl item MD006 MD007 --------------- -------------------- ------------- ------------- 1 0401-95155010 1.000 1.000 --*/
TO 邹键 Sorry,别外,我把结果集LEFT JOIN 别的表时,能否不让它重新排序,以便保持我展开的BOM结构。
别外,我把结果集LEFT JOIN 别的表时,能否不让它重新排序,以便保持我展开的BOM结构。 不能,关联后的顺序不能保证,其实你可以在#ReTable 中增加一个标识列来保证顺序. Create Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3) id int identity) --最后的查询改为: Select Lvl,item,MD006,MD007 from #ReTable a left join INVMB b ON a.item = b.MB001 order by a.id
truncate table ReTable
否則記錄會重復。也就是drop table #stack
之類的。
CREATE PROCEDURE EXPBOM (@PR char(20)) AS
begin
SET NOCOUNT ONCreate Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3))
DECLARE @lvl int, @line char(20), @qy1 numeric(11,3), @qy2 numeric(11,3)
CREATE TABLE #stack (lvl char(15),item char(20), MD006 numeric(11,3), MD007 numeric(11,3))
INSERT INTO #stack VALUES ('1',@PR,1,1)
Set @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))))
BEGIN
SELECT @PR = item,@QY1=MD006,@QY2=MD007 FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) Order By item DESC
SELECT @line =Replicate('0',@lvl-1)+Ltrim(Cast(@lvl as char(15)))+Space(10)
PRINT @line+@PR --测试用
Insert into #ReTable(Lvl,item,MD006,MD007) values(@line,@PR,@QY1,@QY2)
DELETE FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) AND item = @PR
INSERT #stack SELECT Ltrim(cast(@lvl+1 as char(2))),MD003,MD006,MD007 FROM BOM WHERE MD001 = @PR Order BY MD001
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END
Select Lvl,item,MD006,MD007
from #ReTable --left join INVMB ON item = MB001
SET NOCOUNT OFF
end
INSERT INTO BOM VALUES('1210-00307000','3250-01307000',1,1)
INSERT INTO BOM VALUES('1210-00307000','1250-01307001',1,1)
INSERT INTO BOM VALUES('1210-00307000','3250-01307002',1,1)
INSERT INTO BOM VALUES('1210-00307000','LA-CHL-50001',1,1)
INSERT INTO BOM VALUES('1250-01307001','4250-01307000',1,1)
INSERT INTO BOM VALUES('1250-01307001','4250-01307001',1,1)
INSERT INTO BOM VALUES('1250-01307001','4250-01307002',1,1)
INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001000',1,1)
INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001001',1,1)
INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001002',1,1)
INSERT INTO BOM VALUES('LA-CHL-50001','2101-03001003',1,1)
go CREATE PROCEDURE EXPBOM (@PR char(20)) AS
begin
SET NOCOUNT ONCreate Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3))
DECLARE @lvl int, @line char(20), @qy1 numeric(11,3), @qy2 numeric(11,3)
CREATE TABLE #stack (lvl char(15),item char(20), MD006 numeric(11,3), MD007 numeric(11,3))
INSERT INTO #stack VALUES ('1',@PR,1,1)
Set @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))))
BEGIN
SELECT @PR = item,@QY1=MD006,@QY2=MD007 FROM #stack
WHERE lvl = Ltrim(cast(@lvl as char(2))) Order By item DESC
SELECT @line =Replicate('0',@lvl-1)+Ltrim(Cast(@lvl as char(15)))+Space(10)
PRINT @line+@PR --测试用
Insert into #ReTable(Lvl,item,MD006,MD007) values(@line,@PR,@QY1,@QY2)
DELETE FROM #stack WHERE lvl = Ltrim(cast(@lvl as char(2))) AND item = @PR
INSERT #stack SELECT Ltrim(cast(@lvl+1 as char(2))),MD003,MD006,MD007 FROM BOM
WHERE MD001 = @PR Order BY MD001
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END
Select Lvl,item,MD006,MD007
from #ReTable --left join INVMB ON item = MB001
SET NOCOUNT OFF
end
goEXEC EXPBOM '0401-95155010'
EXEC EXPBOM '0401-95155010'
go
drop proc EXPBOM
drop table bom/*--结果
1 0401-95155010
Lvl item MD006 MD007
--------------- -------------------- ------------- -------------
1 0401-95155010 1.000 1.0001 0401-95155010
Lvl item MD006 MD007
--------------- -------------------- ------------- -------------
1 0401-95155010 1.000 1.000
--*/
不能,关联后的顺序不能保证,其实你可以在#ReTable 中增加一个标识列来保证顺序.
Create Table #ReTable (Lvl char(15),item char(20),MD006 numeric(11,3),MD007 numeric(11,3)
id int identity) --最后的查询改为:
Select Lvl,item,MD006,MD007
from #ReTable a left join INVMB b ON a.item = b.MB001
order by a.id
会不会当delete table后立即插入同样的数据时,当时数据页中的原有数据还没有来得及清除,于是SQL Server将其删除标记简单去除,于是表中数据的位置就保留了上一次插入的印记?