在MS SQLServer 2000/2005中 用derivedtbl 表示临时表(被推导出来的表)
一般在返回结果集的select语句:
select * from (select * from tableA) as A
使用“derivedtbl” 则可将"as 子句"省略
select * from (select * from tableA) derivedtbl
如在sql分页语句中则可以应用:
显示第n到m条记录(ID 字段为主键)
select * from (select top m * from TableName order by ID) derivedtbl
where ID not in(select top n-1 ID from TableName order by ID)
注意:在Access数据库中 由于不支持top0 ,所以 not in 后面括号中的子句要根据起始的记录号用代码来判断一下
如:一个实际的asp+Access项目中的例子(asp代码)
dim strChildSQL
if intLowIndex=0 then '进行处理
strChildSQL="(SELECT TOP 1 ID FROM TableName WHERE 1=2 order by ID)"
else
strChildSQL="(SELECT TOP " & intLowIndex & " ID FROM TableName order by " & ID)"
end if