请问这一段语句是不是循环填充topN列?
临时表#TblHighRunner里有五列,其中ProductLine, PartNumber, PartDesc, Qty列都已填充,只有TopN列没被填充。这段代码是填充TopN列的,请问其循环机制是怎么样的?我看的很糊涂。
DECLARE @PL VARCHAR(25)
, @PN VARCHAR(25)
, @TopN INT
SET
@PL = 'DSAM' WHILE (@PL IS NOT NULL) BEGIN
SET
@PL =
(SELECT
TOP 1 ProductLine
FROM
#TblHighRunner
WHERE
TopN IS NULL
ORDER BY
ProductLine
, Qty DESC)
SET
@PN =
(SELECT
TOP 1 PartNumber
FROM
#TblHighRunner
WHERE
TopN IS NULL
ORDER BY
ProductLine
, Qty DESC)
UPDATE #TblHighRunner
SET
TopN = (CASE WHEN
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) IS NULL
THEN 1
ELSE
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) + 1
END)
WHERE
ProductLine = @PL
AND
PartNumber = @PN
SET
@TopN = (CASE WHEN
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) IS NULL
THEN
1
ELSE
(SELECT
MAX(TopN)
FROM
#TblHighRunner) + 1 END)
END
[解决办法]
大致看了一下 差不多是那么个意思
DECLARE @PL VARCHAR(25)--定义变量
, @PN VARCHAR(25)
, @TopN INT
SET
@PL = 'DSAM'--赋值
WHILE (@PL IS NOT NULL) --如果@PL不为空 进入循环
BEGIN
SET
@PL =
(SELECT
TOP 1 ProductLine
FROM
#TblHighRunner
WHERE
TopN IS NULL
ORDER BY
ProductLine
, Qty DESC--根据ProductLine,Qty排序 取出第一行的ProductLine并赋值给@PL
)
SET
@PN =
(SELECT
TOP 1 PartNumber
FROM
#TblHighRunner
WHERE
TopN IS NULL
ORDER BY
ProductLine
, Qty DESC)--根据ProductLine,Qty排序 取出第一行的PartNumber并赋值给@PN
UPDATE #TblHighRunner
SET
TopN = (CASE WHEN
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) IS NULL --判断ProductLine = @PL的MAX(TopN)是否为空
THEN 1 --如果为空
ELSE
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) + 1
END--如果不为空
)
WHERE
ProductLine = @PL
AND
PartNumber = @PN
SET
@TopN = (CASE WHEN
(SELECT
MAX(TopN)
FROM
#TblHighRunner
WHERE
ProductLine = @PL) IS NULL
THEN
1
ELSE
(SELECT
MAX(TopN)
FROM
#TblHighRunner) + 1 END)
--其实你这里还应该继续给@PL赋值 不然你这是一个死循环吧
END
循环给这个变量@PL赋值
SET @PL=(
SELECT TOP 1
ProductLine
FROM #TblHighRunner
WHERE TopN IS NULL
ORDER BY ProductLine , Qty DESC
)
第一次得到的是ProductLine 最小,Qty最大的那个
然后
UPDATE #TblHighRunner
SET TopN=(CASE WHEN (
SELECT MAX (TopN) FROM #TblHighRunner WHERE ProductLine = @PL
) IS NULL THEN 1
--这里第一次取得topn 是null 所以更新为1
--第二次就是执行下面的else,取最大的加1进行累计
ELSE (
SELECT MAX (TopN) FROM #TblHighRunner WHERE ProductLine = @PL
)+1
END)