@Tiger_Zhao 上次在sql 2008上是可以用with as 的,但在sql 2000中没有这个。。
应该是 How old are you! 怎么老是你!CREATE PROCEDURE PROCNAME @matNo VARCHAR(20), @unitNo VARCHAR(20) AS BEGIN DECLARE @RESULT TABLE( matNo varchar(20), unitNo varchar(20), unitName varchar(20), unitParentNo varchar(20), unitNum INT, Result INT, UnitLevel INT ) DECLARE @UnitLevel INT SET @UnitLevel=1
INSERT INTO @RESULT SELECT *,unitNum,@UnitLevel FROM unit WHERE matNo=@matNo AND unitNo=@unitNo --2000没有递归,采用迭代 WHILE @@ROWCOUNT>0 BEGIN SET @UnitLevel=@UnitLevel+1 INSERT INTO @RESULT SELECT T1.*,T1.unitNum*T2.Result,@UnitLevel FROM unit T1 JOIN @RESULT T2 ON T1.unitParentNo=T2.unitNo AND T1.matNo=T2.matNo AND T2.UnitLevel=@UnitLevel-1 END --查询结果 SELECT TOP 1 matNo,Result FROM @RESULT ORDER BY UnitLevel DESC END GO -- EXEC PROCNAME '1001','001'
ALTER PROCEDURE PROCNAME @matNo VARCHAR(20), @unitNo VARCHAR(20) AS BEGIN DECLARE @RESULT TABLE( matNo varchar(20), unitNo varchar(20), unitName varchar(20), unitParentNo varchar(20), unitNum INT, Result INT, UnitLevel INT ) DECLARE @UnitLevel INT SET @UnitLevel=1
INSERT INTO @RESULT SELECT *,1,@UnitLevel FROM unit WHERE matNo=@matNo AND unitNo=@unitNo --2000没有递归,采用迭代 WHILE @@ROWCOUNT>0 BEGIN SET @UnitLevel=@UnitLevel+1 INSERT INTO @RESULT SELECT T1.*,T1.unitNum*T2.Result,@UnitLevel FROM unit T1 JOIN @RESULT T2 ON T1.unitParentNo=T2.unitNo AND T1.matNo=T2.matNo AND T2.UnitLevel=@UnitLevel-1 END --查询结果 SELECT TOP 1 matNo,Result FROM @RESULT ORDER BY UnitLevel DESC END GO -- EXEC PROCNAME '1001','002'理解出入问题,再试试
@ky_min 谢谢大神已经解决了,将 INSERT INTO @RESULT SELECT *,unitNum,@UnitLevel FROM unit WHERE matNo=@matNo AND unitNo=@unitNo 改成 INSERT INTO @RESULT SELECT *,unitNum=1,@UnitLevel FROM unit WHERE matNo=@matNo AND unitNo=@unitNo 就可以了
How are you - 怎么又是你
How old are you!
怎么老是你!CREATE PROCEDURE PROCNAME
@matNo VARCHAR(20),
@unitNo VARCHAR(20)
AS
BEGIN
DECLARE @RESULT TABLE(
matNo varchar(20),
unitNo varchar(20),
unitName varchar(20),
unitParentNo varchar(20),
unitNum INT,
Result INT,
UnitLevel INT
)
DECLARE @UnitLevel INT
SET @UnitLevel=1
INSERT INTO @RESULT
SELECT *,unitNum,@UnitLevel FROM unit
WHERE matNo=@matNo AND unitNo=@unitNo
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @UnitLevel=@UnitLevel+1
INSERT INTO @RESULT
SELECT T1.*,T1.unitNum*T2.Result,@UnitLevel
FROM unit T1 JOIN @RESULT T2 ON T1.unitParentNo=T2.unitNo
AND T1.matNo=T2.matNo AND T2.UnitLevel=@UnitLevel-1
END
--查询结果
SELECT TOP 1 matNo,Result FROM @RESULT ORDER BY UnitLevel DESC
END
GO
--
EXEC PROCNAME '1001','001'
EXEC PROCNAME '1001','001' 结果为 50 是正确的,但执行EXEC PROCNAME '1001','002' 结果也是50,实际应该是10,执行
EXEC PROCNAME '1001','003' 结果为10,实际应该是1,请大神再帮忙看看
@matNo VARCHAR(20),
@unitNo VARCHAR(20)
AS
BEGIN
DECLARE @RESULT TABLE(
matNo varchar(20),
unitNo varchar(20),
unitName varchar(20),
unitParentNo varchar(20),
unitNum INT,
Result INT,
UnitLevel INT
)
DECLARE @UnitLevel INT
SET @UnitLevel=1
INSERT INTO @RESULT
SELECT *,1,@UnitLevel FROM unit
WHERE matNo=@matNo AND unitNo=@unitNo
--2000没有递归,采用迭代
WHILE @@ROWCOUNT>0
BEGIN
SET @UnitLevel=@UnitLevel+1
INSERT INTO @RESULT
SELECT T1.*,T1.unitNum*T2.Result,@UnitLevel
FROM unit T1 JOIN @RESULT T2 ON T1.unitParentNo=T2.unitNo
AND T1.matNo=T2.matNo AND T2.UnitLevel=@UnitLevel-1
END
--查询结果
SELECT TOP 1 matNo,Result FROM @RESULT ORDER BY UnitLevel DESC
END
GO
--
EXEC PROCNAME '1001','002'理解出入问题,再试试
INSERT INTO @RESULT
SELECT *,unitNum,@UnitLevel FROM unit
WHERE matNo=@matNo AND unitNo=@unitNo
改成
INSERT INTO @RESULT
SELECT *,unitNum=1,@UnitLevel FROM unit
WHERE matNo=@matNo AND unitNo=@unitNo
就可以了