SQL 循环遍历表中的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15908548/
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 records in a table
提问by Jana
I have a table called master, the primary key is account_num. Each account number has an account_type (single character). I need to do the following:
我有一个叫做master的表，主键是account_num。每个帐号都有一个 account_type（单个字符）。我需要执行以下操作：
- find all accounts with a type of A or B.
- store that account number in a new table called year_end_close along with a time stamp of when that transaction takes place
- set all accounts in master with a type of A to C, and all accounts with a type of B to D
- 查找所有类型为 A 或 B 的帐户。
- 将该帐号存储在名为 year_end_close 的新表中，并带有该交易发生时间的时间戳
- 将master中A类型的所有账户设置为C，将B类型的所有账户设置为D
What's the best way to handle this in SQL? While loop? Case statement? Cursor? Any help is appreciated. The table has about 17,000 rows.
在 SQL 中处理此问题的最佳方法是什么？虽然循环？案例陈述？光标？任何帮助表示赞赏。该表有大约 17,000 行。
回答by Jeff Rosenberg
You shouldn't need to use a cursor/loop to do something like this. When writing SQL, always try to look for a set-based solution first. I would recommend a CASEstatement, which was one of the options you mentioned.
你不应该需要使用游标/循环来做这样的事情。在编写 SQL 时，总是首先尝试寻找基于集合的解决方案。我会推荐一个CASE声明，这是您提到的选项之一。
Try this:
尝试这个：
BEGIN TRAN;
SELECT account_num, CURRENT_TIMESTAMP
INTO year_end_close
FROM dbo.master
WHERE account_type IN ('a','b');
UPDATE dbo.master
SET account_type = CASE account_type
WHEN 'a' THEN 'c'
WHEN 'b' THEN 'd'
ELSE account_type
END
WHERE account_type IN ('a','b');
COMMIT TRAN;
回答by The Pig's Ear
Are you searching for something like this? (Replace the 'PRINT' statements for your actual SQL statements)
你在寻找这样的东西吗？（将“PRINT”语句替换为您实际的 SQL 语句）
DECLARE @MasterTable TABLE
(
account_num int,
account_type varchar(1)
)
INSERT INTO @MasterTable VALUES (1, 'A')
INSERT INTO @MasterTable VALUES (2, 'A')
INSERT INTO @MasterTable VALUES (3, 'B')
INSERT INTO @MasterTable VALUES (4, 'B')
INSERT INTO @MasterTable VALUES (5, 'C')
INSERT INTO @MasterTable VALUES (6, 'C')
DECLARE @account_num int
DECLARE @account_type varchar(1)
DECLARE @switch_type varchar(1)
DECLARE db_cursor CURSOR FOR
SELECT account_num, account_type
FROM @MasterTable
WHERE account_type IN ('A', 'B')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @account_num, @account_type
WHILE @@FETCH_STATUS = 0
BEGIN
IF @account_type = 'A'
SET @switch_type = 'C'
ELSE
SET @switch_type = 'D'
PRINT 'INSERT year_end_close (account_num, timestampfield) VALUES (' + CAST(@account_num AS VARCHAR) + ', GETDATE())'
PRINT 'UPDATE @MasterTable SET account_type = ' + @switch_type + ' WHERE account_num = ' + CAST(@account_num AS VARCHAR)
FETCH NEXT FROM db_cursor INTO @account_num, @account_type
END
CLOSE db_cursor
DEALLOCATE db_cursor
