Skip to content

��ҳ����

���ĵ���ϸ���� cDataBase ��ķ�ҳ��ѯ���ܣ�֧�� SQL Server��MySQL��Access �����ݿ⡣


? Ŀ¼


��ҳ����

ʲô�Ƿ�ҳ

��ҳ�ǽ��������ݷֳɶ��ҳ����ʾ�ļ�����ÿҳ��ʾ�̶������ļ�¼��

��ҳ������

  • �����Ż� - ֻ��ѯ��Ҫ�����ݣ������ڴ�ռ��
  • �û����� - ���ټ��أ����ⳤʱ��ȴ�
  • ��Դ��Լ - �������紫������ݿ⸺��

��ҳԭ��

�ܼ�¼��: 1000
ÿҳ��ʾ: 10
��ҳ��: 100

�� 1 ҳ: ��¼ 1-10   (OFFSET 0, LIMIT 10)
�� 2 ҳ: ��¼ 11-20  (OFFSET 10, LIMIT 10)
�� 3 ҳ: ��¼ 21-30  (OFFSET 20, LIMIT 10)
...

Page ����

�﷨

vb
Function Page(Optional num As Long = 1, Optional Limit As Long = 10) As cDataBase

����˵��

��������˵��
numLongҳ�루��ѡ��Ĭ�� 1��
LimitLongÿҳ��¼������ѡ��Ĭ�� 10��

����ֵ

���� cDataBase ����֧����ʽ���á�

ʾ��

vb
' ��ѯ�� 1 ҳ��ÿҳ 10 ��
db.Sql("SELECT * FROM users").Page(1, 10).Query

' ��ѯ�� 2 ҳ��ÿҳ 20 ��
db.Sql("SELECT * FROM users").Page(2, 20).Query

֧�ֵ����ݿ�

SQL Server (2012+)

ʹ�� OFFSET ... ROWS FETCH NEXT ... ROWS ONLY �﷨��

vb
' ԭʼ SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' �Զ�ת��Ϊ
SELECT * FROM users 
ORDER BY (SELECT NULL)
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

ע��: SQL Server 2012 �����ϰ汾��֧�� OFFSET FETCH��

MySQL

ʹ�� LIMIT ... OFFSET ... �﷨��

vb
' ԭʼ SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' �Զ�ת��Ϊ
SELECT * FROM users 
LIMIT 10 OFFSET 10

Access

ʹ�� TOP ���Ӳ�ѯ����ʵ�֣���

vb
' ԭʼ SQL
db.Sql("SELECT * FROM users").Page(2, 10).Query

' �Զ�ת��Ϊ
SELECT TOP 20 * FROM (
    SELECT * FROM users
) AS T

ע��: Access �ķ�ҳʵ���Ǽ򻯰汾�����ܲ�����ȫ׼ȷ�ķ�ҳ��


ʹ��ʾ��

ʾ�� 1��������ҳ

vb
' ��ѯ�� 1 ҳ��ÿҳ 10 ��
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

ʾ�� 2��������ķ�ҳ

vb
' �����併�����У���ҳ��ʾ
If db.Sql("SELECT * FROM users ORDER BY age DESC").Page(2, 10).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("age")
    Next
End If

ʾ�� 3��������ѯ��ҳ

vb
' ��ѯ������� 18 ���û�����ҳ��ʾ
If db.Sql("SELECT * FROM users WHERE age > 18").Page(1, 20).Fetch Then
    Dim i As Long
    For i = 1 To db.Rows.Count
        Debug.Print db.Rows(i)("name")
    Next
End If

ʾ�� 4����ȡ�ܼ�¼��

vb
' ��ѯ����
Dim lTotal As Long
lTotal = db.Count("users")

' ������ҳ��
Dim lPageSize As Long
lPageSize = 10
Dim lTotalPages As Long
lTotalPages = Int((lTotal + lPageSize - 1) / lPageSize)

' ��ҳ��ѯ
If db.Sql("SELECT * FROM users").Page(1, lPageSize).Fetch Then
    ' ��ʾ����
End If

ʾ�� 5��������ҳ����

vb
' ��ҳ��ѯ����
Function GetUsersPage(lPage As Long, lPageSize As Long) As Collection
    Set GetUsersPage = New Collection
    
    ' ��ѯ����
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        Set GetUsersPage = db.Rows
    End If
End Function

' ʹ��
Dim colUsers As Collection
Set colUsers = GetUsersPage(1, 10)

ʾ�� 6����ҳ����

vb
' ��ҳ������
Private m_lCurrentPage As Long
Private m_lPageSize As Long
Private m_lTotalRecords As Long

Private Sub LoadPage(lPage As Long)
    ' ��֤ҳ��
    If lPage < 1 Then lPage = 1
    
    Dim lTotalPages As Long
    lTotalPages = Int((m_lTotalRecords + m_lPageSize - 1) / m_lPageSize)
    If lPage > lTotalPages Then lPage = lTotalPages
    
    m_lCurrentPage = lPage
    
    ' ��ѯ����
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, m_lPageSize).Fetch Then
        ' ��ʾ����
        DisplayUsers
    End If
End Sub

Private Sub cmdNextPage_Click()
    LoadPage m_lCurrentPage + 1
End Sub

Private Sub cmdPrevPage_Click()
    LoadPage m_lCurrentPage - 1
End Sub

������

1. ʹ�������ֶ�����

vb
' ? �Ƽ���ʹ�������ֶ�����
db.Sql("SELECT * FROM users ORDER BY id").Page(1, 10).Query

' ? ���Ƽ���ʹ�÷������ֶ�����
db.Sql("SELECT * FROM users ORDER BY name").Page(1, 10).Query

2. ֻ��ѯ��Ҫ���ֶ�

vb
' ? �Ƽ���ֻ��ѯ��Ҫ���ֶ�
db.Sql("SELECT id, name FROM users").Page(1, 10).Query

' ? ���Ƽ�����ѯ�����ֶ�
db.Sql("SELECT * FROM users").Page(1, 10).Query

3. ʹ�� WHERE ��������

vb
' ? �Ƽ���ʹ�� WHERE ����
db.Sql("SELECT * FROM users WHERE status = 'active'").Page(1, 10).Query

' ? ���Ƽ�����ѯ���������ٷ�ҳ
db.Sql("SELECT * FROM users").Page(1, 10).Query

4. ��������ÿҳ��¼��

vb
' ? �Ƽ���������ÿҳ��¼����10-50��
db.Sql("SELECT * FROM users").Page(1, 20).Query

' ? ���Ƽ���ÿҳ��¼������
db.Sql("SELECT * FROM users").Page(1, 1000).Query

��������

Q1: SQL Server ��ҳ����

����: 'OFFSET' �������﷨����

ԭ��: SQL Server �汾���� 2012����֧�� OFFSET FETCH��

���:

  • ������ SQL Server 2012 ����߰汾
  • ��ʹ�� ROW_NUMBER() ʵ�ַ�ҳ����Ҫ�޸������룩

Q2: Access ��ҳ��׼ȷ

ԭ��: Access �ķ�ҳʵ���Ǽ򻯰汾��ʹ�� TOP ��ѯ��

���:

  • ���� Access������ʹ�� ROW_NUMBER() ���ֶ�ʵ�ַ�ҳ
  • �������� SQL Server/MySQL

Q3: ��λ�ȡ�ܼ�¼����

vb
' ���� 1��ʹ�� Count ����
Dim lTotal As Long
lTotal = db.Count("users")

' ���� 2��ʹ�� COUNT(*) ��ѯ
If db.Sql("SELECT COUNT(*) AS cnt FROM users").Fetch Then
    lTotal = db.Row("cnt")
End If

Q4: ��ҳ����α�������

vb
' ? ��ȷ���� SQL �а��� ORDER BY
db.Sql("SELECT * FROM users ORDER BY age DESC").Page(1, 10).Query

' ? ���󣺷�ҳ�������򣨻ᶪʧ����
db.Sql("SELECT * FROM users").Page(1, 10).Query
' Ȼ��Խ������ֻ�Ե�ǰҳ���򣬲���ȫ������

Q5: ���ʵ����ת��ָ��ҳ��

vb
Function GoToPage(lPage As Long, lPageSize As Long) As Boolean
    ' ��֤ҳ��
    If lPage < 1 Then lPage = 1
    
    ' ��ѯָ��ҳ
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        GoToPage = True
    Else
        GoToPage = False
    End If
End Function

���ʵ��

1. ʼ��ʹ�� ORDER BY

vb
' ? �Ƽ���ʹ�� ORDER BY ��֤˳��
db.Sql("SELECT * FROM users ORDER BY id").Page(1, 10).Query

' ? ���Ƽ�����ʹ�� ORDER BY��˳��ȷ����
db.Sql("SELECT * FROM users").Page(1, 10).Query

2. ��֤ҳ���ÿҳ��¼��

vb
' ? �Ƽ�����֤����
Function GetPage(lPage As Long, lPageSize As Long) As Collection
    If lPage < 1 Then lPage = 1
    If lPageSize < 1 Then lPageSize = 10
    If lPageSize > 100 Then lPageSize = 100  ' �������ÿҳ��¼��
    
    If db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
        Set GetPage = db.Rows
    End If
End Function

3. �����ܼ�¼��

vb
' ? �Ƽ��������ܼ�¼��������Ƶ����ѯ
Private m_lCachedTotal As Long
Private m_dtCacheTime As Date

Function GetTotalRecords() As Long
    ' ���� 5 ����
    If DateDiff("s", m_dtCacheTime, Now) > 300 Or m_lCachedTotal = 0 Then
        m_lCachedTotal = db.Count("users")
        m_dtCacheTime = Now
    End If
    GetTotalRecords = m_lCachedTotal
End Function

������: 2026-01-21

VB6及其LOGO版权为微软公司所有