0

## 詢問while迴圈撰寫的邏輯

Book與Issuedate為獨立存在，所以No欄位放null;
Author共有兩組，因此No欄位放0與1做分別。

``````create table [dbo].[TableA](
[Id] int identity (1, 1)
, [Name] [nvarchar](max) null
, [No] bit null
)

insert TableA (
[Name], [No]
)
values
('Book', null)
, ('Author', 0)
, ('Author', 0)
, ('Author', 1)
, ('Author', 1)
, ('IssueDate',null)

select * from TableA
``````

Book
Author
Author
IssueDate

``````declare @count int = 0
, @sum int = (select count(*) from TableA)
, @Name nvarchar(4000) = null

while(@count < @sum)
begin
set @count = @count + 1
select @Name = [Name] from TableA where [Id] = @count
print @Name
end
``````

Book
Author
Author
Author
Author
IssueDate
IssueDate

ccutmis iT邦高手 4 級 ‧ 2019-04-10 17:14:00 檢舉

anniecat iT邦新手 3 級 ‧ 2019-04-10 17:15:37 檢舉

ccutmis iT邦高手 4 級 ‧ 2019-04-10 17:28:32 檢舉
@<=便便 ^^"

### 2 個回答

1
hmjh56032212
iT邦新手 5 級 ‧ 2019-04-10 17:28:50

``````
create table [dbo].[TableA](
[Id] int identity (1, 1)
, [Name] [nvarchar](max) null
, [No] bit null
)

insert TableA (
[Name], [No]
)
values
('Book', null)
, ('Author', 0)
, ('Author', 0)
, ('Author', 1)
, ('Author', 1)
, ('IssueDate',null)

declare @Name as varchar(20);
declare @NO as varchar(20);
DECLARE MYCURSOR CURSOR FOR
select Name,NO from TableA group by Name,NO order by min(id)
OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @Name,@NO
WHILE @@FETCH_STATUS = 0
BEGIN
print @Name
FETCH NEXT FROM MYCURSOR INTO  @Name,@NO
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
``````
anniecat iT邦新手 3 級 ‧ 2019-04-10 17:32:37 檢舉

anniecat iT邦新手 3 級 ‧ 2019-04-10 17:44:20 檢舉

``````declare @tempTable table(
[Id] int identity (1, 1)
, [Name] [nvarchar](max) null
, [No] bit null
)

insert @tempTable
select [Name],[No] from TableA group by [Name], [No]

declare @count int = 0
, @sum int = (select count(*) from @tempTable )
, @Name nvarchar(4000) = null

while(@count < @sum)
begin
set @count = @count + 1
select @Name = [Name] from @tempTable where [Id] = @count
print @Name
end
``````

Author
Author
Book
IssueDate

1
shinobi719
iT邦見習生 ‧ 2019-04-12 23:31:18

ALTER PROCEDURE [dbo].[test]
AS
/* 設定參數 */
DECLARE @Name VARCHAR(10), @No Varchar(10)

--設定 cursor
DECLARE _Tmp1 CURSOR FOR

--SQL
select [Name],[No] from TableA group by [Name], [No]

--open cursor
OPEN _Tmp1

--迴圈
FETCH NEXT FROM _Tmp1
INTO @Name,@No --接值
WHILE (@@FETCH_STATUS=0)
BEGIN
print(@Name) --列印

FETCH NEXT FROM _Tmp1
INTO @Name,@No --接值
END

--關閉 cursor
CLOSE _Tmp1
DEALLOCATE _Tmp1

GO

--上述是可以使用的，註解寫得不好，請鞭小力點...

anniecat iT邦新手 3 級 ‧ 2019-04-15 09:53:17 檢舉

``````declare @tempTable table(
[Id] int identity (1, 1)
, [Name] [nvarchar](max) null
, [No] bit null
)

insert @tempTable
select [Name],[No] from TableA group by [Name], [No]

declare @count int = 0
, @sum int = (select count(*) from @tempTable )
, @Name nvarchar(4000) = null

while(@count < @sum)
begin
set @count = @count + 1
select @Name = [Name] from @tempTable where [Id] = @count
print @Name
end
``````

Author
Author
Book
IssueDate