sql-server - 如何在 SQL Server 的 where 子句中使用别名

Question

我有以下查询，我得到了我想要的完美结果，但我想在 where 子句条件中使用 stManufacturerPartReference 别名，就像下面的第二个查询一样，但它给了我错误。

WITH ProductsCTE (inProductId, inCategoryId, stCategory, stManufacturers, inCompanyId, stERPId,stManufacturerPartReference,
                                stProductName, stProductNumber, stModel, stFileLink, stImage, dcPrice,dcStandardPrice, dcOnHandQty,dcQtyOnPO,dtEstimatedShipDate, dcWeight, inSyncStatus, dtLastSyncDate,
                                inErrorRetry,flgIsActive, flgIsDeleted, inCreatedBy, inModifiedBy, dtModificationDate, dtCreationDate, inRecordCount)
                AS (
                    SELECT
                        product.inProductId,
                        product.inCategoryId,
                        product.stCategory,
                        product.stManufacturers,
                        product.inCompanyId,
                        product.stERPId,
                        product.stProductName,
                        STUFF((SELECT ', ' + PM.stManufacturerPartReference
                           FROM tblProductManufacturers PM
                           JOIN tblProducts Product on PM.inProductId = Product.inProductId
                           JOIN tblManufacturers M on M.inManufacturerId = PM.inManufacturerId
                           WHERE PM.inProductId=product.inProductId
                           ORDER BY M.stManufacturer
                           FOR XML PATH('')), 1, 1, '') as stManufacturerPartReference,
                        product.stProductNumber,
                        product.stModel,
                        product.stFileLink,
                        product.stImage,
                        product.dcPrice,
                        product.dcStandardPrice,
                        product.dcOnHandQty,
                        product.dcQtyOnPO,
                        product.dtEstimatedShipDate,
                        product.dcWeight,
                        product.inSyncStatus,
                        product.dtLastSyncDate,
                        product.inErrorRetry,
                        product.flgIsActive,
                        product.flgIsDeleted,
                        product.inCreatedBy,
                        product.inModifiedBy,
                        product.dtModificationDate,
                        product.dtCreationDate,
                        CAST((COUNT(product.inProductId) OVER()) AS BIGINT) AS inRecordCount
                    FROM tblProducts Product WITH (NOLOCK)
                    WHERE 1=1
                    AND product.flgIsDeleted <> 1 
                    AND flgIsHistoricItem <> 1 AND (product.inCompanyId = 1)   )

    SELECT  P.inProductId,
            P.inCategoryId,
            P.stCategory,
            P.stManufacturers,
            P.stManufacturerPartReference,
            P.inCompanyId,
            P.stERPId,
            P.stProductName,
            P.stProductNumber,
            P.stModel,
            P.stFileLink,
            P.stImage,
            P.dcPrice,
            P.dcStandardPrice,
            P.dcOnHandQty,
            P.dcQtyOnPO,
            P.dtEstimatedShipDate,
            P.dcWeight,
            P.inSyncStatus,
            P.dtLastSyncDate,
            P.inErrorRetry,
            P.flgIsActive,
            P.flgIsDeleted,
            P.inCreatedBy,
            P.inModifiedBy,
            P.dtModificationDate,
            P.dtCreationDate,
            P.inRecordCount
    FROM ProductsCTE P
    ORDER BY stCategory ASC
    OFFSET (1 - 1) * 1000 ROWS
    FETCH NEXT 1000 ROWS ONLY;

我想在 Where 子句中使用 stManufacturerPartReference，如下所示。

WITH ProductsCTE (inProductId, inCategoryId, stCategory, stManufacturers, inCompanyId, stERPId,stManufacturerPartReference,
                                stProductName, stProductNumber, stModel, stFileLink, stImage, dcPrice,dcStandardPrice, dcOnHandQty,dcQtyOnPO,dtEstimatedShipDate, dcWeight, inSyncStatus, dtLastSyncDate,
                                inErrorRetry,flgIsActive, flgIsDeleted, inCreatedBy, inModifiedBy, dtModificationDate, dtCreationDate, inRecordCount)
                AS (
                    SELECT
                        product.inProductId,
                        product.inCategoryId,
                        product.stCategory,
                        product.stManufacturers,
                        product.inCompanyId,
                        product.stERPId,
                        product.stProductName,
                        STUFF((SELECT ', ' + PM.stManufacturerPartReference
                           FROM tblProductManufacturers PM
                           JOIN tblProducts Product on PM.inProductId = Product.inProductId
                           JOIN tblManufacturers M on M.inManufacturerId = PM.inManufacturerId
                           WHERE PM.inProductId=product.inProductId
                           ORDER BY M.stManufacturer
                           FOR XML PATH('')), 1, 1, '') as stManufacturerPartReference,
                        product.stProductNumber,
                        product.stModel,
                        product.stFileLink,
                        product.stImage,
                        product.dcPrice,
                        product.dcStandardPrice,
                        product.dcOnHandQty,
                        product.dcQtyOnPO,
                        product.dtEstimatedShipDate,
                        product.dcWeight,
                        product.inSyncStatus,
                        product.dtLastSyncDate,
                        product.inErrorRetry,
                        product.flgIsActive,
                        product.flgIsDeleted,
                        product.inCreatedBy,
                        product.inModifiedBy,
                        product.dtModificationDate,
                        product.dtCreationDate,
                        CAST((COUNT(product.inProductId) OVER()) AS BIGINT) AS inRecordCount
                    FROM tblProducts Product WITH (NOLOCK)
                    WHERE 1=1
                    AND product.flgIsDeleted <> 1 
                    AND flgIsHistoricItem <> 1 AND (product.inCompanyId = 1) AND  stManufacturerPartReference LIKE '%ABC DEF%'  )

    SELECT  P.inProductId,
            P.inCategoryId,
            P.stCategory,
            P.stManufacturers,
            P.stManufacturerPartReference,
            P.inCompanyId,
            P.stERPId,
            P.stProductName,
            P.stProductNumber,
            P.stModel,
            P.stFileLink,
            P.stImage,
            P.dcPrice,
            P.dcStandardPrice,
            P.dcOnHandQty,
            P.dcQtyOnPO,
            P.dtEstimatedShipDate,
            P.dcWeight,
            P.inSyncStatus,
            P.dtLastSyncDate,
            P.inErrorRetry,
            P.flgIsActive,
            P.flgIsDeleted,
            P.inCreatedBy,
            P.inModifiedBy,
            P.dtModificationDate,
            P.dtCreationDate,
            P.inRecordCount
    FROM ProductsCTE P
    ORDER BY stCategory ASC
    OFFSET (1 - 1) * 1000 ROWS
    FETCH NEXT 1000 ROWS ONLY;

但它给了我错误“无效的列名'stManufacturerPartReference'。” 那么我如何在where子句中使用别名请帮忙。谢谢。

标签： sql-serverasp.net-mvc

Answer 1

我会这样做：

SELECT *, STUFF(stManufacturerPartReference, 1, 1, '') AS stManufacturerPartReference
FROM . . . . 
     . . . . CROSS APPLY
     ( SELECT ', ' + PM.stManufacturerPartReference
       FROM tblProductManufacturers PM JOIN 
            tblProducts Product 
            ON PM.inProductId = Product.inProductId JOIN 
            tblManufacturers M 
            ON M.inManufacturerId = PM.inManufacturerId
       WHERE PM.inProductId=product.inProductId
       FOR XML PATH('') 
     ) tt(stManufacturerPartReference)
WHERE . . . AND
     stManufacturerPartReference LIKE '%ABC DEF%';