SQL WHILE循环 [英] SQL WHILE Loops
问题描述
我一直在努力在SQL中创建嵌套的while循环,但是while循环有问题.我认为主要问题在于我的外部循环.有什么建议?
I have been working on creating a nested while loop in SQL, but having issues with the while loop. I think the main issue is with my outer loop. Any suggestions?
USE HW_DB;
IF OBJECT_ID('dbo.PythagoreanTriangles') IS NOT NULL
DROP TABLE dbo.PythagoreanTriangles;
GO
CREATE TABLE PythagoreanTriangles
(
Side1 INT NOT NULL,
Side2 INT NOT NULL,
Hypotenuse FLOAT NOT NULL
);
DECLARE @side1 AS INT = 1;
DECLARE @side2 AS INT = 1;
DECLARE @count AS INT = 1;
DECLARE @element AS INT = 0;
WHILE (@side1 = @count)
BEGIN
WHILE @side2 <= 10 BEGIN
INSERT INTO dbo.PythagoreanTriangles
VALUES (@side1, @side2, SQRT((@side2 * @side2) + (@side1 * @side1)));
UPDATE dbo.PythagoreanTriangles SET Hypotenuse = ROUND(Hypotenuse, 2)
SET @side2 = @side2 + 1;
SET @count = @count + 1;
SET @element = @element + 1;
IF @element = 10
BEGIN
SET @side1 = @side1 + 1;
SET @element = 0;
END;
END;
END;
输出应如下所示:
1 1 1.41
1 2 2.24
1 3 ...
1 4 ...
1 5
1 6
1 7
1 8
1 9
1 10
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
.....
........等
1 1 1.41
1 2 2.24
1 3 ...
1 4 ...
1 5
1 6
1 7
1 8
1 9
1 10
2 2
2 3
2 4
2 5
2 6
2 7
2 8
2 9
2 10
..........
........ etc
9 9
9 10
10 10
9 9
9 10
10 10
推荐答案
您可以通过一次插入操作来完成此操作,在可能的情况下,我始终建议您避免使用循环/游标.
You can do this with a single insert, and where possible I would always advise avoiding loops/cursors.
WITH Numbers AS
( SELECT TOP 10 Number = ROW_NUMBER() OVER(ORDER BY object_id)
FROM sys.all_objects
)
INSERT dbo.PythagoreanTriangles (Side1, Side2, Hypotenuse)
SELECT Side1 = a.Number,
Side2 = b.Number,
Hypotenuse = ROUND(SQRT(POWER(a.Number, 2) + POWER(b.Number, 2)), 2)
FROM Numbers a
CROSS JOIN Numbers b;
Example on SQL Fiddle
尽管实际上可以回答您的问题,但由于存在以下情况,循环会在一次迭代后退出:
Although to actually answer your question, your loop exits after one iteration because you have this condition:
WHILE (@side1 = @count)
在@ side2 1-10的第一次迭代之后,执行SET @side1 = @side1 + 1;
.由于@Count = 1
和@Side1
现在为2,因此您的WHILE谓词不再为true,因此外部循环退出.尽管我不主张使用这种方法,但是要使循环正常工作,您需要在谓词期间更改外部.也许像这样:
After the first iteration of @side2 1 - 10, you do SET @side1 = @side1 + 1;
. Since @Count = 1
, and @Side1
is now 2, your WHILE predicate is no longer true, so the outer loop exits. Although I don't advocate this approach, to make your loop work, you would need to change your outer while predicate. Perhaps something like:
DECLARE @side1 AS INT = 1;
DECLARE @side2 AS INT = 1;
WHILE (@side1 <= 10)
BEGIN
WHILE @side2 <= 10
BEGIN
INSERT INTO dbo.PythagoreanTriangles
VALUES (@side1, @side2, SQRT((@side2 * @side2) + (@side1 * @side1)));
UPDATE dbo.PythagoreanTriangles SET Hypotenuse = ROUND(Hypotenuse, 2)
SET @side2 = @side2 + 1;
END;
SET @side2 = 1;
SET @side1 = @side1 + 1;
END;
Example on SQL Fiddle
这篇关于SQL WHILE循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!