萬盛學電腦網

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

ASP 獲取 SELECT COUNT(*) 返回值:別名法與 Execute 模式完美解決

統計記錄總數是分頁、報表、儀表盤的基礎操作。ASP 中執行 SELECT COUNT(*) 後,如何正確取得返回值?關鍵在於給聚合列起別名。

核心問題

' ❌ 錯誤:無法通過索引或原始表達式獲取
Set rs = conn.Execute("SELECT COUNT(*) FROM Users")
Response.Write rs(0)          ' 可能報錯或返回空
Response.Write rs("COUNT(*)") ' 語法錯誤 (含特殊字符)

標準解法:AS 別名

<%
' ✅ 正確:給 COUNT(*) 起別名 Total
Set rs = conn.Execute("SELECT COUNT(*) AS Total FROM Users")
Response.Write "總用戶數:" & rs("Total")

' 或使用縮寫
Response.Write conn.Execute("SELECT COUNT(*) AS Total FROM Users")("Total")
%>

完整封裝函數

<%
Function GetRecordCount(conn, tableName, Optional whereClause)
    Dim sql, rs, count
    sql = "SELECT COUNT(*) AS Total FROM " & tableName
    If IsMissing(whereClause) = False And Trim(whereClause) <> "" Then
        sql = sql & " WHERE " & whereClause
    End If
    
    Set rs = conn.Execute(sql)
    If Not rs.EOF Then
        count = rs("Total")
    Else
        count = 0
    End If
    rs.Close
    Set rs = Nothing
    GetRecordCount = count
End Function

' 調用範例
Dim totalUsers, activeUsers
totalUsers = GetRecordCount(conn, "Users")
activeUsers = GetRecordCount(conn, "Users", "Status=1 AND LastLogin > DATEADD('d',-30,NOW())")
Response.Write "總計:" & totalUsers & ",活躍:" & activeUsers
%>

進階:單次查詢獲取多個統計值

<%
Set rs = conn.Execute("SELECT " & _
    "COUNT(*) AS Total, " & _
    "SUM(Amount) AS SumAmount, " & _
    "AVG(Amount) AS AvgAmount, " & _
    "MAX(CreateTime) AS Latest, " & _
    "MIN(CreateTime) AS Earliest " & _
    "FROM Orders WHERE Status = 1")

If Not rs.EOF Then
    Response.Write "訂單總數:" & rs("Total") & "<br>"
    Response.Write "總金額:" & FormatCurrency(rs("SumAmount")) & "<br>"
    Response.Write "平均金額:" & FormatNumber(rs("AvgAmount"), 2) & "<br>"
    Response.Write "最新下單:" & rs("Latest") & "<br>"
    Response.Write "最早下單:" & rs("Earliest")
End If
rs.Close
%>

效能對比:COUNT(*) vs RecordCount

| 方法 | SQL 執行 | 網路傳輸 | 適用場景 | |------|----------|----------|----------| | SELECT COUNT(*) | 聚合掃描 (可用索引) | 1 行 1 列 | **純統計總數**,最快 | | rs.Open "SELECT * ...": rs.RecordCount | 全表掃描 + 傳輸所有列 | 全量數據 | 需同時讀取數據內容時 |

常見陷阱

  • ❌ rs.RecordCount 在 adOpenForwardOnly (默認) 返回 -1 → 必須用 adOpenStatic(3) 或 adOpenKeyset(1)
  • ❌ Access 中 COUNT(*) 對 Memo/Blob 欄位較慢 → 改用 COUNT(ID) (主鍵/非空列)
  • ❌ 並發環境下統計與實際讀取不一致 → 採用事務隔離或樂觀鎖版本號

分頁場景完整模式

<%
Dim pageSize, pageNum, totalRecords, totalPages
pageSize = 20
pageNum = CInt(Request("page")): If pageNum < 1 Then pageNum = 1

' 1. 統計總數
totalRecords = GetRecordCount(conn, "Articles", "Status=1")
totalPages = Int((totalRecords + pageSize - 1) / pageSize)  ' 向上取整

' 2. 邊界修正
If pageNum > totalPages Then pageNum = totalPages

' 3. 分頁查詢 (SQL Server 2012+ 標準語法)
sql = "SELECT Id, Title, CreateTime FROM Articles " & _
      "WHERE Status=1 ORDER BY CreateTime DESC " & _
      "OFFSET " & (pageNum-1)*pageSize & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY"

Set rs = conn.Execute(sql)
' ... 輸出列表 ...

' 4. 渲染分頁條 (參考文章 66774)
%>

「給 COUNT 起別名」是 ASP 開發者的肌肉記憶,配合封裝函數,讓統計查詢零失誤、高複用。

copyright © 萬盛學電腦網 all rights reserved