SQL Server列重复报错及别名失效，分页语法异常求助

报错1：重复列ProductId #

SqlException: 为 'FilteredResults' 多次指定了列 'ProductId'。

错误的查询语句：

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT p.*, pv.* 
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    *, p.ProductId AS ProductIdAlias 
FROM 
    FilteredResults 
ORDER BY 
    p.ProductIdAlias 
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;

尝试用别名无效，移除ORDER BY p.ProductId后出现第二个错误：

报错2：分页语法错误 #

SqlException: @queryOffset 附近有语法错误。
FETCH 语句中 NEXT 选项的使用无效。

以下是可用的查询语句（无分页版本）：

WITH FilteredProducts AS (
    SELECT
        p.ProductId,
        p.ProductName
    FROM Products p
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId
    WHERE 1 = 1
    AND pc.CategoryId = @categoryId
    AND (p.ProductName LIKE @searchText OR pd.DescriptionBody LIKE @searchText)
)
SELECT
    p.ProductId,
    p.ProductName,
    (
        SELECT
            pv.VariantId,
            pv.VariantName,
            pv.VariantPriceMain
        FROM ProductVariants pv
        WHERE p.ProductId = pv.ProductId
        FOR XML PATH('Variant'), ROOT('Variants'), TYPE
    ) AS Variant
FROM FilteredProducts p
WHERE EXISTS (
    SELECT 1
    FROM ProductVariants pv
    WHERE p.ProductId = pv.ProductId
      AND pv.VariantPriceMain >= @minPrice
);

解决方案 #

1. 解决重复列问题 #

CTE里SELECT p.*, pv.*会同时取出Products和ProductVariants的ProductId，导致列名重复。必须明确指定需要的列，避免通配符*：

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredResults AS 
( 
    SELECT 
        p.ProductId,
        p.ProductName,
        -- 明确列出Products表其他需要的列
        pv.VariantId,
        pv.VariantName,
        pv.VariantPriceMain,
        -- 明确列出ProductVariants其他需要的列
        pd.Description
    FROM Products p 
    LEFT JOIN ProductVariants pv ON p.ProductId = pv.ProductId 
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId 
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId 
    WHERE 1 = 1 
      AND pc.CategoryId = @categoryId 
      AND (p.ProductName LIKE @searchText OR pd.Description LIKE @searchText)
) 
SELECT 
    *
FROM 
    FilteredResults 
ORDER BY 
    ProductId  -- 直接用CTE里的ProductId，无需别名
    OFFSET @queryOffset ROWS 
        FETCH NEXT @queryRows ROWS ONLY;

2. 结合可用查询实现分页 #

如果要保留可用查询的XML聚合变体的逻辑，需要先对过滤后的产品进行分页，再关联变体数据：

DECLARE @queryRows AS INT = 5;
DECLARE @queryOffset AS INT = 0; 

WITH FilteredProducts AS (
    SELECT
        p.ProductId,
        p.ProductName
    FROM Products p
    LEFT JOIN ProductCategories pc ON p.ProductId = pc.ProductId
    LEFT JOIN ProductDescriptions pd ON p.ProductId = pd.ProductId
    WHERE 1 = 1
    AND pc.CategoryId = @categoryId
    AND (p.ProductName LIKE @searchText OR pd.DescriptionBody LIKE @searchText)
    AND EXISTS (
        SELECT 1
        FROM ProductVariants pv
        WHERE p.ProductId = pv.ProductId
          AND pv.VariantPriceMain >= @minPrice
    )
),
PagedProducts AS (
    SELECT
        ProductId,
        ProductName
    FROM FilteredProducts
    ORDER BY ProductId
    OFFSET @queryOffset ROWS
    FETCH NEXT @queryRows ROWS ONLY
)
SELECT
    pp.ProductId,
    pp.ProductName,
    (
        SELECT
            pv.VariantId,
            pv.VariantName,
            pv.VariantPriceMain
        FROM ProductVariants pv
        WHERE pp.ProductId = pv.ProductId
        FOR XML PATH('Variant'), ROOT('Variants'), TYPE
    ) AS Variant
FROM PagedProducts pp;

关键说明 #

• 避免使用SELECT *，明确指定列名可以彻底解决重复列问题
• OFFSET/FETCH必须配合ORDER BY使用，这是SQL Server的强制要求，不能省略
• 分页逻辑要放在变体聚合之前，避免对大量变体数据进行分页操作，提升性能

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