詢問while迴圈撰寫的邏輯

mssql sqlserver sql while

anniecat 2019-04-10 17:04:08 ‧ 3766 瀏覽

分享至

我創建了一個測試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

若排除資料結構的問題，我想寫一個迴圈，目的是可以印出以下結果:
讀到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邦高手 2 級 ‧ 2019-04-10 17:14:00 檢舉

怎麼一堆便便!?@@

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

不好意思，請問便便的意思是?

ccutmis iT邦高手 2 級 ‧ 2019-04-10 17:28:32 檢舉

@<=便便 ^^"

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

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

回應 2
分享
檢舉

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

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

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

登入發表回應

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

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

回應 1
分享
檢舉

anniecat iT邦新手 3 級 ‧ 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