iT邦幫忙

0

詢問while迴圈撰寫的邏輯

我創建了一個測試table,程式與結果如下。
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

https://ithelp.ithome.com.tw/upload/images/20190410/20115336HJNgogyDs3.png

若排除資料結構的問題,我想寫一個迴圈,目的是可以印出以下結果:
讀到Book,有一組(null),印出一次
讀到Author發現是第一組(0),印出一次
讀到Author發現是第二組(1),印出一次
讀到IssueDate,有一組(null),印出一次

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邦新手 5 級 ‧ 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邦新手 5 級 ‧ 2019-04-10 17:32:37 檢舉

喔~對耶!我真的腦袋卡住,使用group by 就可以達到了! 感謝~

anniecat iT邦新手 5 級 ‧ 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邦見習生 0 級 ‧ 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邦新手 5 級 ‧ 2019-04-15 09:53:17 檢舉

謝謝幫忙~
後來使用while迴圈,也可以達到目標,也提供給你參考~感謝。

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

我要發表回答

立即登入回答