SQL 實現統計報表小計與合計：臨時表方案完整實戰

需求場景

按某維度（如資產類型）分組，每組顯示明細行，組末顯示小計，最後顯示總計。原始表結構包含數值列（數量、金額等）需匯總。

三種方案對比

臨時表方案完整實現

-- 1. 複製原始數據到臨時表
SELECT * INTO #TB FROM TB;

-- 2. 創建結果表（結構同源表，無數據）
SELECT * INTO #TB1 FROM #TB WHERE 1<>1;

-- 3. 獲取分組維度去重並編號
SELECT DISTINCT zcxt INTO #TBype FROM #TB ORDER BY zcxt;
SELECT IDENTITY(INT,1,1) fid, zcxt INTO #TBype1 FROM #TBype;

-- 4. 循環處理每個分組
DECLARE @i INT, @k INT = 0, @strfname VARCHAR(50);
SELECT @i = COUNT(*) FROM #TBype;

WHILE @k < @i
BEGIN
    SET @k = @k + 1;
    SELECT @strfname = zcxt FROM #TBype1 WHERE fid = @k;

    -- 插入明細 + 小計
    SET IDENTITY_INSERT #TB1 ON;
    INSERT INTO #TB1(fid, qldid, fa_cardid, ztbz, fa_name, model, i_number, gzrq, zcyz, ljzj, jz, sybm, zcxt, fa_ljjzzb)
    SELECT fid, qldid, fa_cardid, ztbz, fa_name, model, i_number, gzrq, zcyz, ljzj, jz, sybm, zcxt, fa_ljjzzb FROM (
        -- 明細行
        SELECT * FROM #TB WHERE zcxt = @strfname
        UNION ALL
        -- 小計行
        SELECT 
            0, '', '', '', '小計', '', 
            SUM(i_number), '', 
            SUM(CAST(zcyz AS money)), 
            SUM(CAST(ljzj AS money)), 
            SUM(CAST(jz AS money)), 
            '', '', SUM(fa_ljjzzb)
        FROM #TB WHERE zcxt = @strfname
        GROUP BY ztbz
    ) AS B;
    SET IDENTITY_INSERT #TB1 OFF;
END

-- 5. 輸出結果 + 總計
SELECT qldid, fa_cardid, zcxt, fa_name, model, i_number, gzrq, zcyz, ljzj, jz, sybm, ztbz, fa_ljjzzb FROM #TB1
UNION ALL
SELECT '', '', '', '合計', '', SUM(i_number), '', SUM(CAST(zcyz AS money)), SUM(CAST(ljzj AS money)), SUM(CAST(jz AS money)), '', '', SUM(fa_ljjzzb) FROM #TB;

-- 6. 清理
DROP TABLE #TB1, #TBype1, #TBype, #TB;

關鍵技術點解析

• SELECT * INTO #temp FROM src WHERE 1<>1：僅複製結構不複製數據
• IDENTITY(INT,1,1)：為分組維度生成連續編號，便於循環
• SET IDENTITY_INSERT ON：允許顯式插入自增列值（小計行 fid=0）
• UNION ALL：保留重複行，明細與小計合併
• CAST(... AS money)：確保金額匯總精度

擴展建議

可封裝為通用存儲過程 sp_AddSubTotalGrandTotal @SourceTable, @GroupField, @SumFields，動態構建 SQL 實現任意表的小計合計。