代码如下
Function GetNextData(CdsGridview:TClientDataSet;EdtView:TEdit;sTable,sField,sWhere,sTop:String;Var LocalCount:Integer):Boolean; //获取下一包数据
Var
Odata:OleVariant;
sSql:String;
begin
Result:=False;
screen.Cursor := crhourglass;
With CdsGridview do
Begin
if Not Active then Exit;
sSql:='Select Top '+sTop+sField
+' From '+sTable+' Where 1=1 '+sWhere;
Odata:=null;
InterSales.qurrecords(sSql,Odata);
DisableControls;
if Not VarisNull(Odata) then
begin
if Active then Close;
Open;
AppendData(Odata,true);
Odata:=null;
//得到此次查询的键值
LocalCount := GetKeyFieldValue('ID',CdsGridView);
EnableControls;
EdtView.Clear;
EdtView.Text:='当前下载记录:'+IntToStr(CdsGridView.RecordCount)+'条';
Result:=True;
end else
begin
EnableControls;
screen.Cursor := crdefault;
exit;
end;
screen.Cursor := crdefault;
End;
end;
GetKeyFieldValue的函数原形:
function GetKeyFieldValue(sortField:string;clientdb:TClientDataSet): OleVariant;
var
aCDS : TClientDataSet;
begin
aCDS:=TClientDataSet.Create(nil);
try
try
if not varisnull(clientdb.Data) then
begin
///如果重新按其他列排序后,此处可能不对了 !
aCDS.Data:=clientdb.Data;
WITH acds do begin
IndexName := '';
IndexDefs.Update;
end;
with aCDs.IndexDefs.AddIndexDef do
begin
Fields := sortField;
Options := [];
end;
aCDs.IndexDefs.Update;
aCDS.Last;
Result:=aCDS.FieldByName(sortfield).Value;
end;
finally
aCDS.Free ;
end;
except
on e: Exception do
raise;
end;
end;
【回答】:
select * from table_name where id_field not in (select top 3 id_field from table_name)
注意,其实就是把 select top 语句写两遍,第二遍包含了第一遍,然后把是第一遍中的记录用not in剔除就可以了,所以两个where都必须包含相同的外加条件。
asp 中经常用来写 pageno 的程序,因为直接用ado的pagesize如果遇上几十万数据,用pagesize就完蛋喽。
关于 not in 语句速度比较慢,某人提示将not in改为
select table2.* from table2 left join table1 on table2.key=table1.key where (able1.key is null)
返回第3条记录用:
select top 1 * from (SELECT top 3 a.编号 FROM a order by 编号) as b order by 编号 desc
如果用来进行分页,返回第100-150条记录,可以用
select top 50 * from (SELECT top 150 a.编号 FROM a order by 编号) as b order by 编号 desc
procedure TForm1.FormShow(Sender: TObject);
begin
with aqStore do
begin
Close;
SQL.Clear;
SQL.Add('select * from users');
Open;
end;
adsClone := TADODataSet.Create(self);
aqStore.Recordset.PageSize := 5;
pCount := aqStore.Recordset.PageCount;
ShowData(1);
end;
procedure TForm1.Button1Click(Sender: TObject);
begin
Form1.Close;
end;
procedure TForm1.ShowData(iPage: Integer);
procedure CreadeAdsField;
var
i: Integer;
begin
adsClone.Close;
adsClone.FieldDefs.Clear;
for i := 0 to aqStore.FieldCount - 1 do
begin
adsClone.FieldDefs.Add(aqStore.Fields[i].FieldName, aqStore.Fields[i].DataType, aqStore.Fields[i].Size);
end;
adsClone.CreateDataSet;
end;
var
i, j: Integer;
rs: adoint.RecordSet;
begin
aqStore.Recordset.AbsolutePage := iPage;
rs := aqStore.Recordset;
CreadeAdsField;
adsClone.DisableControls;
for i := 0 to rs.PageSize - 1 do
begin
adsClone.append;
for j :=0 to rs.Fields.Count - 1 do
adsClone.Fields[j].Value := rs.Fields[j].Value;
rs.MoveNext;
if rs.EOF then Break;
end;
adsClone.EnableControls;
adsClone.First;
dsStore.DataSet := adsClone;
lblCount.Caption := '页次:' + IntToStr(iPage) + '/' + IntToStr(pCount);
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
curPage := 1;
ShowData(curPage);
end;
procedure TForm1.Button3Click(Sender: TObject);
begin
if curPage = 1 then
begin
MessageBox(self.Handle, '已到了首页!', '提示', mb_IconInformation + mb_Ok);
Exit;
end;
Dec(curPage);
ShowData(curPage);
end;
procedure TForm1.Button4Click(Sender: TObject);
begin
if curPage = pCount then
begin
MessageBox(self.Handle, '已到了末页!', '提示', mb_IconInformation + mb_Ok);
Exit;
end;
Inc(curPage);
ShowData(curPage);
end;
procedure TForm1.Button5Click(Sender: TObject);
begin
curPage := pCount;
ShowData(curPage);
end;