sqlserver分库分表后如何分页查询

在SQL Server中进行分页查询时，需要考虑到数据可能被分散存储在不同的库和表中。下面提供两种分页查询的方法，分别针对水平分库和垂直分表的情况。

1. 水平分库的分页查询

水平分库是指将数据按照某个条件分散存储在多个数据库中。如果要在分散的数据库中进行分页查询，可以采用以下步骤：

• 计算查询结果的总行数。
• 根据每个分库中的数据量，确定需要在哪些分库中进行查询。
• 在每个分库中查询所需的数据，并将结果合并到一起。
• 对合并后的结果进行分页处理。

下面是一个示例代码：

DECLARE @PageSize INT = 10 -- 每页行数
DECLARE @PageNum INT = 1 -- 页码数

-- 计算总行数
DECLARE @TotalCount INT
SELECT @TotalCount = COUNT(*)
FROM Table1 t
WHERE t.key_column BETWEEN 'A' AND 'Z'

-- 计算需要查询的分库列表
DECLARE @DBList TABLE (DBName VARCHAR(100))
INSERT INTO @DBList
SELECT DISTINCT DBName
FROM Table1
WHERE key_column BETWEEN 'A' AND 'Z'

-- 查询每个分库中的数据，并合并结果
DECLARE @Result TABLE (key_column VARCHAR(100), value_column VARCHAR(100))
DECLARE @DBName VARCHAR(100)
DECLARE @Offset INT = (@PageNum - 1) * @PageSize
DECLARE @Limit INT = @PageSize
DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT DBName
FROM @DBList

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'SELECT key_column, value_column FROM ' + @DBName + '.dbo.Table1 WHERE key_column BETWEEN ''A'' AND ''Z'' ORDER BY key_column OFFSET ' + CAST(@Offset AS VARCHAR(10)) + ' ROWS FETCH NEXT ' + CAST(@Limit AS VARCHAR(10)) + ' ROWS ONLY'
    INSERT INTO @Result
    EXEC sp_executesql @SQL
    FETCH NEXT FROM db_cursor INTO @DBName
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- 对结果进行分页处理
SELECT *
FROM @Result
ORDER BY key_column
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY

1. 垂直分表的分页查询

垂直分表是指将一个表按照列的维度拆分成多个表。如果要在分散的表中进行分页查询，可以采用以下步骤：

• 在每个表中查询所需的数据。
• 将查询结果合并到一起，并按照指定的排序字段进行排序。
• 对合并后的结果进行分页处理。

下面是一个示例代码：

DECLARE @PageSize INT = 10 -- 每页行数
DECLARE @PageNum INT = 1 -- 页码数

-- 查询每个表中的数据，并合并结果
DECLARE @Result TABLE (key_column VARCHAR(100), value_column VARCHAR(100))
DECLARE @SQL NVARCHAR(MAX)

DECLARE @Offset INT = (@PageNum - 1) * @PageSize
DECLARE @Limit INT = @PageSize
DECLARE @OrderBy VARCHAR(100) = 'key_column'

INSERT INTO @Result
SELECT key_column, value_column FROM Table1 WHERE key_column BETWEEN 'A' AND 'Z'
UNION ALL
SELECT key_column, value_column FROM Table2 WHERE key_column BETWEEN 'A' AND 'Z'
UNION ALL
SELECT key_column, value_column FROM Table3 WHERE key_column BETWEEN 'A' AND 'Z'
ORDER BY @OrderBy
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY

-- 对结果进行分页处理
SELECT *
FROM @Result
ORDER BY key_column
OFFSET @Offset ROWS
FETCH NEXT @Limit ROWS ONLY

在上述示例代码中，我们使用UNION ALL将多个表中的查询结果合并到一起，并按照指定的排序字段进行排序。然后再对合并后的结果进行分页处理。

需要注意的是，在进行垂直分表的分页查询时，需要保证每个表中的列名和数据类型一致，否则可能会出现类型不匹配的错误。