基于主记录的SQL分页例程实现及命名子查询重名字段访问方案咨询

首先明确你的核心需求：分页是针对主记录（比如Customer）计算的——比如第4页每页10条，实际是取第31-40个唯一客户，再返回这些客户的所有关联电话记录，而非直接按返回的总行数切分。针对这个需求，我们从SQL优化和工具封装两个层面解决问题：

原方案的核心问题分析 #

你遇到的字段冲突（比如Customer.Id和Phone.Id重名），本质是CTE中的字段没有做明确区分，导致无法精准引用主表主键。另外原写法中ORDER BY (SELECT NULL)会导致分页结果不稳定，建议始终指定明确的排序字段（比如Customer.Id或Customer.Name）。

优化后的SQL实现 #

我们可以先筛选出分页范围内的主记录ID，再关联原查询获取完整数据，既避免字段冲突，又提升查询效率：

DECLARE @PageNumber int = 4;
DECLARE @RecordsPerPage int = 10;

-- 第一步：先获取分页范围内的唯一主记录ID（用OFFSET/FETCH简洁实现分页）
WITH PaginatedPrimaryKeys AS (
    SELECT DISTINCT 
        c.Id AS CustomerPrimaryKey  -- 给主表主键起唯一别名，彻底避免字段冲突
    FROM Customer c
    INNER JOIN Phone p ON c.Id = p.CustomerId
    ORDER BY c.Id  -- 指定明确排序，保证分页结果稳定
    OFFSET (@PageNumber - 1) * @RecordsPerPage ROWS
    FETCH NEXT @RecordsPerPage ROWS ONLY
)
-- 第二步：关联原查询，获取这些主记录的所有关联数据
SELECT 
    c.Id, c.Name, c.Address,
    p.Id AS PhoneId, p.Type, p.Number  -- 给子表ID也起别名，避免返回字段重名
FROM Customer c
INNER JOIN Phone p ON c.Id = p.CustomerId
INNER JOIN PaginatedPrimaryKeys ppk ON c.Id = ppk.CustomerPrimaryKey
ORDER BY c.Id, p.Id;  -- 保持结果有序，方便后续映射复杂对象

这个写法的优势：

• 用OFFSET/FETCH（SQL Server 2012+支持）直接分页主记录ID，比嵌套ROW_NUMBER()更简洁直观
• 通过别名彻底规避字段重名问题，清晰区分主/子表字段
• 先筛选主记录再关联子表，减少不必要的关联操作，性能更优

你的C#工具集实现优化建议 #

你要封装成工具集自动给SQL添加分页逻辑的思路非常实用，这里可以调整细节避免潜在问题：

public static string AddPagination(string sql, string primaryKey, PaginationParams requestParams)
{
    if (string.IsNullOrWhiteSpace(primaryKey))
        throw new ArgumentNullException(nameof(primaryKey), "必须指定主记录的主键（如Customer.Id）");

    // 替换SELECT子句，添加主记录的排名（DENSE_RANK保证同一主记录的所有关联行排名相同）
    var sqlWithRank = sql.Replace(
        "SELECT ", 
        $"SELECT DENSE_RANK() OVER (ORDER BY {primaryKey}) AS PrimaryRecordRank, ", 
        StringComparison.OrdinalIgnoreCase);

    // 计算分页边界
    var startRank = 1 + ((requestParams.PageNumber - 1) * requestParams.RecordsPerPage);
    var endRank = requestParams.PageNumber * requestParams.RecordsPerPage;

    // 拼接最终SQL，保留排序保证结果稳定
    return $@"WITH OriginalQuery AS ({sqlWithRank})
SELECT *
FROM OriginalQuery
WHERE PrimaryRecordRank BETWEEN {startRank} AND {endRank}
ORDER BY PrimaryRecordRank";
}

// 分页参数类示例
public class PaginationParams
{
    public int PageNumber { get; set; }
    public int RecordsPerPage { get; set; }
}

这个工具函数的亮点：

• 用DENSE_RANK()精准匹配你的需求：同一主记录的所有关联行获得相同排名，完美实现按主记录分页
• 加入参数校验，避免非法输入导致的SQL错误
• 用BETWEEN替代两次范围比较，语法更简洁

补充注意事项 #

1. 如果原SQL已经包含ORDER BY，建议复用原排序字段作为DENSE_RANK()的排序依据，避免排序逻辑冲突
2. 对于数据量较大的表，建议给主记录主键字段建立索引，大幅提升分页查询的性能
3. 如果原SQL使用了表别名，要确保传入的primaryKey参数和别名一致（比如c.Id而非Customer.Id）

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