不確定類別次數INSERT TABLE- 藍色小舖 BlueShop

台灣最大程式設計社群網站

∣免費加入會員∣登入 ∣回首頁∣ 您好

線上人數
1265

會員總數：246229
討論主題：189787

登入

登出

歡迎您免費加入會員

討論區選單

開發工具

行動裝置開發

資料庫

多媒體 / 網管

綜合

文章區

討論區列表 >> MS SQL >> 不確定類別次數INSERT TABLE

[]

[我要回覆]

不確定類別次數INSERT TABLE
價值 : 50 QP 點閱數:839 回應數:11

樓主

Authur Kuo

0	4
64	6

發送站內信

不好意思，有一個問題提問，目前我在寫一隻store procedure，目的是去偵測某狀態下，input day 與現在日期的差距(30,60,90天)，但是當中某個欄位是 Customer，但是在幾萬筆的資料裡面，我不知道Customer有幾家，但是每一家都需要INSERT(30,60,90天各一筆資料)，請問我該如何下手?

以下是未加上 Customer的狀態

declare
	@id       int,
	@count    int,
    @30count  int,
	@60count  int,
	@90count  int,
	@customer varchar(50),
	@year     varchar(10),
	@month    varchar(10),    --月份
	@nowtime  datetime     --目前時間	
	
 select
	@id = 0,
	@count = 0,
	@30count = 0,
	@60count = 0,
	@90count = 0,
	@customer = '',
	@year =  year(getdate()),
	@month = month(getdate()),
	@nowtime = getdate()		
	
--計算差距日期
	SELECT @30count=Count(*) FROM SNO where Status = 'O' and (DATEDIFF(day, Cdt ,@nowtime)>30)  --兩者間日期的差距(30天)
	SELECT @60count=Count(*) FROM SNO where Status = 'O' and (DATEDIFF(day, Cdt ,@nowtime)>60)  --兩者間日期的差距(60天)
	SELECT @90count=Count(*) FROM SNO where Status = 'O' and (DATEDIFF(day, Cdt ,@nowtime)>90)  --兩者間日期的差距(90天)

--檢查有沒有該月份紀錄，如果有，更新該筆資料，如果沒有，INSERT紀錄進去

--30 Days
SELECT @count=Count(*) FROM RMA_AGED_DAYS WHERE [Month] = @month and [Year] = @year and [Type] = '30' 
if @count > 0 begin 
	select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '30'
	UPDATE RMA_AGED_DAYS SET Qty = @30count, Udt = @nowtime  WHERE Id = @id
end
else begin
	INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Customer, Udt) VALUES (@year, @month, '30', @30count, @nowtime)
end	
	
--60 Days
SELECT @count=Count(*) FROM RMA_AGED_DAYS WHERE [Month] = @month and [Year] = @year and [Type] = '60' 
if @count > 0 begin 
	select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '60'
	UPDATE RMA_AGED_DAYS SET Qty = @60count, Udt = @nowtime  WHERE Id = @id
end
else begin
	INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '60', @60count, @nowtime)
end

--90 Days
SELECT @count=Count(*) FROM RMA_AGED_DAYS WHERE [Month] = @month and [Year] = @year and [Type] = '90' 
if @count > 0 begin 
	select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '90'
	UPDATE RMA_AGED_DAYS SET Qty = @90count, Udt = @nowtime  WHERE Id = @id
end
else begin
	INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '90', @90count, @nowtime)
end

請問該如何改寫比較好???

搜尋相關Tags的文章： [ INSERT ] , [ MS SQL ] ,
本篇文章發表於2016-06-30 11:56

1樓回應 Daimom	用cursor，大約會長這樣。 declare hourRange cursor for select datediff(hour,s_starttime,e_stoptime),starttime,stoptime from aa declare @hourCount int,@start datetime,@end datetime open hourRange fetch next from hourRange into @hourCount,@start,@end WHILE @@FETCH_STATUS = 0 begin if @hourCount > 1 begin ... end fetch next from hourRange into @hourCount,@start,@end end close hourRange DEALLOCATE hourRange 本篇文章回覆於2016-06-30 12:14 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
2樓作者回應 Authur Kuo	很抱歉，關於Daimom的寫法我不是很懂，我如果要處理Customer的數量的話，需要用到DISTINCT嗎? 該在哪裡先撈取? 抱歉，有點混亂，如果可以希望解釋簡單一些，不好意思@@.. 本篇文章回覆於2016-06-30 13:14 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
3樓回應 pilipala	利用 CROSS JOIN 可以找出每個客戶和日期差異的全部組合 DECLARE @Customer TABLE (CustomerName nchar(100)) INSERT INTO @Customer (CustomerName) VALUES(N'A') INSERT INTO @Customer (CustomerName) VALUES(N'B') INSERT INTO @Customer (CustomerName) VALUES(N'C') INSERT INTO @Customer (CustomerName) VALUES(N'D') INSERT INTO @Customer (CustomerName) VALUES(N'E') DECLARE @Range TABLE (DD tinyint) INSERT INTO @Range (DD) VALUES(30) INSERT INTO @Range (DD) VALUES(60) INSERT INTO @Range (DD) VALUES(90) SELECT * FROM @Customer AS C CROSS JOIN @Range AS R ORDER BY CustomerName , DD 使用交叉聯結 https://technet.microsoft.com/zh-tw/library/ms190690(v=sql.105).aspx 本篇文章回覆於2016-06-30 13:21 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
4樓回應 Daimom	cursor是指標，有點像是迴圈逐行跑。步驟是這樣的 1.可以先利用樓上大大的方法擷取出你的customerName成一個表A 2.用while 針對表A一行一行的將customerName取出來後做處理 3.可以將處理完的值塞入一個temptable或其他.. 4.結束本篇文章回覆於2016-06-30 13:31 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
5樓回應 Daimom	抱歉，我記錯cross join的用法了.. 我不清楚 pilipala 大大的作法是不是你要的。 -----修正#4 1.用distinct撈出你customerName的唯一值成一個表A 本篇文章回覆於2016-06-30 13:43 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
6樓回應 pilipala	SELECT @count=Count() FROM RMA_AGED_DAYS WHERE [Month] = @month and [Year] = @year and [Type] = '90' if @count > 0 begin select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '90' UPDATE RMA_AGED_DAYS SET Qty = @90count, Udt = @nowtime WHERE Id = @id end else begin INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '90', @90count, @nowtime) end 利用 EXISTS 來判斷資料是否存在， IF EXISTS ( SELECT FROM TableName WHERE Condition ) BEGIN -- 資料存在 END ELSE BEGIN -- 資料不存在 END 或是改用 Merge 語法來完成這類需求，對於效能是有所幫助的使用 MERGE 插入、更新，和刪除資料 https://technet.microsoft.com/zh-tw/library/bb522522(v=sql.105).aspx 本篇文章回覆於2016-06-30 14:01 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
7樓作者回應 Authur Kuo	不好意思，針對pilipala大的方式有些疑問，我目前是用一個#temp 撈出需要處理的CustomerName, 接下來按照Daimom大得作法我該怎麼做?? --撈出需要處理的Customer Name create table #temp ( NAME nvarchar(20) ) insert into #temp (NAME) select distinct Customer from SNO where Status = 'O' -- 建立一個CURSOR , cur 為此CURSOR 的名稱(如果只計算>30天就好) DECLARE cur CURSOR LOCAL FOR SELECT @30count=Count() FROM SNO where Status = 'O' and (DATEDIFF(day, Cdt ,@nowtime)>30) --超過30天的所有總數 BEGIN OPEN cur --開啟 CURSOR FETCH NEXT FROM cur INTO #temp (NAME) --讀取第一筆資料 WHILE @@FETCH_STATUS = 0 BEGIN -- 迴圈內要執行的動作例如 SELECT @count=Count() FROM RMA_AGED_DAYS WHERE [Type] = '30' --檢查有沒有那筆資料 if @count > 0 begin select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '30' UPDATE RMA_AGED_DAYS SET Qty = @30count WHERE Id = @id --如果有，update end else begin INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '30', @30count, @nowtime) --如果沒有，Insert一筆進去 end FETCH NEXT FROM cur INTO #temp (NAME) --讀取下一筆資料 END CLOSE cur --關閉cur DEALLOCATE cur --釋放cur 請問我這樣寫的邏輯是否正確?? 求指教本篇文章回覆於2016-06-30 14:47 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
8樓最有價值解答 Daimom	給樓主一個範例，先將他丟去sql server management裡面跑跑看。這是將本來的值多加100後在存去新的table declare @t table (sn int primary key NOT NULL identity(1,1),name varchar(10),category varchar(20),amt int) declare @tt table (sn int primary key NOT NULL identity(1,1),name varchar(10),category varchar(20),amt int) insert into @t values ('Liu','Car',5000),('Chiu','Enforcement',3000),('Wang','Car',4000),('Liu','Enforcement',2500) declare hourRange cursor for SELECT amt,category,name from @t --group by DATEADD(minute,DATEDIFF(minute,0,startTime)/6060,0) declare @hourCount int,@category varchar(20),@name varchar(20) open hourRange fetch next from hourRange into @hourCount,@category,@name WHILE @@FETCH_STATUS = 0 begin insert into @tt select @name,@category,@hourCount+100 fetch next from hourRange into @hourCount,@category,@name end close hourRange DEALLOCATE hourRange select from @tt 本篇文章回覆於2016-06-30 16:33 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
9樓作者回應 Authur Kuo	感謝Daimom 大的範例，獲益良多，只是有一個疑問就是使用cursor的優勢是甚麼? 因為我發現在Store Procedure裡跑 While迴圈也可以跑出資料，是在效能上面有所差別嗎?? 還是說某些狀況下使用cursor會比較好?? 本篇文章回覆於2016-07-01 13:47 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
10樓回應 Daimom	cursor 可以看這篇 cursor 講白話點就是『目前指到的單筆資料』本篇文章回覆於2016-07-01 15:00 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
11樓回應 pilipala	[SQL SERVER][Performance]盡量避免使用Cursor https://dotblogs.com.tw/ricochen/archive/2010/11/09/19323.aspx [SQL SERVER]優化Cursor https://dotblogs.com.tw/ricochen/archive/2015/09/22/153413.aspx 本篇文章回覆於2016-07-01 21:43 == 簽名檔 == --未登入的會員無法查看對方簽名檔--

回覆

如要回應,請先登入.

1樓回應 Daimom	用cursor，大約會長這樣。 declare hourRange cursor for select datediff(hour,s_starttime,e_stoptime),starttime,stoptime from aa declare @hourCount int,@start datetime,@end datetime open hourRange fetch next from hourRange into @hourCount,@start,@end WHILE @@FETCH_STATUS = 0 begin if @hourCount > 1 begin ... end fetch next from hourRange into @hourCount,@start,@end end close hourRange DEALLOCATE hourRange 本篇文章回覆於2016-06-30 12:14 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
2樓作者回應 Authur Kuo	很抱歉，關於Daimom的寫法我不是很懂，我如果要處理Customer的數量的話，需要用到DISTINCT嗎? 該在哪裡先撈取? 抱歉，有點混亂，如果可以希望解釋簡單一些，不好意思@@.. 本篇文章回覆於2016-06-30 13:14 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
3樓回應 pilipala	利用 CROSS JOIN 可以找出每個客戶和日期差異的全部組合 DECLARE @Customer TABLE (CustomerName nchar(100)) INSERT INTO @Customer (CustomerName) VALUES(N'A') INSERT INTO @Customer (CustomerName) VALUES(N'B') INSERT INTO @Customer (CustomerName) VALUES(N'C') INSERT INTO @Customer (CustomerName) VALUES(N'D') INSERT INTO @Customer (CustomerName) VALUES(N'E') DECLARE @Range TABLE (DD tinyint) INSERT INTO @Range (DD) VALUES(30) INSERT INTO @Range (DD) VALUES(60) INSERT INTO @Range (DD) VALUES(90) SELECT * FROM @Customer AS C CROSS JOIN @Range AS R ORDER BY CustomerName , DD 使用交叉聯結 https://technet.microsoft.com/zh-tw/library/ms190690(v=sql.105).aspx 本篇文章回覆於2016-06-30 13:21 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
4樓回應 Daimom	cursor是指標，有點像是迴圈逐行跑。步驟是這樣的 1.可以先利用樓上大大的方法擷取出你的customerName成一個表A 2.用while 針對表A一行一行的將customerName取出來後做處理 3.可以將處理完的值塞入一個temptable或其他.. 4.結束本篇文章回覆於2016-06-30 13:31 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
5樓回應 Daimom	抱歉，我記錯cross join的用法了.. 我不清楚 pilipala 大大的作法是不是你要的。 -----修正#4 1.用distinct撈出你customerName的唯一值成一個表A 本篇文章回覆於2016-06-30 13:43 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
6樓回應 pilipala	SELECT @count=Count() FROM RMA_AGED_DAYS WHERE [Month] = @month and [Year] = @year and [Type] = '90' if @count > 0 begin select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '90' UPDATE RMA_AGED_DAYS SET Qty = @90count, Udt = @nowtime WHERE Id = @id end else begin INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '90', @90count, @nowtime) end 利用 EXISTS 來判斷資料是否存在， IF EXISTS ( SELECT FROM TableName WHERE Condition ) BEGIN -- 資料存在 END ELSE BEGIN -- 資料不存在 END 或是改用 Merge 語法來完成這類需求，對於效能是有所幫助的使用 MERGE 插入、更新，和刪除資料 https://technet.microsoft.com/zh-tw/library/bb522522(v=sql.105).aspx 本篇文章回覆於2016-06-30 14:01 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
7樓作者回應 Authur Kuo	不好意思，針對pilipala大的方式有些疑問，我目前是用一個#temp 撈出需要處理的CustomerName, 接下來按照Daimom大得作法我該怎麼做?? --撈出需要處理的Customer Name create table #temp ( NAME nvarchar(20) ) insert into #temp (NAME) select distinct Customer from SNO where Status = 'O' -- 建立一個CURSOR , cur 為此CURSOR 的名稱(如果只計算>30天就好) DECLARE cur CURSOR LOCAL FOR SELECT @30count=Count() FROM SNO where Status = 'O' and (DATEDIFF(day, Cdt ,@nowtime)>30) --超過30天的所有總數 BEGIN OPEN cur --開啟 CURSOR FETCH NEXT FROM cur INTO #temp (NAME) --讀取第一筆資料 WHILE @@FETCH_STATUS = 0 BEGIN -- 迴圈內要執行的動作例如 SELECT @count=Count() FROM RMA_AGED_DAYS WHERE [Type] = '30' --檢查有沒有那筆資料 if @count > 0 begin select @id = Max(Id) FROM RMA_AGED_DAYS where [Type] = '30' UPDATE RMA_AGED_DAYS SET Qty = @30count WHERE Id = @id --如果有，update end else begin INSERT RMA_AGED_DAYS ([Year], [Month], [Type], Qty, Udt) VALUES (@year, @month, '30', @30count, @nowtime) --如果沒有，Insert一筆進去 end FETCH NEXT FROM cur INTO #temp (NAME) --讀取下一筆資料 END CLOSE cur --關閉cur DEALLOCATE cur --釋放cur 請問我這樣寫的邏輯是否正確?? 求指教本篇文章回覆於2016-06-30 14:47 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
8樓最有價值解答 Daimom	給樓主一個範例，先將他丟去sql server management裡面跑跑看。這是將本來的值多加100後在存去新的table declare @t table (sn int primary key NOT NULL identity(1,1),name varchar(10),category varchar(20),amt int) declare @tt table (sn int primary key NOT NULL identity(1,1),name varchar(10),category varchar(20),amt int) insert into @t values ('Liu','Car',5000),('Chiu','Enforcement',3000),('Wang','Car',4000),('Liu','Enforcement',2500) declare hourRange cursor for SELECT amt,category,name from @t --group by DATEADD(minute,DATEDIFF(minute,0,startTime)/6060,0) declare @hourCount int,@category varchar(20),@name varchar(20) open hourRange fetch next from hourRange into @hourCount,@category,@name WHILE @@FETCH_STATUS = 0 begin insert into @tt select @name,@category,@hourCount+100 fetch next from hourRange into @hourCount,@category,@name end close hourRange DEALLOCATE hourRange select from @tt 本篇文章回覆於2016-06-30 16:33 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
9樓作者回應 Authur Kuo	感謝Daimom 大的範例，獲益良多，只是有一個疑問就是使用cursor的優勢是甚麼? 因為我發現在Store Procedure裡跑 While迴圈也可以跑出資料，是在效能上面有所差別嗎?? 還是說某些狀況下使用cursor會比較好?? 本篇文章回覆於2016-07-01 13:47 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
10樓回應 Daimom	cursor 可以看這篇 cursor 講白話點就是『目前指到的單筆資料』本篇文章回覆於2016-07-01 15:00 == 簽名檔 == --未登入的會員無法查看對方簽名檔--
11樓回應 pilipala	[SQL SERVER][Performance]盡量避免使用Cursor https://dotblogs.com.tw/ricochen/archive/2010/11/09/19323.aspx [SQL SERVER]優化Cursor https://dotblogs.com.tw/ricochen/archive/2015/09/22/153413.aspx 本篇文章回覆於2016-07-01 21:43 == 簽名檔 == --未登入的會員無法查看對方簽名檔--