---
title : TSQL資料查詢加入統計資料
tags: SQL Server、TSQL、查詢加入統計資料
creat-date: 2022-04-07
update_date : 2022-04-07
---

---
## TSQL資料查詢加入統計資料
---

### 一、目的
**資料列表中顯示小計資料，包含「件數合計」與「經費合計」。**

### 二、建立函數

CREATE FUNCTION [dbo].[FN_MultiPersonEngData]
(@CategoryNames nvarchar(Max),@Ntimes nvarchar(MAX),@StartEngokd nvarchar(MAX),@EndEngokd nvarchar(MAX))

RETURNS @Eng_Table TABLE
    ([FlowNo] [nvarchar](50) NULL,
     [person] [nvarchar](50) NULL,
     [Eng_id] [nvarchar](50) NULL, 
     [eng_name] [nvarchar](510) NULL,     
     [Price] [money] NULL)
BEGIN 
    Declare @TempStr nvarchar(MAX)
    Declare @Total   int
    Declare @TotalPrice money

    -- @CategoryNames 有包含逗號就一直執行迴圈
    WHILE (CHARINDEX(',',@CategoryNames)>0)
	BEGIN
	    -- 取出最前面的類別
	    SET @TempStr=SUBSTRING(@CategoryNames,1,CHARINDEX(',',@CategoryNames)-1)
	    INSERT INTO @Eng_Table
	    SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY powerful_view.Eng_id) AS FlowNo,
	    	    powerful_view.person, 
	    	    powerful_view.Eng_id, 
	    	    CASE WHEN V_ENG_SIMPLE_EXEC.eng_name IS NOT NULL THEN V_ENG_SIMPLE_EXEC.eng_name 
                         WHEN powerful_view.engname_AFL IS NOT NULL AND powerful_view.engname_AFL <> '' THEN powerful_view.engname_AFL 
                         ELSE powerful_view.sug_reason END AS eng_name,                
	    	    powerful_view.authorized_price AS Price 			   
	    FROM  powerful_view 
                  LEFT JOIN V_ENG_SIMPLE_EXEC ON powerful_view.eng_id = V_ENG_SIMPLE_EXEC.Eng_id 
	    WHERE (powerful_view.idclass = '立法委員') 
                  AND ((V_ENG_SIMPLE_EXEC.eng_id IS NOT Null) OR (eng_not_exist = 'Y')) 
                  AND (powerful_view.assess='F') 
                  AND ((powerful_view.PreLimitDate='已核定' AND powerful_view.authorized_price>0) 
                    OR (powerful_view.PreLimitDate='函覆/結案' AND powerful_view.authorized_price>0)) 
                  AND Ntimes = @Ntimes 
                  AND (CONVERT(DATE,powerful_view.engokd) >= CONVERT(DATE, @StartEngokd)) 
                  AND (CONVERT(DATE,powerful_view.engokd) <= CONVERT(DATE,@EndEngokd)) 
                  AND powerful_view.person=@TempStr
	    ORDER BY powerful_view.Eng_id
		
	    -- 件數合計、經費合計
	    SELECT @Total=@@ROWCOUNT,@TotalPrice=SUM(Price)
	    FROM   @Eng_Table
	    WHERE  [person]=@TempStr	    
		
	    INSERT INTO @Eng_Table ([FlowNo],[person],[Eng_id],[eng_name],[Price]) 
	    VALUES ('','件數合計',Convert(varchar,@Total),'經費合計(千元)',@TotalPrice) 
	        
	    -- 把最前面的類別加上逗號後，取代為空字串再指派回給@CategoryNames
	    SET @CategoryNames = REPLACE(@CategoryNames,@TempStr+',','')
	END

	-- @CategoryNames有值但沒有逗號，表示此為最後一個類別
	IF(LEN(@CategoryNames)>0 And CHARINDEX(',',@CategoryNames)=0) 
	BEGIN
	    -- 取出類別
	    SET @TempStr=@CategoryNames 
	    		
	    INSERT INTO @Eng_Table
	    SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY powerful_view.Eng_id) AS FlowNo,
	    	    powerful_view.person, 
	    	    powerful_view.Eng_id, 
	    	    CASE WHEN V_ENG_SIMPLE_EXEC.eng_name IS NOT NULL THEN V_ENG_SIMPLE_EXEC.eng_name 
	    	         WHEN powerful_view.engname_AFL IS NOT NULL AND powerful_view.engname_AFL <> '' THEN powerful_view.engname_AFL 
	    	         ELSE powerful_view.sug_reason END AS eng_name,                
	    	    powerful_view.authorized_price AS Price
	    FROM   powerful_view 
                  LEFT JOIN V_ENG_SIMPLE_EXEC ON powerful_view.eng_id = V_ENG_SIMPLE_EXEC.Eng_id 
	    WHERE (powerful_view.idclass = '立法委員') 
                  AND ((V_ENG_SIMPLE_EXEC.eng_id IS NOT Null) OR (eng_not_exist = 'Y')) 
                  AND (powerful_view.assess='F') 
                  AND ((powerful_view.PreLimitDate='已核定' AND powerful_view.authorized_price>0) 
                    OR (powerful_view.PreLimitDate='函覆/結案' AND powerful_view.authorized_price>0)) 
                  AND Ntimes = @Ntimes 
                  AND (CONVERT(DATE,powerful_view.engokd) >= CONVERT(DATE, @StartEngokd)) 
                  AND (CONVERT(DATE,powerful_view.engokd) <= CONVERT(DATE,@EndEngokd)) 
                  AND powerful_view.person=@TempStr
	    ORDER BY powerful_view.Eng_id
		    
	    -- 件數合計、經費合計
	    SELECT @Total=@@ROWCOUNT,@TotalPrice=SUM(Price)
	    FROM   @Eng_Table
	    WHERE  [person]=@TempStr
		
	    INSERT INTO @Eng_Table ([FlowNo],[person],[Eng_id],[eng_name],[Price]) 
	    VALUES ('','件數合計',Convert(varchar,@Total),'經費合計(千元)',@TotalPrice) 			    
	END 

    -- 回傳table變數
    RETURN 
END

### 三、SQL查詢

SELECT * 
FROM dbo.FN_MultiPersonEngData_test(N'陳超明,葉宜津,蘇震清','9','2022/01/01','2022/04/01')


### 三、參考來源
https://dotblogs.com.tw/shadow/2011/07/02/30957