知识点:
①声明变量:DECLARE 关键字
②循环语句:WHILE ...
BEGIN ...
END
③数据类型转换:CAST()函数
应用举例:
初始化脚本:
CREATE TABLE [dbo].[TEST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TID] [int] NULL,
[Discription] [nvarchar](200) NULL
)
--测试数据
DELETE FROM TEST
INSERT INTO TEST (TID,Discription)VALUES (1,'记录1-1')
INSERT INTO TEST (TID,Discription)VALUES (1,'记录1-2')
INSERT INTO TEST (TID,Discription)VALUES (1,'记录1-3')
INSERT INTO TEST (TID,Discription)VALUES (1,'记录1-4')
INSERT INTO TEST (TID,Discription)VALUES (1,'记录1-5')
INSERT INTO TEST (TID,Discription)VALUES (2,'记录2-1')
INSERT INTO TEST (TID,Discription)VALUES (2,'记录2-2')
INSERT INTO TEST (TID,Discription)VALUES (2,'记录2-3')
SELECT * FROM TEST
演练脚本:
DECLARE @startIndex INT,@endIndex INT,@discription NVARCHAR(100);
SELECT @startIndex= MIN(ID)FROM TEST;--最小记录ID
SELECT @endIndex= MAX(ID)FROM TEST;--最大记录ID
WHILE @startIndex <@endIndex
BEGIN
Select @discription=('第'+CAST(ID as NVARCHAR(100))+'行记录:'+CAST(TID as NVARCHAR(100))+'.'+Discription) FROM TEST WHERE ID=@startIndex;
PRINT(@discription);
SELECT @startIndex=@startIndex+1--当前记录加1
END