在SQL Server中执行多表连接后查询指定范围的记录

首先先修正你原查询语句里的一个小语法错误：INNER JOIN customeraddresstable a ON c.customernumber = a code 应该改为 INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber（这里大概率是字段名输入时打漏了）。

接下来，不同数据库的分页语法差异很大，我会针对主流数据库给出对应的解决方案，重要提醒：分页前一定要加上ORDER BY子句固定结果集顺序，否则每次查询的分页结果可能不一致，因为数据库默认返回顺序不保证稳定。

1. MySQL / MariaDB #

使用LIMIT语法，格式为LIMIT 偏移量, 行数。因为要取第10到25条记录，总共16条（25-10+1），而偏移量是从0开始计数的，所以第10条对应的偏移量是9：

SELECT 
    c.customernumber, 
    c.customername, 
    c.duedate, 
    c.chequenumber, 
    c.amount, 
    c.status, 
    n.nextdate, 
    a.tel, 
    a.tel2, 
    a.mobile 
FROM currentcustemerchequetable c 
INNER JOIN newtdate n ON c.customernumber = n.customerno 
INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
ORDER BY c.customernumber -- 这里建议按业务需要的字段排序，比如客户编号
LIMIT 9, 16;

2. PostgreSQL #

PostgreSQL支持两种分页方式，一种是和MySQL类似的LIMIT ... OFFSET，另一种是标准SQL的FETCH NEXT语法：

方式一：LIMIT + OFFSET #

SELECT 
    c.customernumber, 
    c.customername, 
    c.duedate, 
    c.chequenumber, 
    c.amount, 
    c.status, 
    n.nextdate, 
    a.tel, 
    a.tel2, 
    a.mobile 
FROM currentcustemerchequetable c 
INNER JOIN newtdate n ON c.customernumber = n.customerno 
INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
ORDER BY c.customernumber
LIMIT 16 OFFSET 9;

方式二：OFFSET + FETCH NEXT #

SELECT 
    c.customernumber, 
    c.customername, 
    c.duedate, 
    c.chequenumber, 
    c.amount, 
    c.status, 
    n.nextdate, 
    a.tel, 
    a.tel2, 
    a.mobile 
FROM currentcustemerchequetable c 
INNER JOIN newtdate n ON c.customernumber = n.customerno 
INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
ORDER BY c.customernumber
OFFSET 9 ROWS FETCH NEXT 16 ROWS ONLY;

3. SQL Server（2012及以上版本） #

使用标准的OFFSET ... FETCH语法，或者用窗口函数ROW_NUMBER()兼容更早版本：

方式一：OFFSET + FETCH #

SELECT 
    c.customernumber, 
    c.customername, 
    c.duedate, 
    c.chequenumber, 
    c.amount, 
    c.status, 
    n.nextdate, 
    a.tel, 
    a.tel2, 
    a.mobile 
FROM currentcustemerchequetable c 
INNER JOIN newtdate n ON c.customernumber = n.customerno 
INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
ORDER BY c.customernumber
OFFSET 9 ROWS FETCH NEXT 16 ROWS ONLY;

方式二：ROW_NUMBER()窗口函数（兼容SQL Server 2008及更早） #

SELECT *
FROM (
    SELECT 
        c.customernumber, 
        c.customername, 
        c.duedate, 
        c.chequenumber, 
        c.amount, 
        c.status, 
        n.nextdate, 
        a.tel, 
        a.tel2, 
        a.mobile,
        ROW_NUMBER() OVER (ORDER BY c.customernumber) AS row_num
    FROM currentcustemerchequetable c 
    INNER JOIN newtdate n ON c.customernumber = n.customerno 
    INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
) AS temp
WHERE row_num BETWEEN 10 AND 25;

4. Oracle #

Oracle的ROWNUM是先筛选再编号，所以需要用子查询先给记录编号，再筛选：

SELECT *
FROM (
    SELECT 
        c.customernumber, 
        c.customername, 
        c.duedate, 
        c.chequenumber, 
        c.amount, 
        c.status, 
        n.nextdate, 
        a.tel, 
        a.tel2, 
        a.mobile,
        ROW_NUMBER() OVER (ORDER BY c.customernumber) AS row_num
    FROM currentcustemerchequetable c 
    INNER JOIN newtdate n ON c.customernumber = n.customerno 
    INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
) AS temp
WHERE row_num BETWEEN 10 AND 25;

如果是Oracle 12c及以上版本，也支持标准的OFFSET ... FETCH语法：

SELECT 
    c.customernumber, 
    c.customername, 
    c.duedate, 
    c.chequenumber, 
    c.amount, 
    c.status, 
    n.nextdate, 
    a.tel, 
    a.tel2, 
    a.mobile 
FROM currentcustemerchequetable c 
INNER JOIN newtdate n ON c.customernumber = n.customerno 
INNER JOIN customeraddresstable a ON c.customernumber = a.customernumber
ORDER BY c.customernumber
OFFSET 9 ROWS FETCH NEXT 16 ROWS ONLY;

内容的提问来源于stack exchange，提问作者Grijan