1、效率最高的,受限于主键为一个,且主键是数字或者可以转化数字两段代码一致,第一段用于阅读,第二段用于Ctrl+C到程序中。用string替换%%之间字段即可。
SELECT TOP %页大小% *
FROM %表名%
WHERE %主键% >
(
SELECT ISNULL(MAX(%主键%),0)
FROM
(
SELECT TOP %页大小%*(%页数%-1) %主键% FROM %表名%
WHERE 1=1 and (%客户的条件%)
ORDER BY %主键%
) A
)
and (%客户的条件%)
ORDER BY %主键%
SELECT TOP %页大小% * FROM %表名% WHERE [%主键%] > (SELECT ISNULL(MAX([%主键%]),0) FROM (SELECT TOP %页大小%*(%页数%-1) [%主键%] FROM %表名% WHERE 1=1 and (%客户的条件%) ORDER BY [%主键%]) A ) and (%客户的条件%) ORDER BY [%主键%]
2、次高效率,没有限制,同样两段
SELECT TOP %页大小% * FROM %表名% WHERE %主键% NOT IN (SELECT TOP %页大小%*(%页数%-1) %主键% FROM %表名% WHERE 1=1 and (%客户的条件%) ORDER BY %主键%) and (%客户的条件%) ORDER BY %主键%
SELECT TOP %页大小% *
FROM %表名%
WHERE %主键% NOT IN
(SELECT TOP %页大小%*(%页数%-1) %主键%
FROM %表名%
WHERE 1=1 and (%客户的条件%)
ORDER BY %主键%)
and (%客户的条件%)
ORDER BY %主键%