統計記錄總數是分頁、報表、儀表盤的基礎操作。ASP 中執行 SELECT COUNT(*) 後,如何正確取得返回值?關鍵在於給聚合列起別名。
' ❌ 錯誤:無法通過索引或原始表達式獲取
Set rs = conn.Execute("SELECT COUNT(*) FROM Users")
Response.Write rs(0) ' 可能報錯或返回空
Response.Write rs("COUNT(*)") ' 語法錯誤 (含特殊字符)
<%
' ✅ 正確:給 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
%>
SELECT COUNT(*) | 聚合掃描 (可用索引) | 1 行 1 列 | **純統計總數**,最快 |
| rs.Open "SELECT * ...": rs.RecordCount | 全表掃描 + 傳輸所有列 | 全量數據 | 需同時讀取數據內容時 |
rs.RecordCount 在 adOpenForwardOnly (默認) 返回 -1 → 必須用 adOpenStatic(3) 或 adOpenKeyset(1)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 開發者的肌肉記憶,配合封裝函數,讓統計查詢零失誤、高複用。