ASP Access/SQL Server 讀取指定筆數記錄：TOP N 與分頁技巧完整教學

資料庫讀取前 N 筆記錄的標準寫法

在 ASP 搭配 Access 或 SQL Server 時，讀取最新的 N 筆記錄是常見需求 (如最新公告、最新文章)。不同資料庫語法略有差異。

Access 資料庫：TOP 關鍵字

<%
Dim sqlnote2, conn, rs
' 取得最新 8 筆，依 id 遞減排序
sqlnote2 = "SELECT TOP 8 * FROM note1 ORDER BY id DESC"

Call opendb() ' 假設為自訂開啟連線函式
rs.Open sqlnote2, conn, 1, 1 ' adOpenKeyset, adLockReadOnly

If Not rs.EOF Then
    Do While Not rs.EOF
%>
    <tr>
        <td width="5" height="22" align="left" bgcolor="#FFFFFF">&nbsp;</td>
        <td width="169" height="27" align="left" bgcolor="#FFFFFF">
            <img src="index_files/dot_email.gif" width="5" height="5" />&nbsp;
            <a href="<%=rs("file_path")%>" target="_blank" class="prodcut" title="<%=rs("title")%>">
                <%=rs("title")%>
            </a>&nbsp;&nbsp;
            <%
            ' 今日發布顯示 NEW 圖示
            If rs("send_date") = Date() Then
                Response.Write("")
            Else
                Response.Write(rs("send_date"))
            End If
            %>
        </td>
        <td width="1" height="5" align="left" bgcolor="#FFFFFF">&nbsp;</td>
    </tr>
<%
        rs.MoveNext
    Loop
%>
    <tr>
        <td colspan="3" align="right" bgcolor="#FFFFFF">
            <a href="note.asp" class="page">更多資訊...</a>&nbsp;&nbsp;&nbsp;
        </td>
    </tr>
<%
Else
    ' 無資料時顯示
%>
    <tr class="button3">
        <td colspan="3" bgcolor="#FFFFFF">暫時沒有通知公告</td>
    </tr>
<%
End If

Call closedb()
%>

SQL Server：TOP 與 ORDER BY

<%
' SQL Server 語法相同，但支援 TOP N PERCENT
sql = "SELECT TOP 10 PERCENT * FROM Articles ORDER BY PubDate DESC"
' 或搭配 WITH TIES 處理同序值
sql = "SELECT TOP 5 WITH TIES * FROM Products ORDER BY UnitPrice DESC"
%>

MySQL/PostgreSQL：LIMIT 子句

<%
' 若使用 MySQL (透過 ODBC/OLEDB)
sql = "SELECT * FROM notes ORDER BY id DESC LIMIT 8"
%>

進階：通用分頁函式 (支援 Access/SQL Server/MySQL)

Function GetPagedRecordset(sql, pageNo, pageSize, conn, dbType)
    ' dbType: "access", "sqlserver", "mysql"
    Dim rs, startRow, endRow, finalSQL
    
    If pageNo < 1 Then pageNo = 1
    startRow = (pageNo - 1) * pageSize + 1
    endRow = pageNo * pageSize
    
    Select Case LCase(dbType)
        Case "access"
            ' Access 使用子查詢 + TOP 模擬分頁 (效能較差)
            finalSQL = "SELECT * FROM (" & _
                       "SELECT TOP " & pageSize & " * FROM (" & _
                       "SELECT TOP " & endRow & " * FROM (" & sql & ") " & _
                       "ORDER BY sortField DESC) " & _
                       "ORDER BY sortField ASC) " & _
                       "ORDER BY sortField DESC"
                       
        Case "sqlserver"
            ' SQL Server 2012+ 使用 OFFSET FETCH (標準 SQL)
            finalSQL = sql & " ORDER BY sortField OFFSET " & (startRow - 1) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY"
            ' SQL Server 2008/2005 使用 ROW_NUMBER()
            ' finalSQL = "WITH CTE AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sortField) AS rn FROM (" & sql & ")) SELECT * FROM CTE WHERE rn BETWEEN " & startRow & " AND " & endRow
            
        Case "mysql"
            finalSQL = sql & " ORDER BY sortField LIMIT " & (startRow - 1) & ", " & pageSize
            
        Case Else
            Err.Raise vbObjectError + 513, "GetPagedRecordset", "Unsupported dbType: " & dbType
    End Select
    
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open finalSQL, conn, 1, 1 ' adOpenKeyset, adLockReadOnly
    Set GetPagedRecordset = rs
End Function

' 使用範例：
' Dim rsNews
' Set rsNews = GetPagedRecordset("SELECT * FROM News WHERE IsPub=1", 1, 10, conn, "access")
%>

Recordset 游標與鎖定類型參數說明 (rs.Open 參數 3, 4)

常用組合推薦

• 唯讀列表/分頁：rs.Open sql, conn, 1, 1 (Keyset + ReadOnly) - 最推薦
• 高效能唯讀：rs.Open sql, conn, 0, 1 (ForwardOnly + ReadOnly) - 無法取得 RecordCount
• 編輯/新增/刪除：rs.Open sql, conn, 1, 3 (Keyset + Optimistic) 或 3, 3 (Static + Optimistic)
• 大量資料唯讀串流：rs.Open sql, conn, 0, 1 + rs.CacheSize = 100

效能優化關鍵點

1. 只選需要欄位：SELECT id, title, send_date 而非 SELECT *
2. 善用索引：ORDER BY 欄位必須有索引
3. 關閉游標：用完立即 rs.Close: Set rs = Nothing
4. 連線共用：單頁多查詢共用同一 Connection 物件
5. 考慮快取：熱門列表頁面輸出 HTML 片段快存至 Application/檔案/Redis