SQL sql遍历表中的每一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28506747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql loop through each row in a table
提问by vighurt
I've created a program that generates buy and sell signals of stocks. I've also created logic that tests the different signals and provides the return for each trade.
我创建了一个程序，可以生成股票的买卖信号。我还创建了测试不同信号并为每笔交易提供回报的逻辑。
The next step is to simulate the strategy and its rules over a long period of time. All information is exported to text files and imported to a table in a SQL Server database. I've realized that I need to declare a number of variables such as StartCapital, CurrentCapital, NumberOfPositions, PositionsLeft. One of the columns is named BuyPriceand indicates when to buy and to which price, when this occurs NumberOfPositionsshould be subtracted by 1.
下一步是在很长一段时间内模拟策略及其规则。所有信息都导出到文本文件并导入到 SQL Server 数据库中的表中。我意识到我需要声明一些变量，例如StartCapital, CurrentCapital, NumberOfPositions, PositionsLeft。其中一列被命名BuyPrice并指示何时购买以及以哪个价格购买，当发生这种情况NumberOfPositions时应减去 1。
The column SellPriceindicates when to sell and to which price, when this occurs NumberOfPositionsneeds to be added by one. The maximum of NumberOfPositionsshould be 5 and minimum 0. The desired result is to see how the CurrentCapitalunfolds.
该列SellPrice指示何时出售以及以哪个价格出售，发生这种情况时NumberOfPositions需要加一。的最大值NumberOfPositions应为 5，最小值应为 0。所需的结果是查看CurrentCapital展开方式。
I would very much appreciate any input and some kind of SQL code to start form.
我非常感谢任何输入和某种 SQL 代码来启动表单。
回答by Owen
There is another way to loop. I've seen a lot of answers increment the counter by 1. However, in my experience, there is no guarantee that your ids in the dataset won't have gaps.
还有另一种循环方式。我已经看到很多答案将计数器加 1。但是，根据我的经验，不能保证您在数据集中的 id 不会有间隙。
Here is a solution that I often use:
这是我经常使用的解决方案：
declare @idColumn int
select @idColumn = min( TableID ) from Table
while @idColumn is not null
begin
/*
Do all the stuff that you need to do
*/
select @idColumn = min( TableID ) from Table where TableID > @idColumn
end
回答by Jorge E. Hernández
Based on the caption of your question. This is the way I loop through each row of a table using a variable of type TABLE:
根据您问题的标题。这是我使用类型变量遍历表的每一行的方式TABLE：
DECLARE
@counter INT = 1,
@max INT = 0
-- Declare a variable of type TABLE. It will be used as a temporary table.
DECLARE @myTable TABLE (
[Id] int identity,
[Column1] nvarchar(max),
[Column2] nvarchar(100)
)
-- Insert your required data in the variable of type TABLE
INSERT INTO @myTable
SELECT Column1, Column2
FROM [dbo].[YOUR_DATABASE_TABLE]
-- Initialize the @max variable. We'll use thie variable in the next WHILE loop.
SELECT @max = COUNT(ID) FROM @myTable
-- Loop
WHILE @counter <= @max
BEGIN
-- Do whatever you want with each row in your table variable filtering by the Id column
SELECT Column1, Column2
FROM @myTable
WHERE Id = @counter
SET @counter = @counter + 1
END
