橄欖葉子

 

--查詢結果想要長得如下表
DEPT        NAME        AMT          SUMAMT
1          A1         10           10
2          A2         20           30
3          A3         50           80

--自建範例1開
--資料庫版本為SQL Server 2012(含)以上
--建資料表
USE [Northwind] --自行切換database name
GO

CREATE TABLE [dbo].[Table1](
	[DEPT] [smallint] NOT NULL,
	[NAME] [nvarchar](50) NULL,
	[AMT] [int] NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
	[DEPT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--寫入3筆測試資料
INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (1, N'A1', 10)
GO
INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (2, N'A2', 20)
GO
INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (3, N'A3', 50)
GO

--執行下述查詢
select DEPT,NAME,AMT,sum(AMT) over(ORDER BY DEPT) SUMAMT 
from table1 
ORDER BY DEPT

--查詢結果
DEPT	NAME	AMT	  SUMAMT
1       A1	10	    10
2       A2	20	    30
3       A3	50	    80

很不幸的，因為Marty的資料庫版本為SQL Server 2008 R2(含)以下，所以無法利用OVER子句並加上SUM函數的方式來達到想要的結果，所以另想了一個旁門左道的方式--利用自建函數。

--自建範例2開始
--資料庫版本為SQL Server 2008 R2(含)以下
--建資料表table1(包含3筆測試資料)
USE [Northwind] --自行切換database name
GO

CREATE TABLE [dbo].[Table1](
	[DEPT] [smallint] NOT NULL,
	[NAME] [nvarchar](50) NULL,
	[AMT] [int] NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
	[DEPT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (1, N'A1', 10)
GO
INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (2, N'A2', 20)
GO
INSERT [dbo].[Table1] ([DEPT], [NAME], [AMT]) VALUES (3, N'A3', 50)
GO

/*先建資料表table2，用來儲存重新排序過(用ROW_NUMBER( ) )的資料，使其有編號，因為在Function(函數)中不能使用temp table，所以建了一個實體資料表，且若資料太多，也不建議用table變數來取代實體資料表*/

--將排序過的資料匯入資料表table2
select DEPT,NAME,AMT,ROW_NUMBER() over(order by DEPT) SUM_V
into table2
from table1 ORDER BY DEPT

--查一下table2的資料
select * from table2
go

--建函數dbo.test，當傳入ROW_NUMBER( )的編號後，可以回傳該欄位的累加後的彙總值

create function dbo.test(@int int)
RETURNS int
as
begin
		declare @aaa int,@bbb int,@ccc int
		select @ccc=0

		Declare curTable CURSOR FOR
		-- Cursor 資料來源
		SELECT AMT FROM table2 where SUM_V between 1 and @int

		OPEN curTable ;
		FETCH NEXT FROM curTable INTO @aaa
			WHILE (@@FETCH_STATUS = 0)
				BEGIN

					-- 處理動作 (開始)	
					set @ccc=@ccc+@aaa
					-- 處理動作 (結束)	

					FETCH NEXT FROM curTable
					INTO  @aaa
				END;

			-- 關閉 Cursor
			CLOSE curTable;
			DEALLOCATE curTable;
RETURN(@ccc)
end
go

--執行查詢
select DEPT,NAME,AMT,dbo.test(SUM_V) as SUMAMT from table2

--查詢結果如下，雖然可以得到想要的結果，但畢竟是用Cursor + Function來處理，隨著資料量變多，效能應該也會隨之下降，沒有OVER子句並加上SUM函數的效果來得好。
DEPT	NAME	AMT	  SUMAMT
1       A1	10	   10
2       A2	20	   30
3       A3	50	   80

後來發現在SSRS中有一個RunningValue函數也可以做到累加欄位值的效果，這樣就不用寫Function來處理了，但這又是後話了。

Jay Huang