DBGRIDEH 数据分页显示问题！

[zjgblue]

SQL数据库，有8W条数据，一次全部查询并显示出来有点卡，想通过分页显示到DBGRIDEH，

请问如何通过 4个Button 来实现“第一页”，“下一页”，“上一页”，“最后一页”的功能,

求详细实例或详细完整代码！

[bdmh]

这根DBGRIDEH无关，你可以先取得记录总数，比如1000条，那第一次就选出前250条，这是你可以获得这250条的最大的id值，然后第二页时，获得比这个id大的下250条，以此类推，分页技术，有很多种方法，网上是有介绍的，看看先

[zjgblue]

不知道与DBGRIDEH，就是不知道SQL代码怎么写，特别是“最后一页”的代码

[zkroy36]

  Select * FROM (select * , ROW_NUMBER()Over(order by ID) as RowId 
     from Tabel1) as   mytable 
  where RowId between 1 and 500

刚才在另一个帖子里的，这个，挺简单

[Allanlove]

Select Max(RowId) as ZYL_RowId FROM (select *, ROW_NUMBER()Over( order by 主键)
as RowId from 表名 where 过滤条件) as mytable //取得总条数 总条数/每页条数 = 页数 单独用一个方法求出来


Select * FROM (select * , ROW_NUMBER()Over( order by 主键 asc) as RowId
from 表名 where 过滤条件 ) as mytable where RowId between 1 and 100 order by RowId asc //查询第一个100行数据

第N页 Select * FROM (select * , ROW_NUMBER()Over( order by 主键 asc) as RowId
from 表名 where 过滤条件 ) as mytable where RowId between (第N页 - 1) * 每页条数 + 1 and
第N页 * 每页条数

第一页 N= 1 最后一页 N=总页数

其中N是公用变量，初始化是 1，点下一页时，N：=N+1 ，上一页 ：N：=N-1；

[zjgblue]

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBGridEhGrouping, ADODB, GridsEh, DBGridEh, DB, StdCtrls;

type
  TForm1 = class(TForm)
    Conn1: TADOConnection;
    ds1: TDataSource;
    ADOQuery1: TADOQuery;
    dbgrdh1: TDBGridEh;
    tbl1: TADOTable;
    btn1: TButton;
    btn2: TButton;
    btn3: TButton;
    btn4: TButton;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    ADOQuery2: TADOQuery;
    procedure btn1Click(Sender: TObject);
    procedure btn2Click(Sender: TObject);
    procedure btn3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btn4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  SN:integer=500;  //每页要显示的记录条数
  N,ZYS:integer;   //N显示页面累加，ZYS 总页数
  ZHYY:Double;     //最后一页	
implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
adoquery2.Close;
adoquery2.SQL.clear;
adoquery2.sql.add('select count(ID) as MYID from MJSJ');
adoquery2.Open;
ZYS:=ADOQuery2.FieldByName('MYID').AsInteger;
ZHYY:=Round(ZYS / SN);
end;

procedure TForm1.btn1Click(Sender: TObject);  //第一页
begin
N:=0;
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.add('select top '+inttostr(SN)+' * from MJSJ');
adoquery1.Open;
end;

procedure TForm1.btn2Click(Sender: TObject);  //下一页
begin
N:=N+SN;
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.Add('select top '+inttostr(SN)+' * from MJSJ where ID not in(select top '+inttostr(N)+' ID from MJSJ)');
adoquery1.Open;
end;

procedure TForm1.btn3Click(Sender: TObject); //上一页
begin

if (N-SN=0) then
begin
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.add('select top '+inttostr(SN)+' * from MJSJ');
adoquery1.Open;
end;

if (N-SN>0) then
begin
N:=N-SN;
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.Add('select top '+inttostr(SN)+' * from MJSJ where ID not in(select top '+inttostr(N)+' ID from MJSJ)');
adoquery1.Open;
end
ELSE Exit;

end;

procedure TForm1.btn4Click(Sender: TObject);  //最后页
begin
adoquery1.Close;
adoquery1.SQL.clear;
adoquery1.SQL.Add('select top '+inttostr(SN)+' * from MJSJ where ID not in(select top '+inttostr(ZYS-SN)+' ID from MJSJ)');
adoquery1.Open;
end;

end.

自己写了下，但是有问题，查询越后面的数据，查询速度越慢，我有8W数据，前2000以内的数据很快，后面的数据查询起来是越来越慢。麻烦高手看下，什么问题！数据库是在本地计算机的！

[Allanlove]

。。你本来数据量就大，还用子查询，子查询的数据越多，肯定就越慢啊

除了我上面的办法，还有个办法就是，

如果没有索引的视图，但是有唯一列，也可以用以下办法分页查询，可以稍微加快一点查询速度
select * from Table1 where Gid > (select min(Gid) as AMinID from (select top 1000 * from Table1 order by Gid desc )as mytable)
and Gid <= (select min(Gid) as AMaxID from (select top 500 * from Table1 order by Gid desc )as mytable)
其中Table1是表名，GID是唯一列，500和1000也是根据页数和每页行数计算所得。

你把这个写成方法或过程，页数、每页行数等做成参数，每次查询，只要调用这一个方法就行了，不需要单独写按钮事件，并且，如果表名、主键等也做成参数，那么就是一个公用的分页查询了

[Allanlove]

算了，我把2种方法的代码都贴给你吧，都是获得SQL语句的

第一种：

Function TCustomQueryForm.FunGetYOrderSql: String;
Var
  AStrSQL: String; // -- 主语句
  AstrOrder, AFilter, AWhereStr: String;
  AMaxRecordcount: Integer;

  Function GetMaxID(Aord: integer): String;
  Var
    ALine, AStr: String;
  Begin
    ALine := '1000';
    Case Aord Of
      0:
        Begin
          ALine := inttostr((FInfoPageIndex_Int) * FInfoPageSize_Int);
          AStr := 'select max(' + FInfoOrderBy_str + ') as AMaxID  from (select top ' + ALine + ' * from ' +
            FQueryTableName + AWhereStr + ' order by ' + FInfoOrderBy_str + ')as mytable';
        End;
      1:
        Begin
          ALine := inttostr((FInfoPageIndex_Int - 1) * FInfoPageSize_Int);
          AStr := 'select min(' + FInfoOrderBy_str + ') as AMaxID from (select top ' + ALine + ' * from ' +
            FQueryTableName + AWhereStr + ' order by ' + FInfoOrderBy_str + ' desc )as mytable';
        End;
    End;
    GetDataset(AStr, ADQ_Count);
    Result := ADQ_Count.fieldbyname('AMaxID').AsString;
  End;

  Function GetMinID(Aord: integer): String;
  Var
    ALine, AStr: String;
  Begin
    ALine := '1000';
    Case Aord Of
      0:
        Begin
          ALine := inttostr((FInfoPageIndex_Int - 1) * FInfoPageSize_Int);
          AStr := 'select max(' + FInfoOrderBy_str + ') as AMaxID  from (select top ' + ALine + ' * from ' +
            FQueryTableName + AWhereStr + ' order by ' + FInfoOrderBy_str + ')as mytable';
        End;
      1:
        Begin
          ALine := inttostr((FInfoPageIndex_Int) * FInfoPageSize_Int);
          AStr := 'select min(' + FInfoOrderBy_str + ') as AMaxID from (select top ' + ALine + ' * from ' +
            FQueryTableName + AWhereStr + ' order by ' + FInfoOrderBy_str + ' desc )as mytable';
        End;
    End;
    GetDataset(AStr, ADQ_Count);
    Result := ADQ_Count.fieldbyname('AMaxID').AsString;
  End;
Begin
  Try
    AFilter := FInfoWhere_Str;
    If FIfQueryOfDate Then
      Begin
        If (Trim(FFieldofDate) <> '') And (Trim(DateEdtBegin.Text) <> '') And (Trim(DateEdtEnd.Text) <> '') Then
          AFilter := AFilter + ' and ' + FFieldofDate + '>=' + QuotedStr(formatdatetime('yyyy-mm-dd', DateEdtBegin.Date)) +
            ' and ' + FFieldofDate + '<=' + QuotedStr(formatdatetime('yyyy-mm-dd', DateEdtEnd.Date));
      End;

    If QueryParamEditor.FilterText <> '' Then
      AFilter := AFilter + ' and (' + QueryParamEditor.FilterText + ')';

    Case FCurSubQueryType Of
      1:
        Begin
          If SubQueryParam_DCL <> '' Then
            AFilter := AFilter + ' and ' + self.SubQueryParam_DCL;
        End;
      2:
        Begin
          If SubQueryParam_YCL <> '' Then
            AFilter := AFilter + ' and ' + self.SubQueryParam_YCL;
        End;
    End; //case FCurSubQueryType of

    If Trim(FQueryParam) <> '' Then
      Begin
        AWhereStr := FQueryParam + ' and ' + AFilter;
        AFilter := trim(UpperCase(AWhereStr));
        AFilter := Copy(AFilter, Pos(AFilter, 'WHERE') + 6, Length(AFilter)); //  FQueryParam
      End
    Else
      AWhereStr := ' where ' + AFilter;

    If FInfoOrderType_str <> '0' Then
      Begin
        AstrOrder := ' order by ' + FInfoOrderBy_str + ' desc';
      End
    Else
      Begin
        AstrOrder := ' order by ' + FInfoOrderBy_str + ' asc';
      End;

    If Not FWhetherDetachPage Then
      Begin
        AMaxRecordcount := 10000;
        AStrSQL := 'select top ' + inttostr(AMaxRecordcount) + ' * from ' + FQueryTableName;
        AStrSQL := AStrSQL + AWhereStr + AstrOrder;

        result := AstrSQL;
        Exit;
      End;

    FunGetPageCount(AFilter);

    If FInfoOrderType_str = '0' Then
      Begin
        AStrSQL := 'select * from ' + FQueryTableName + AWhereStr;
        If FInfoPageIndex_Int <> 1 Then
          AStrSQL := AStrSQL + ' and ' + FInfoOrderBy_str + ' > ' + GetMinID(0);
        If FInfoPageIndex_Int <> FPageCount Then
          AStrSQL := AStrSQL + ' and ' + FInfoOrderBy_str + ' <= ' + GetMaxID(0);
      End
    Else
      Begin
        AStrSQL := 'select * from ' + FQueryTableName + AWhereStr;
        If FInfoPageIndex_Int <> 1 Then
          AStrSQL := AStrSQL + ' and ' + FInfoOrderBy_str + ' <= ' + GetMaxID(1);
        If FInfoPageIndex_Int <> FPageCount Then
          AStrSQL := AStrSQL + ' and ' + FInfoOrderBy_str + ' > ' + GetMinID(1);
      End;

    result := AStrSQL;
  Except
    result := '';
  End;

End;

第二种

Function TCustomQueryForm.FunGetDetachPageSql: String;
Var
  AStrSQL: String; // -- 主语句
  AStrTmp: String; //-- 临时变量
  AStrOrder, AWhereStr, AFilter: String;
  AMaxRecordcount: integer;
Begin
  AFilter := FInfoWhere_Str;

  If Trim(FQueryParam) <> '' Then
    Begin
      AWhereStr := FQueryParam + ' and ' + AFilter;
      AFilter := trim(UpperCase(AWhereStr));
      AFilter := Copy(AFilter, Pos(AFilter, 'WHERE') + 6, Length(AFilter)); //  FQueryParam
    End
  Else
    AWhereStr := ' where ' + AFilter;

  Try
    FunGetPageCount(AFilter);
    If FInfoOrderType_str <> '0' Then
      Begin
        AstrOrder := ' order by ' + FInfoOrderBy_str + ' desc';
      End
    Else
      Begin
        AstrOrder := ' order by ' + FInfoOrderBy_str + ' asc';
      End;

    AStrSQL := 'Select * FROM (' +
      'select ' + FInfoFldCow_str + ', ROW_NUMBER()Over(' + AStrOrder + ') as ZYL_RowId ' +
      ' from ' + FQueryTableName + AWhereStr + ' ' + FInfoGroupBy_str +
      ' ) as mytable where ZYL_RowId between ' + inttostr((FInfoPageIndex_Int - 1) * FInfoPageSize_Int + 1) +
      ' and ' + inttostr(FInfoPageIndex_Int * FInfoPageSize_Int) + AstrOrder;
    result := AstrSQL;
  Except
    result := '';
  End;
End;

Function TCustomQueryForm.FunGetPageCount(AFilterStr: String): Boolean;
Var
  ASqlStr: String;
Begin
  Try
    If FInfoPageSize_Int = 0 Then Exit;

    ASqlStr := 'Select Max(ZYL_RowId) as ZYL_RowId FROM (' +
      'select ' + FInfoFldCow_str + ', ROW_NUMBER()Over( order by ' + FInfoOrderBy_str + ') as  ZYL_RowId ' +
      ' from ' + FQueryTableName + ' where ' + AFilterStr + ' ' + FInfoGroupBy_str +
      ' ) as mytable';

    //  ASqlStr := 'Select count(' + FInfoOrderBy_str + ') as ZYL_RowId FROM ' + FQueryTableName + ' where ' + AFilterStr + ' ' + FInfoGroupBy_str;

    GetDataset(ASqlStr, ADQ_Count);  //打开数据集
    FDateCount := ADQ_Count.FieldByName('ZYL_RowId').AsInteger;
    FPageCount := Trunc(FDateCount / FInfoPageSize_Int); //div
    If FDateCount Mod FInfoPageSize_Int <> 0 Then
      FPageCount := FPageCount + 1;

    Label4.Caption := IntToStr(FDateCount);
    Label6.Caption := IntToStr(FPageCount);
    Edt_Page.Text := IntToStr(FInfoPageIndex_Int);

    CLastPage.Enabled := FInfoPageIndex_Int <> 1;
    CNextPage.Enabled := (FInfoPageIndex_Int <> FPageCount) And (FPageCount <> 0);
  Except
  End;
End;

公用变量：
FInfoFldCow_str: String; //要查询的列
FInfoWhere_Str: String; // 查询条件 (注意: 不要加 where)
FInfoOrderType_str: String; // 设置排序类型, 1则降序
FInfoGroupBy_str: String; // 分组查询
FInfoOrderBy_str: String; // 排序依据
FInfoPageIndex_Int: integer; //第一次显示的页码
FInfoPageSize_Int: integer; //页条数
FPageCount: Integer; //页数
FDateCount: Integer; //行数

[zjgblue]

看的头晕，给个DEMO下载慢慢学习下！

DELPHI新手

[YLCN2010]

SQL2005的话用Row或者Row_Number方法，清晰明了，性能也不比Max差。

[Allanlove]

[Quote=引用 11 楼 zjgblue 的回复:]
看的头晕，给个DEMO下载慢慢学习下！

DELPHI新手
[/Quote]

邮箱发来
[Quote=引用 12 楼 ylcn2010 的回复:]
SQL2005的话用Row或者Row_Number方法，清晰明了，性能也不比Max差。
[/Quote]

如果是有索引的表或试图，差别不大，如果没有索引，相差差不多一半速度

[pengxuan]

网上分页的sql和函数很多，搜搜看看，你懂的。

[zjgblue]

[e-mail]

[zjgblue]

谢谢了！

[Allanlove]

程序已发