萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ASP + Access 按年按月統計資料:交叉表查詢 (Crosstab) 實作

Access 交叉表查詢 (TRANSFORM/PIVOT) 實現年月統計報表

需求:依年份為列、月份為欄顯示金額加總。Access 專用 TRANSFORM ... PIVOT 語法可直接產生交叉表,避免程式迴圈組合。本文提供完整 SQL、資料範例、ASP 執行輸出 HTML 表格代碼。

資料表結構與範例資料

d_idd_je (金額)d_sxrq (日期)
1202009-5-1
2302009-5-6
3102009-6-5
.........

Access 交叉表 SQL

TRANSFORM Sum(IIF(Month(D_SXRQ) = 1, D_JE, 0)) AS M01,
       Sum(IIF(Month(D_SXRQ) = 2, D_JE, 0)) AS M02,
       ...
       Sum(IIF(Month(D_SXRQ) = 12, D_JE, 0)) AS M12
SELECT Year(D_SXRQ) AS Y
FROM D_FY
GROUP BY Year(D_SXRQ)
PIVOT Month(D_SXRQ);

※ Access 只允許單一彙總欄位於 TRANSFORM,故需用 IIF 拆解 12 個月份,或改用程式迴圈組合。

改良版:單一 TRANSFORM + 程式組合 (效能更好)

' 1. 取得年度清單
sqlYear = "SELECT DISTINCT Year(D_SXRQ) AS Y FROM D_FY ORDER BY Y DESC"
rsYear.Open sqlYear, conn

' 2. 取得明細資料 (年度、月份、金額)
sqlDetail = "SELECT Year(D_SXRQ) AS Y, Month(D_SXRQ) AS M, Sum(D_JE) AS Total " & _
            "FROM D_FY GROUP BY Year(D_SXRQ), Month(D_SXRQ)"
rsDetail.Open sqlDetail, conn

' 3. 將明細存入 Dictionary (Key: "2024-1", Value: Total)
Dim dict, key
Set dict = Server.CreateObject("Scripting.Dictionary")
Do While Not rsDetail.EOF
    key = rsDetail("Y") & "-" & rsDetail("M")
    dict(key) = rsDetail("Total")
    rsDetail.MoveNext
Loop

' 4. 輸出 HTML 表格
Response.Write ""
For m = 1 To 12: Response.Write "": Next
Response.Write ""

Do While Not rsYear.EOF
    y = rsYear("Y")
    Response.Write ""
    For m = 1 To 12
        key = y & "-" & m
        If dict.Exists(key) Then
            Response.Write ""
        Else
            Response.Write ""
        End If
    Next
    Response.Write ""
    rsYear.MoveNext
Loop
Response.Write "
年份" & m & "月
" & y & "" & dict(key) & "0
"

SQL Server 版本 (PIVOT)

SELECT Y, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (
    SELECT Year(D_SXRQ) AS Y, Month(D_SXRQ) AS M, D_JE
    FROM D_FY
) AS SourceTable
PIVOT (SUM(D_JE) FOR M IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PivotTable
ORDER BY Y DESC;
copyright © 萬盛學電腦網 all rights reserved