��ҳ����
���ĵ���ϸ���� 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����˵��
| ���� | ���� | ˵�� |
|---|---|---|
num | Long | ҳ�루��ѡ��Ĭ�� 1�� |
Limit | Long | ÿҳ��¼������ѡ��Ĭ�� 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 10Access
ʹ�� 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).Query2. ֻ��ѯ��Ҫ���ֶ�
vb
' ? �Ƽ���ֻ��ѯ��Ҫ���ֶ�
db.Sql("SELECT id, name FROM users").Page(1, 10).Query
' ? ���Ƽ�����ѯ�����ֶ�
db.Sql("SELECT * FROM users").Page(1, 10).Query3. ʹ�� WHERE ��������
vb
' ? �Ƽ���ʹ�� WHERE ����
db.Sql("SELECT * FROM users WHERE status = 'active'").Page(1, 10).Query
' ? ���Ƽ�����ѯ���������ٷ�ҳ
db.Sql("SELECT * FROM users").Page(1, 10).Query4. ��������ÿҳ��¼��
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 IfQ4: ��ҳ����α�������
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).Query2. ��֤ҳ���ÿҳ��¼��
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 Function3. �����ܼ�¼��
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